Database Schema
PostgreSQL 15 on Supabase. Row-Level Security is enabled on every table. All tenanted tables carry a company_id column with an RLS policy restricting access to the authenticated organisation.
Fleet
aircraft
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | FK companies -- RLS key |
| registration | text | ICAO registration, e.g. 5Y-KQS |
| aircraft_type | text | e.g. Boeing 737-800 |
| status | text | active | aog | maintenance | stored |
| base_station | text | ICAO station code |
| total_flight_hours | numeric | Cumulative airframe FH |
| total_cycles | integer | Cumulative airframe cycles |
| rvsm_approved | boolean | |
| rvsm_expiry | timestamptz | |
| etops_rating_minutes | integer | 0 | 120 | 180 |
| lvo_approved | boolean | |
| airspace_approvals | jsonb | RNP-AR, MNPS and other approval flags |
aircraft_engines
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| aircraft_id | uuid | FK aircraft |
| engine_position | text | 1 | 2 | APU |
| tso_hours | numeric | Time Since Overhaul (hours) |
| tsn_cycles | integer | Time Since New (cycles) |
flight_sectors
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| aircraft_id | uuid | FK aircraft |
| origin | text | ICAO departure |
| destination | text | ICAO arrival |
| departure_time | timestamptz | |
| fh_before | numeric | Airframe FH at block-off |
| fh_after | numeric | Airframe FH at block-on |
| cycles | integer | 1 per sector |
| block_time_minutes | integer |
Defects & MEL
aviation_defects
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| aircraft_id | uuid | FK aircraft |
| title | text | |
| description | text | |
| ata_chapter | text | e.g. 32 (Landing Gear) |
| severity | text | aog | high | medium | low |
| status | text | open | deferred | rectified | closed |
| station | text | ICAO code where defect was raised |
| reported_by | text | |
| reported_at | timestamptz | |
| created_at | timestamptz |
mel_deferrals
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| aircraft_id | uuid | FK aircraft |
| ata_chapter | text | |
| mel_category | text | A | B | C | D |
| deferral_date | date | |
| expiry_date | date | Computed from category limits |
| status | text | active | expired | rectified |
aviation_delays
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| aircraft_id | uuid | |
| flight_number | text | |
| iata_delay_code | text | Technical delays: 41-48 |
| delay_minutes | integer | |
| occurrence_date | date |
Platform
audit_logs
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| action | text | Namespaced: wo.transition, amp.approve, approval.sign ... |
| entity_type | text | |
| entity_id | uuid | |
| actor_user_id | uuid | FK auth.users |
| summary | text | Human-readable description |
| metadata | jsonb | Before/after state, signatures, licence numbers |
| created_at | timestamptz |
members
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| user_id | uuid | FK auth.users |
| text | ||
| name | text | |
| role | text | admin | engineer | viewer |
login_attempts
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| text | Lowercased | |
| ip | text | |
| failed_at | timestamptz | Auto-pruned after 1 hour via trigger |
rate_limit_log
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| identifier | text | IP address |
| scope | text | auth | crs_issue | dispatch_gate ... |
| created_at | timestamptz |
stores_items
| Column | Type | Notes |
|---|---|---|
| id | uuid | PK |
| company_id | uuid | RLS key |
| aircraft_id | uuid | Optional -- fleet-wide stock has no aircraft_id |
| part_number | text | |
| quantity_on_hand | integer | |
| minimum_stock | integer | Alert threshold |
| is_aog_stock | boolean | Reserved for AOG use only |