← back to Unclaimed Property Platform
auto-data-snapshot: 2026-08-13T15:04:38 (1 data files) — db/schema.sql
10056f2e660cfcbf10c1fc1412456bef162bebfd · 2026-08-13 15:04:43 -0700 · auto-commit-fleet
Files touched
Diff
commit 10056f2e660cfcbf10c1fc1412456bef162bebfd
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date: Thu Aug 13 15:04:43 2026 -0700
auto-data-snapshot: 2026-08-13T15:04:38 (1 data files) — db/schema.sql
---
db/schema.sql | 170 ----------------------------------------------------------
1 file changed, 170 deletions(-)
diff --git a/db/schema.sql b/db/schema.sql
deleted file mode 100644
index a06c31e..0000000
--- a/db/schema.sql
+++ /dev/null
@@ -1,170 +0,0 @@
--- Canonical relational schema — PostgreSQL dialect.
--- The prototype (services/common/sqlite_repo.py) uses a SQLite-compatible subset.
--- Design principle: raw data is NEVER overwritten; canonical fields live alongside it;
--- every displayed datum is traceable to a source batch (provenance).
-
--- ---------------------------------------------------------------------------
--- Jurisdiction configuration
--- ---------------------------------------------------------------------------
-CREATE TABLE jurisdiction (
- jurisdiction_id TEXT PRIMARY KEY, -- e.g. 'CA'
- name TEXT NOT NULL,
- status TEXT NOT NULL DEFAULT 'prospect' -- prospect|contracted|live|paused
-);
-
-CREATE TABLE jurisdiction_policy (
- policy_id TEXT PRIMARY KEY,
- jurisdiction_id TEXT NOT NULL REFERENCES jurisdiction(jurisdiction_id),
- version INTEGER NOT NULL,
- effective_from TIMESTAMP NOT NULL,
- effective_to TIMESTAMP,
- -- masking / claim tier / retention / required evidence / search limits (JSON)
- config_json TEXT NOT NULL
-);
-
--- ---------------------------------------------------------------------------
--- Ingestion + provenance
--- ---------------------------------------------------------------------------
-CREATE TABLE source_feed (
- feed_id TEXT PRIMARY KEY,
- jurisdiction_id TEXT NOT NULL REFERENCES jurisdiction(jurisdiction_id),
- format_name TEXT NOT NULL, -- naupa2 | naupa3 | state_csv_v1 | ...
- cadence TEXT,
- transport TEXT
-);
-
-CREATE TABLE ingestion_batch (
- batch_id TEXT PRIMARY KEY,
- feed_id TEXT REFERENCES source_feed(feed_id),
- jurisdiction_id TEXT NOT NULL,
- source_uri TEXT,
- checksum TEXT NOT NULL, -- sha256 of the source file
- parser_version TEXT NOT NULL,
- received_at TIMESTAMP NOT NULL,
- accepted_count INTEGER NOT NULL DEFAULT 0,
- rejected_count INTEGER NOT NULL DEFAULT 0,
- status TEXT NOT NULL, -- running|completed|completed_with_errors|failed|duplicate
- expected_count INTEGER, -- state-declared control total (NAUPA trailer in prod)
- reconciliation TEXT, -- ok|short|over|unknown (accepted+rejected vs expected)
- UNIQUE (jurisdiction_id, checksum) -- idempotency: same file never processed twice
-);
-
-CREATE TABLE raw_object (
- raw_object_id TEXT PRIMARY KEY,
- batch_id TEXT NOT NULL REFERENCES ingestion_batch(batch_id),
- storage_uri TEXT NOT NULL,
- checksum TEXT NOT NULL
-);
-
--- ---------------------------------------------------------------------------
--- Canonical property + owners (non-destructive history)
--- ---------------------------------------------------------------------------
-CREATE TABLE property (
- property_id TEXT PRIMARY KEY, -- surrogate
- jurisdiction_id TEXT NOT NULL,
- source_property_id TEXT NOT NULL, -- the state's record id
- holder_name_raw TEXT,
- property_type TEXT,
- amount NUMERIC,
- status TEXT NOT NULL DEFAULT 'active',
- UNIQUE (jurisdiction_id, source_property_id)
-);
-
-CREATE TABLE property_version (
- property_version_id TEXT PRIMARY KEY,
- property_id TEXT NOT NULL REFERENCES property(property_id),
- batch_id TEXT NOT NULL REFERENCES ingestion_batch(batch_id),
- effective_from TIMESTAMP NOT NULL,
- effective_to TIMESTAMP,
- raw_payload TEXT NOT NULL, -- the exact source row (JSON)
- raw_record_hash TEXT NOT NULL -- dedup / forensic compare
-);
-
-CREATE TABLE owner (
- owner_id TEXT PRIMARY KEY,
- property_id TEXT NOT NULL REFERENCES property(property_id),
- owner_type TEXT NOT NULL DEFAULT 'person', -- person|business
- owner_name_raw TEXT NOT NULL,
- owner_name_normalized TEXT NOT NULL,
- city_normalized TEXT,
- region TEXT,
- postal_code TEXT
-);
-
--- Optional real-world entity clustering (non-destructive; never a hard merge)
-CREATE TABLE canonical_entity (
- entity_id TEXT PRIMARY KEY,
- entity_type TEXT NOT NULL,
- status TEXT NOT NULL DEFAULT 'candidate'
-);
-
-CREATE TABLE entity_link (
- entity_link_id TEXT PRIMARY KEY,
- owner_id TEXT NOT NULL REFERENCES owner(owner_id),
- entity_id TEXT NOT NULL REFERENCES canonical_entity(entity_id),
- score REAL NOT NULL,
- model_version TEXT NOT NULL,
- review_status TEXT NOT NULL DEFAULT 'unreviewed' -- unreviewed|confirmed|rejected
-);
-
--- ---------------------------------------------------------------------------
--- Search publication control (what the public index is allowed to show)
--- ---------------------------------------------------------------------------
-CREATE TABLE search_document_state (
- property_id TEXT PRIMARY KEY REFERENCES property(property_id),
- index_version INTEGER NOT NULL DEFAULT 0,
- is_public BOOLEAN NOT NULL DEFAULT 1,
- is_suppressed BOOLEAN NOT NULL DEFAULT 0,
- owner_name_masked TEXT,
- amount_band TEXT
-);
-
--- ---------------------------------------------------------------------------
--- Claims (state machine + transactional outbox)
--- ---------------------------------------------------------------------------
-CREATE TABLE claim_case (
- claim_id TEXT PRIMARY KEY,
- jurisdiction_id TEXT NOT NULL,
- public_property_reference TEXT NOT NULL,
- claimant_id TEXT,
- status TEXT NOT NULL,
- version INTEGER NOT NULL DEFAULT 1,
- state_case_id TEXT
-);
-
-CREATE TABLE claim_event ( -- append-only timeline
- claim_event_id TEXT PRIMARY KEY,
- claim_id TEXT NOT NULL REFERENCES claim_case(claim_id),
- event_type TEXT NOT NULL,
- payload TEXT NOT NULL,
- idempotency_key TEXT NOT NULL,
- created_at TIMESTAMP NOT NULL,
- UNIQUE (claim_id, idempotency_key)
-);
-
-CREATE TABLE outbox_event ( -- transactional outbox → reliable state integration
- outbox_event_id TEXT PRIMARY KEY,
- aggregate_id TEXT NOT NULL,
- event_type TEXT NOT NULL,
- payload TEXT NOT NULL,
- delivery_state TEXT NOT NULL DEFAULT 'pending', -- pending|sent|failed
- attempts INTEGER NOT NULL DEFAULT 0,
- last_error TEXT
-);
-
--- ---------------------------------------------------------------------------
--- Governance
--- ---------------------------------------------------------------------------
-CREATE TABLE audit_event (
- audit_event_id TEXT PRIMARY KEY,
- actor TEXT,
- action TEXT NOT NULL,
- resource TEXT,
- before_hash TEXT,
- after_hash TEXT,
- created_at TIMESTAMP NOT NULL
-);
-
-CREATE INDEX idx_owner_name_norm ON owner(owner_name_normalized);
-CREATE INDEX idx_owner_region ON owner(region);
-CREATE INDEX idx_property_jur ON property(jurisdiction_id);
← 50995a7 auto-data-snapshot: 2026-08-07T11:47:33 (2 data files) — db/
·
back to Unclaimed Property Platform
·
(newest)