source (PDF document)
  │
  ├── 1:N ──► acm_record (extracted ACM items)
  │               │
  │               └── N:1 ──► acm_table_section (raw HTML table for provenance)
  │
  └── 1:1 ──► site_config (non-extractable metadata: department, agency, etc.)

extraction_progress  (keyed by command_id — one per extraction job)
field_schema         (keyed by version — drives parser, grid columns, and export)
agui_events          (AG-UI SSE events keyed by command_id and sequence)

DEFINE TABLE acm_record SCHEMAFULL;

-- Core identification
DEFINE FIELD source_id ON acm_record TYPE record<source>;

-- Organisation Hierarchy
DEFINE FIELD department     ON acm_record TYPE option<string>;
DEFINE FIELD agency         ON acm_record TYPE option<string>;
DEFINE FIELD sub_agency     ON acm_record TYPE option<string>;
DEFINE FIELD site_name      ON acm_record TYPE option<string>;

-- Building Information (15 fields)
DEFINE FIELD building_id            ON acm_record TYPE string;
DEFINE FIELD building_name          ON acm_record TYPE string;
DEFINE FIELD building_type          ON acm_record TYPE option<string>;
DEFINE FIELD building_address       ON acm_record TYPE option<string>;
DEFINE FIELD suburb                 ON acm_record TYPE option<string>;
DEFINE FIELD postcode               ON acm_record TYPE option<string>;
DEFINE FIELD owned_or_leased        ON acm_record TYPE option<string>;
DEFINE FIELD building_unique_id     ON acm_record TYPE option<string>;
DEFINE FIELD frequency_of_use       ON acm_record TYPE option<string>;
DEFINE FIELD public_access          ON acm_record TYPE option<string>;
DEFINE FIELD date_of_inspection     ON acm_record TYPE option<datetime>;
DEFINE FIELD building_year          ON acm_record TYPE option<int>;
DEFINE FIELD building_size_m2       ON acm_record TYPE option<float>;
DEFINE FIELD number_of_levels       ON acm_record TYPE option<int>;
DEFINE FIELD building_construction  ON acm_record TYPE option<string>;
DEFINE FIELD roof_type              ON acm_record TYPE option<string>;

-- Location
DEFINE FIELD area_type   ON acm_record TYPE string;
DEFINE FIELD level       ON acm_record TYPE option<string>;
DEFINE FIELD room_id     ON acm_record TYPE option<string>;
DEFINE FIELD room_name   ON acm_record TYPE option<string>;
DEFINE FIELD room_area   ON acm_record TYPE option<float>;
DEFINE FIELD location    ON acm_record TYPE string;

-- ACM Item Details
DEFINE FIELD product              ON acm_record TYPE string;
DEFINE FIELD material_description ON acm_record TYPE option<string>;
DEFINE FIELD friable              ON acm_record TYPE option<string>;
DEFINE FIELD acm_product_group    ON acm_record TYPE option<string>;
DEFINE FIELD acm_product_type     ON acm_record TYPE option<string>;
DEFINE FIELD nata_sample_number   ON acm_record TYPE option<string>;
DEFINE FIELD sample_result        ON acm_record TYPE option<string>;
DEFINE FIELD hygiene_company      ON acm_record TYPE option<string>;

-- Assessment
DEFINE FIELD material_condition    ON acm_record TYPE option<string>;
DEFINE FIELD disturbance_potential ON acm_record TYPE option<string>;
DEFINE FIELD extent                ON acm_record TYPE option<string>;
DEFINE FIELD risk_status           ON acm_record TYPE option<string>;

-- Documentation
DEFINE FIELD labelled                  ON acm_record TYPE option<string>;
DEFINE FIELD label_details             ON acm_record TYPE option<string>;
DEFINE FIELD hygienist_recommendations ON acm_record TYPE option<string>;
DEFINE FIELD additional_comments       ON acm_record TYPE option<string>;
DEFINE FIELD photo_reference           ON acm_record TYPE option<string>;

-- Removal Tracking
DEFINE FIELD psb_acm_id              ON acm_record TYPE option<string>;
DEFINE FIELD assumed_removed         ON acm_record TYPE option<string>;
DEFINE FIELD date_of_removal         ON acm_record TYPE option<datetime>;
DEFINE FIELD quantity_removed        ON acm_record TYPE option<string>;
DEFINE FIELD removal_notification_no ON acm_record TYPE option<string>;
DEFINE FIELD epa_certificate_no      ON acm_record TYPE option<string>;
DEFINE FIELD removal_comments        ON acm_record TYPE option<string>;

-- BAR Compliance fields (added E1-S12 / E1-S14)
DEFINE FIELD quantity           ON acm_record TYPE option<string>;
DEFINE FIELD acm_labelled       ON acm_record TYPE option<bool>;
DEFINE FIELD acm_label_details  ON acm_record TYPE option<string>;
DEFINE FIELD identifying_company ON acm_record TYPE option<string>;
DEFINE FIELD floor_level        ON acm_record TYPE option<string>;
DEFINE FIELD normalized_action  ON acm_record TYPE option<string>;
DEFINE FIELD enriched_text      ON acm_record TYPE option<string>;

-- Parent Document Retrieval (E11-S1)
DEFINE FIELD parent_table_id ON acm_record TYPE option<record<acm_table_section>>;

-- Metadata
DEFINE FIELD page_number           ON acm_record TYPE option<int>;
DEFINE FIELD extraction_confidence ON acm_record TYPE option<float>;
DEFINE FIELD created_at            ON acm_record TYPE datetime DEFAULT time::now();
DEFINE FIELD updated_at            ON acm_record TYPE datetime DEFAULT time::now();

-- Indexes
DEFINE INDEX acm_source       ON acm_record FIELDS source_id;
DEFINE INDEX acm_building     ON acm_record FIELDS building_id;
DEFINE INDEX acm_risk         ON acm_record FIELDS risk_status;
DEFINE INDEX acm_department   ON acm_record FIELDS department;
DEFINE INDEX acm_agency       ON acm_record FIELDS agency;
DEFINE INDEX acm_suburb       ON acm_record FIELDS suburb;
DEFINE INDEX acm_sample_result ON acm_record FIELDS sample_result;