# Database overview

PowerAMS targets MySQL 8, InnoDB, `utf8mb4`, strict SQL mode, and UTC. Operational business tables include `airline_id`; composite tenant/status/date indexes support isolation and common queries. Foreign keys protect relationships, unique constraints protect business identifiers, and soft-delete columns preserve appropriate master history.

## Domain groups

| Domain | Principal tables |
|---|---|
| Foundation and identity | `airlines`, `airline_bases`, `users`, `roles`, `permissions`, `user_roles`, `user_bases`, `user_sessions`, `login_attempts`, `audit_logs`, `system_settings`, `migrations` |
| Airport, route, and fleet | `airports`, `routes`, `route_alternate_airports`, `aircraft_types`, `aircraft`, engines, owners, leases, documents, status/location histories |
| Schedule and operations | templates/days, `flights`, status/assignment/conflict history, dispatches, actual times, payloads, delays, cancellations, diversions, checklists, attachments, operational logs |
| Fleet utilization | daily/monthly utilization, downtime, availability, usage adjustments |
| Maintenance | programs, tasks, requirements, schedules, work orders/tasks/completions, defects/actions, approvals, return-to-service, attachments |
| Components and inventory | component types/components/life/movements/installations, items/batches/locations/transactions, suppliers, purchase orders, receipts, reservations, work-order parts |
| Expense and finance | vendors, categories, expenses/allocations/attachments/approvals, approval engine, vendor invoices/payments, revenue/invoices/payments/refunds, budgets/periods/currencies/rates, profitability snapshots |
| Fuel | suppliers/prices, plans, uplifts, consumption, invoices/items, variances |
| Crew | employees/profiles/roles, qualifications/documents/training, availability/leave, rosters, assignments, duties, conflicts |
| Documents and alerts | documents/versions/categories/links, alerts/rules, notifications/preferences/deliveries/templates |
| Integration and administration | API clients/tokens/scopes/logs, webhook endpoints/events/deliveries, provider credentials/logs/failures, settings, sequences, issued numbers, exports, backup status, executive snapshots |

Money, exchange rates, fuel quantities, hours, weights, and calculated measurements use DECIMAL at their declared scale. Status and audit/history tables retain operational evidence. Immutable stock transactions, issued numbers, approvals, payments, component movements, and completion records must not be rewritten.

## Tenant isolation rules

The authenticated session or API token supplies the airline context. Services receive both user and airline IDs; repositories include the airline in lookup/update predicates. A missing cross-tenant record returns not found rather than revealing its existence. Never add a repository method that fetches an operational record by primary key alone.

## Health and maintenance

Use `php cli migrate:status`, `php cli health:check`, `CHECK TABLE`, `information_schema.STATISTICS`, slow-query logs, buffer-pool metrics, connection utilization, replica lag where used, and backup restore tests. Do not manually edit migration metadata. Archive old logs according to retention policy without deleting mandated audit evidence.