FLYsafe.live — Database Structure & Field Reference

① Database Landscape

Two physically separate database contexts are involved. Every drone-facing table/view lives in a tenant's own database; AADMS is a single central database that reads across all tenants.

② Entity Relationship Diagram

③ Table Structure Reference

Every column that is actually stored, grouped the same way the schema itself groups them. Views are described separately below — they don't store anything, so their columns aren't repeated.

drones.dlb_drones — raw DLB import (table)

Written only by the Import process. Column names match the DLB API verbatim — never renamed, never written by RTM.

drones.drones — Custodian dTwin state (table)

PK (tenant_id, serial_number). Three write owners: the Import process (identity, first insert only), the Custodian APIs (config, from the RTM Map UI or MCP agents), and the GCS Plugin path (live telemetry, via the Custodian).

Core identity

Custodian / adapter configuration

OI defaults & FLYplan automation

Default pilot & separation defaults

ADS-B provisioning & ownership

Conspicuity / ADS-B detail (v1.13)

Provisioning detail behind the flags above — closes the gap flagged in §4. Not columns: the mockup's ec_callsign / ec_tc_reg are just local aliases for dlb_drones.inventory_number / identification_number (already Callsign / TC_Registration in v_dtwin) — no new column needed for either.

Observed ADS-B state (read-only, v1.13)

Data source monitoring

Current flight & current OI

Live state (written from GCS Plugin telemetry)

Cross-references & timestamps

Where OI geometry actually lives (v1.13)

drones.daa_rules — DAA rule set (table, v1.13)

One row per Detect-and-Avoid rule per drone — backs the mockup's daa_rules[] array. Rules evaluate in sort_order; each can be enabled/disabled without deleting it.

-- drones.daa_rules · one row per DAA rule per drone
-- Read/written by the Custodian API's DAA endpoints; evaluated by the DAA service at runtime.
CREATE TABLE drones.daa_rules (
  tenant_id             text NOT NULL,
  serial_number         text NOT NULL,
  rule_id                text NOT NULL,                             -- e.g. R1, R2, R3

  condition_text        text NOT NULL,                             -- human-readable trigger condition
  action_text            text NOT NULL,                             -- action taken when triggered
  priority                text NOT NULL,                             -- critical | high | medium | low
  enabled                 bool NOT NULL DEFAULT true,
  sort_order             int4 NOT NULL DEFAULT 0,                    -- evaluation / display order

  PRIMARY KEY (tenant_id, serial_number, rule_id),
  FOREIGN KEY (tenant_id, serial_number) REFERENCES drones.drones(tenant_id, serial_number)
);

aadms.drones — cross-tenant lookup (materialized view, central AADMS db)

Physically stored (unlike a plain view) so it has its own column list. Built with postgres_fdw — one foreign server + proxy schema per tenant — aggregating every tenant's drones.drones ⋈ dlb_drones into one flat, queryable surface. Refreshed after each Import sync run.

-- aadms.drones · cross-tenant drone lookup
-- Materialized view aggregating every tenant's drones.drones rows (joined to dlb_drones for callsign).
-- Foreign servers / user mappings / fdw_<tenant> proxy schemas are set up earlier in aadms-schema.sql.
CREATE SCHEMA IF NOT EXISTS aadms;

CREATE MATERIALIZED VIEW IF NOT EXISTS aadms.drones AS
  -- One UNION ALL block per tenant (via its fdw_<tenant> proxy schema); extend as tenants are added.
  SELECT
    d.tenant_id,
    d.serial_number,
    d.dlb_guid              AS guid,
    dlb.inventory_number    AS callsign,
    d.aadms_id               AS id
  FROM fdw_airmarket.drones d
  JOIN fdw_airmarket.dlb_drones dlb ON dlb.guid = d.dlb_guid
  WHERE d.retired_at IS NULL

  UNION ALL

  SELECT
    d.tenant_id,
    d.serial_number,
    d.dlb_guid              AS guid,
    dlb.inventory_number    AS callsign,
    d.aadms_id               AS id
  FROM fdw_sait.drones d
  JOIN fdw_sait.dlb_drones dlb ON dlb.guid = d.dlb_guid
  WHERE d.retired_at IS NULL

  UNION ALL

  SELECT
    d.tenant_id,
    d.serial_number,
    d.dlb_guid              AS guid,
    dlb.inventory_number    AS callsign,
    d.aadms_id               AS id
  FROM fdw_sandbox.drones d
  JOIN fdw_sandbox.dlb_drones dlb ON dlb.guid = d.dlb_guid
  WHERE d.retired_at IS NULL
  -- ... repeat for each tenant as onboarded ...
;

CREATE UNIQUE INDEX idx_aadms_drones_callsign ON aadms.drones (callsign);
CREATE INDEX        idx_aadms_drones_serial   ON aadms.drones (serial_number);

Plain views — what they add, not what they store

④ Design vs. Mockup — Gaps to Resolve

Compared the design (Aircraft_Custodian_dTwin_Strategy.html v1.13 + the deployed .sql files) against the current RTM Map mockup (fsl-rtm-map-mu.html). Five rows were resolved on 2026-08-20; the remaining CLEANUP rows are still open for the next dev sync.

⑤ Recommended Next Steps

1. Migrate the deployed schema (rtm-tenant-schema.sql, aadms-schema.sql) from v1.6 to the current v1.13 design: drop keycloak_client_id; add the v1.7–v1.13 columns, including fv_buffer_m/cv_buffer_m, the 8 crewed/uncrewed NMAC/SWC fields, and the new conspicuity/ADS-B columns; create drones.daa_rules. No oi_geometry to add — it's been removed from the design in favor of flights_binding.
2. Retire the mockup's legacy compat fields once the filter logic that depends on them is moved to adapter_type/adapter_config.
3. Reconcile the adapter_type enum and stop modeling ADS-B Out as an adapter personality.