Data model and hand-off scripts

Files in the repository

• database/schema.sql — full DDL: enums, tables, foreign keys, indexes, append-only rule and two reporting views.
• database/seed.sql — generated DML that reproduces every row these screens show (792 records, their statuses, links, use-case bindings and evidence timelines).
• database/generate-seed.ts — regenerates the seed from the application fixtures, so the dev database never drifts from the workspace.
• database/schema-connectors.sql — additive v0.2 DDL for the connector catalogue (274 profiles, 23 families, 12 templates, capability grid, primary sources), contracts, the 32 management operations, connection lifecycle, recovery, qualification, release gates, the warehouse grain and assisted diagnosis history. Approved canonical contracts and proposed warehouse specialisations live in separate tables and are never merged.
• database/seed-connectors.sql — generated DML for all of the above, taken from the same packs and fixtures these screens render.
• database/generate-connector-seed.ts — regenerates that seed so the scripts and the screens cannot drift.
• database/schema-vision.sql — additive v0.3 DDL for the machine vision (EyeZ) extension: 33 camera and perception profiles, 16 software and standards dependencies, 60 EyeZ jobs with their camera and software candidates, 12 payload contracts, 12 detail screens, 16 fault scenarios, the ten runtime operations with their effect, and the station, calibration, capture-group, view, evidence, inspection, association, recovery, qualification and release grain.
• database/seed-vision.sql — generated DML for all of the above, taken from the same pack and fixtures the vision screens render.
• database/generate-vision-seed.ts — regenerates the vision seed so those scripts and screens cannot drift.
• database/decisions.md — decisions taken and the open proposals awaiting the canonical V9 identifiers and the connector work.

psql "$DATABASE_URL" -f database/schema.sql
psql "$DATABASE_URL" -f database/seed.sql
psql "$DATABASE_URL" -f database/schema-connectors.sql
psql "$DATABASE_URL" -f database/seed-connectors.sql
psql "$DATABASE_URL" -f database/schema-vision.sql
psql "$DATABASE_URL" -f database/seed-vision.sql

Verified by loading all six scripts into a PostgreSQL engine: 274 connector profiles, 408 capability rows, 40 approved shared schemas, 18 proposed warehouse specialisations, 32 management operations, the full warehouse grain, and the vision extension (33 profiles, 60 EyeZ jobs, 624 candidate references, 26 stations, 77 capture views, 73 evidence references, 26 inspections), with no null foreign keys. Constraints in the schema enforce the honesty rules — a count can never post an adjustment, a hold can never be released from CoreSync, an inspection can never release product or cause actuation, and a non-pass vision state can never be admitted as a pass.

Invariants the schema enforces

• Lifecycle, approval, deployment, disposition, runtime health and evidence maturity are separate status dimensions — never collapsed into one column.
• Original source time, recorded time and valid time are three distinct columns on every observation.
• Quantity kind, unit, quality, truth context and value origin are separate; none of them implies another.
• Evidence is append-only: a correction is a new row pointing back, and the original stays readable.
• Every record carries its tenant and a simulation flag, so no fixture row can be read as production evidence.
• Replay, backfill and reprocessing are modelled as distinct recovery operations, not one verb.

Where each record type lands

Every one of these is a row in cs_record with its kind-specific attributes in jsonb. See the coverage ledger for what is and is not demonstrated.