Skip to main content

Schema and Read-Model Contract Lock

Status​

Implemented in OP-081.

  • Phase 1 journey hardening requires schema drift to fail before deploy.
  • Agent onboarding-to-asset visibility depends on assets, approved_inventory, agent_enrollment_tokens, and agent_identities staying aligned across SQLite local tests and Postgres migrations.
  • Console-visible read models must preserve API shapes and aliases consumed by the frontend.

Problem Statement​

Recent deployed validation found bugs where a route or component looked correct in isolation but the production-shaped read model failed because SQLite schema, Postgres migrations, and console expectations had drifted. This caused production-only surprises such as missing columns or aliases in asset reads.

Architectural Intent​

OP-081 adds a cheap, always-on guardrail before broader deployed smoke probes. It does not replace Postgres runtime validation or persona journeys; it catches the earliest class of table/column and alias drift that should never reach AWS dev.

What Was Implemented​

  • Added scripts/validate_schema_read_models.py.
  • Introspects SQLite after init_db().
  • Statically parses db/postgres/*.sql for CREATE TABLE and additive ALTER TABLE ... ADD COLUMN IF NOT EXISTS migrations.
  • Fails when the initial critical tenant/asset/agent table set drifts by column name between SQLite and Postgres migrations.
  • Fails closed when critical-table migrations use unsupported shape-changing DDL such as column rename, drop, or type-change operations until the parser is extended to model them.
  • Reports type-class warnings such as SQLite text timestamps versus Postgres timestamptz.
  • Pins whitespace/case-tolerant asset read-model tokens including approved_by AS owner, mac_addresses_json, network_interfaces_json, open_ports_json, agent_id, asset_type, source_confidence, and approved_inventory.
  • Added make validate-read-model-contracts.
  • Wired the guardrail into CI backend_validate and full validation.
  • Added tests/test_schema_read_model_contracts.py so make test-sqlite also exercises the guardrail.

Components Involved​

  • scripts/validate_schema_read_models.py
  • tests/test_schema_read_model_contracts.py
  • poc/ingest_api/database.py
  • db/postgres/*.sql
  • poc/ingest_api/asset_read_model.py
  • Makefile
  • .gitlab-ci.yml
  • scripts/ci/validate.sh

APIs / Events / Schemas​

No API, event, OpenAPI, or AsyncAPI contracts changed. This is a validation guardrail for runtime persistence and console-facing read models.

Security / Tenant Isolation​

The first locked table set covers tenant, role, asset, approved inventory, agent enrollment token, and agent identity tables that form the tenant-scoped agent onboarding-to-asset journey. The guardrail helps prevent tenant-scoped read models from silently diverging between local tests and Postgres-backed AWS dev.

Validation Steps​

make validate-read-model-contracts
make test-sqlite

The full branch validation also runs:

make validate-contracts
make typecheck-python
make lint
make docs-build
git diff --cached --check

Known Limitations​

  • The first OP-081 table set is intentionally narrow: tenant, asset, approved inventory, enrollment token, and agent identity tables.
  • Static Postgres DDL parsing is not a substitute for a live Postgres information_schema comparison. It is the always-on gate that runs in local and CI contexts without requiring a live database.
  • Alerts, tickets, compliance, delivery, SCIM, Intune, and discovery read models should be added as their persona journeys are hardened under OP-083, OP-084, and OP-085.

Acceptance Criteria Mapping​

Acceptance criterionEvidence
Critical SQLite/Postgres tables are checkedscripts/validate_schema_read_models.py compares SQLite init_db() against parsed Postgres migrations
Schema drift fails before deploymake validate-read-model-contracts runs in backend_validate and full validation
Asset read-model aliases are pinnedThe validator checks poc/ingest_api/asset_read_model.py for critical tokens including approved_by AS owner
Guardrail is test-backedtests/test_schema_read_model_contracts.py runs under make test-sqlite