Database

Database

MariaDB database db_team11 (local dev via XAMPP; credentials in .env, never committed). The schema is managed with CodeIgniter migrations in app/Database/Migrations/; sample data comes from seeders in app/Database/Seeds/.

Table overview

users ──┬──< meet_participants >── meets ──(created_by)── users
        ├──< group_members >───── flight_groups ──(created_by)── users
        └──< messages >────────── flight_groups (group_id, NULL = global chat)

Shield:   users, auth_identities, auth_groups_users, auth_permissions_users, ...
Legacy:   personen, umsaetze          (from the original fwe.sql import)
Weather:  weather_reports             (Open-Meteo forecast cache)

FlightMeet domain tables

Created by CreateFlightMeetTables (2026-07-17). All foreign keys cascade on delete.

meets

Column	Type	Notes
id	INT unsigned, PK
title	VARCHAR(120)
spot	VARCHAR(120)	launch/landing site
region	VARCHAR(80), indexed
date / time	DATE / TIME
description	TEXT
level	VARCHAR(20), default All levels	Beginner / Intermediate / Advanced / All levels
max_participants	SMALLINT unsigned
latitude / longitude	DECIMAL(8,5), nullable	null when geocoding failed
created_by	FK → users.id	the organizer
created_at / updated_at	DATETIME

meet_participants

Join table (meet_id, user_id) with a unique key on the pair (prevents duplicate joins at the DB level) plus created_at. Participant count and meet status (open/full) are always derived from this table, never stored (spec NFR-4).

flight_groups

Pilot communities (not to be confused with Shield auth groups): name (VARCHAR(80), unique), region, description, optional image, created_by FK, timestamps.

group_members

Join table (group_id, user_id), unique on the pair, created_at. Member counts are derived.

messages

Chat messages: group_id (nullable FK → flight_groups; NULL = the global "All pilots" channel), user_id FK, body TEXT, created_at. Indexed on (group_id, id) to make the incremental after-cursor polling query cheap.

Users and auth tables

• Shield tables (users, auth_identities, auth_groups_users, ...) come from php spark shield:setup / Shield's own migrations.
• AddProfileFieldsToUsers (2026-07-03) adds profile columns to users: vorname, nachname, strasse, plz, ort. The original personen table stays untouched.
• AddSubscriptionTierToUsers (2026-07-17) adds subscription_tier (pilot / club / school), defaulting every account to the free pilot tier.

Weather cache

CreateWeatherReportsTable (2026-07-17) creates weather_reports: cached Open-Meteo forecasts keyed by (latitude, longitude), holding the location name/country, the raw forecast payload, and fetched_at. See Weather Integration for upsert, staleness, and purge rules.

Legacy tables

personen and umsaetze come from the original course dataset (fwe.sql, 9999 person records). They are not part of the FlightMeet domain; PersonenSeeder imports the people as regular Shield users.

Seeders

Seeder	What it does
FlightMeetSeeder	Sample content: pilots pilot1..pilot6 (password password123), four flight groups with memberships, upcoming meets with real coordinates and participants, and chat messages (global + per group). Idempotent: aborts when meets is non-empty; creates pilots only when missing.
PersonenSeeder	Imports the 9999 personen records as regular user accounts and creates the admin / moderator test accounts (see Authentication and Roles).

Running migrations and seeds

php spark migrate --all
php spark db:seed FlightMeetSeeder
php spark db:seed PersonenSeeder    # optional, for the legacy user base + admin accounts