-- === 0001_p01_core.sql === -- OctoRoam Intel P0.1 Core Persistent Fact Layer (13 tables: 3 meta + 10 intel) -- Strictly zero industry-hardcoded columns; multi-tenant + visibility pre-embedded on all intel.* tables. CREATE SCHEMA IF NOT EXISTS meta; CREATE SCHEMA IF NOT EXISTS intel; -- 1. meta.schema_migrations CREATE TABLE IF NOT EXISTS meta.schema_migrations ( version TEXT PRIMARY KEY, name TEXT NOT NULL, checksum TEXT NOT NULL, applied_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 2. meta.schema_registry CREATE TABLE IF NOT EXISTS meta.schema_registry ( schema_id TEXT NOT NULL, version TEXT NOT NULL, kind TEXT NOT NULL CHECK (kind IN ('artifact', 'claim_value', 'qualifiers', 'field_def')), json_schema JSONB NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'deprecated', 'draft')), created_by TEXT NOT NULL DEFAULT 'system', created_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (schema_id, version) ); -- 3. meta.field_defs CREATE TABLE IF NOT EXISTS meta.field_defs ( field_key TEXT PRIMARY KEY, entity_type TEXT NOT NULL, domain_pack TEXT NOT NULL, tier TEXT NOT NULL CHECK (tier IN ('tier_a', 'tier_b')), value_type TEXT NOT NULL, canonical_unit TEXT NOT NULL DEFAULT '', unit_rules_json JSONB NOT NULL DEFAULT '{}'::jsonb, qualifiers_schema JSONB NOT NULL DEFAULT '{}'::jsonb, aliases_json JSONB NOT NULL DEFAULT '[]'::jsonb, min_independent_sources INT NOT NULL DEFAULT 1, authority_tier TEXT NOT NULL DEFAULT 'official_or_verified', default_stale_days INT NOT NULL DEFAULT 180, governance_status TEXT NOT NULL DEFAULT 'active' CHECK (governance_status IN ('active', 'proposed', 'deprecated')), schema_version TEXT NOT NULL DEFAULT 'v1', updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 4. intel.blobs CREATE TABLE IF NOT EXISTS intel.blobs ( blob_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), sha256 TEXT NOT NULL, byte_size BIGINT NOT NULL CHECK (byte_size >= 0), mime_type TEXT NOT NULL DEFAULT 'application/octet-stream', storage_backend TEXT NOT NULL DEFAULT 'local_cas', storage_key TEXT NOT NULL, source_url TEXT NOT NULL DEFAULT '', metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, visibility, sha256) ); -- 5. intel.entities CREATE TABLE IF NOT EXISTS intel.entities ( entity_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), entity_type TEXT NOT NULL CHECK (entity_type IN ('organization', 'brand', 'series', 'model', 'sku')), canonical_key TEXT NOT NULL, display_name TEXT NOT NULL, parent_entity_id TEXT REFERENCES intel.entities(entity_id) ON DELETE SET NULL, domain_pack TEXT NOT NULL DEFAULT 'common', metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, entity_type, canonical_key) ); CREATE INDEX IF NOT EXISTS idx_entities_parent ON intel.entities (tenant_id, parent_entity_id); CREATE INDEX IF NOT EXISTS idx_entities_visibility ON intel.entities (visibility, entity_type, canonical_key); -- 6. intel.entity_aliases CREATE TABLE IF NOT EXISTS intel.entity_aliases ( alias_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), entity_id TEXT NOT NULL REFERENCES intel.entities(entity_id) ON DELETE CASCADE, alias_type TEXT NOT NULL CHECK (alias_type IN ('official_domain', 'product_url', 'brand_name', 'model_title', 'sku_code')), raw_value TEXT NOT NULL, normalized_value TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, alias_type, normalized_value) ); CREATE INDEX IF NOT EXISTS idx_entity_aliases_entity ON intel.entity_aliases (tenant_id, entity_id); -- 7. intel.research_runs (strictly tenant_private) CREATE TABLE IF NOT EXISTS intel.research_runs ( run_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility = 'tenant_private'), job_id TEXT, source_job_key TEXT, root_entity_id TEXT REFERENCES intel.entities(entity_id) ON DELETE SET NULL, domain_pack TEXT NOT NULL DEFAULT 'mobility_ebike', pipeline_version TEXT NOT NULL DEFAULT 'p0.1', policy_version TEXT NOT NULL DEFAULT 'p0.1-default', status TEXT NOT NULL DEFAULT 'running', report_status TEXT NOT NULL DEFAULT 'draft', request_json JSONB NOT NULL DEFAULT '{}'::jsonb, usage_json JSONB NOT NULL DEFAULT '{}'::jsonb, error_json JSONB, started_at TIMESTAMPTZ NOT NULL DEFAULT now(), finished_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, source_job_key) ); CREATE INDEX IF NOT EXISTS idx_runs_job ON intel.research_runs (tenant_id, job_id); -- 8. intel.artifacts (immutable stage artifacts, including report_snapshot.v1) CREATE TABLE IF NOT EXISTS intel.artifacts ( artifact_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), run_id TEXT NOT NULL REFERENCES intel.research_runs(run_id) ON DELETE CASCADE, stage_key TEXT NOT NULL, schema_id TEXT NOT NULL, schema_version TEXT NOT NULL, content_sha256 TEXT NOT NULL, payload_jsonb JSONB, blob_id TEXT REFERENCES intel.blobs(blob_id) ON DELETE SET NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, run_id, stage_key, schema_id, content_sha256) ); CREATE INDEX IF NOT EXISTS idx_artifacts_run ON intel.artifacts (tenant_id, run_id, stage_key); -- 9. intel.sources CREATE TABLE IF NOT EXISTS intel.sources ( source_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), domain TEXT NOT NULL DEFAULT '', canonical_url TEXT NOT NULL, source_type TEXT NOT NULL CHECK (source_type IN ('official', 'registry', 'third_party', 'user_supplied')), title TEXT NOT NULL DEFAULT '', publisher TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, visibility, canonical_url) ); -- 10. intel.source_snapshots (strictly tenant_private) CREATE TABLE IF NOT EXISTS intel.source_snapshots ( snapshot_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility = 'tenant_private'), source_id TEXT NOT NULL REFERENCES intel.sources(source_id) ON DELETE CASCADE, run_id TEXT REFERENCES intel.research_runs(run_id) ON DELETE SET NULL, http_status INT, final_url TEXT NOT NULL DEFAULT '', content_sha256 TEXT NOT NULL, blob_id TEXT REFERENCES intel.blobs(blob_id) ON DELETE SET NULL, excerpt TEXT NOT NULL DEFAULT '', fetch_method TEXT NOT NULL DEFAULT 'bounded_public_crawl', fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, source_id, run_id, content_sha256) ); -- 11. intel.claims CREATE TABLE IF NOT EXISTS intel.claims ( claim_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), entity_id TEXT NOT NULL REFERENCES intel.entities(entity_id) ON DELETE CASCADE, field_key TEXT NOT NULL, field_tier TEXT NOT NULL CHECK (field_tier IN ('tier_a', 'tier_b')), raw_label TEXT NOT NULL DEFAULT '', qualifiers_json JSONB NOT NULL DEFAULT '{}'::jsonb, value_json JSONB NOT NULL DEFAULT 'null'::jsonb, normalized_value_json JSONB, unit TEXT NOT NULL DEFAULT '', canonical_unit TEXT NOT NULL DEFAULT '', raw_text TEXT NOT NULL DEFAULT '', verification_status TEXT NOT NULL CHECK (verification_status IN ('unverified', 'auto_sourced', 'conflicting', 'human_confirmed', 'human_rejected')), lifecycle_status TEXT NOT NULL DEFAULT 'active' CHECK (lifecycle_status IN ('active', 'superseded', 'retracted')), observed_at TIMESTAMPTZ NOT NULL DEFAULT now(), valid_from TIMESTAMPTZ, valid_to TIMESTAMPTZ, superseded_by_claim_id TEXT REFERENCES intel.claims(claim_id) ON DELETE SET NULL, first_run_id TEXT REFERENCES intel.research_runs(run_id) ON DELETE SET NULL, last_run_id TEXT REFERENCES intel.research_runs(run_id) ON DELETE SET NULL, claim_fingerprint TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, claim_fingerprint) ); CREATE INDEX IF NOT EXISTS idx_claims_qualifiers_gin ON intel.claims USING GIN (qualifiers_json); CREATE INDEX IF NOT EXISTS idx_claims_value_gin ON intel.claims USING GIN (value_json); CREATE INDEX IF NOT EXISTS idx_claims_lookup ON intel.claims (tenant_id, entity_id, field_key, lifecycle_status); -- 12. intel.claim_evidence CREATE TABLE IF NOT EXISTS intel.claim_evidence ( evidence_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), claim_id TEXT NOT NULL REFERENCES intel.claims(claim_id) ON DELETE CASCADE, snapshot_id TEXT REFERENCES intel.source_snapshots(snapshot_id) ON DELETE SET NULL, blob_id TEXT REFERENCES intel.blobs(blob_id) ON DELETE SET NULL, locator_json JSONB NOT NULL DEFAULT '{}'::jsonb, quote_excerpt TEXT NOT NULL DEFAULT '', extractor_id TEXT NOT NULL DEFAULT 'normalizer.v1', evidence_fingerprint TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (tenant_id, evidence_fingerprint) ); CREATE INDEX IF NOT EXISTS idx_claim_evidence_claim ON intel.claim_evidence (tenant_id, claim_id); -- 13. intel.feedback CREATE TABLE IF NOT EXISTS intel.feedback ( feedback_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL DEFAULT 'tenant_default', visibility TEXT NOT NULL DEFAULT 'tenant_private' CHECK (visibility IN ('public_graph', 'tenant_private')), run_id TEXT REFERENCES intel.research_runs(run_id) ON DELETE SET NULL, target_type TEXT NOT NULL, target_id TEXT NOT NULL, feedback_type TEXT NOT NULL, before_json JSONB NOT NULL DEFAULT '{}'::jsonb, after_json JSONB NOT NULL DEFAULT '{}'::jsonb, reason_code TEXT NOT NULL DEFAULT '', actor_type TEXT NOT NULL CHECK (actor_type IN ('human', 'system_deterministic')), actor_id TEXT NOT NULL, review_seconds DOUBLE PRECISION NOT NULL DEFAULT 0.0, notes TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_feedback_target ON intel.feedback (tenant_id, target_type, target_id); -- === 0002_p01_hardening.sql === -- OctoRoam Intel P0.1 Database-Level Invariants & Hardening -- Enforces governance provenance, artifact immutability, evidence backing, -- human review redlines, and entity/alias visibility consistency inside PostgreSQL. -- a. meta.field_defs governance provenance ALTER TABLE meta.field_defs ADD COLUMN IF NOT EXISTS approved_by TEXT NOT NULL DEFAULT 'system_seed', ADD COLUMN IF NOT EXISTS approved_at TIMESTAMPTZ NOT NULL DEFAULT now(); ALTER TABLE meta.field_defs DROP CONSTRAINT IF EXISTS chk_field_defs_approved_by_nonempty; ALTER TABLE meta.field_defs ADD CONSTRAINT chk_field_defs_approved_by_nonempty CHECK (btrim(approved_by) <> ''); -- b. intel.artifacts immutability trigger (no UPDATE; DELETE only with explicit purge setting) CREATE OR REPLACE FUNCTION intel.enforce_artifacts_immutable() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'UPDATE' THEN RAISE EXCEPTION 'intel.artifacts is immutable and cannot be updated'; END IF; IF TG_OP = 'DELETE' THEN IF COALESCE(current_setting('octu.allow_artifact_purge', true), '') <> 'true' THEN RAISE EXCEPTION 'intel.artifacts is immutable and cannot be deleted unless octu.allow_artifact_purge=true'; END IF; RETURN OLD; END IF; RETURN NULL; END; $$; DROP TRIGGER IF EXISTS trg_artifacts_immutable ON intel.artifacts; CREATE TRIGGER trg_artifacts_immutable BEFORE UPDATE OR DELETE ON intel.artifacts FOR EACH ROW EXECUTE FUNCTION intel.enforce_artifacts_immutable(); -- c. intel.claims + intel.claim_evidence deferred constraint triggers CREATE OR REPLACE FUNCTION intel.enforce_claim_has_evidence() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.lifecycle_status = 'active' AND NEW.verification_status IN ('auto_sourced', 'conflicting') THEN IF NOT EXISTS ( SELECT 1 FROM intel.claim_evidence ev WHERE ev.claim_id = NEW.claim_id AND ev.tenant_id = NEW.tenant_id ) THEN RAISE EXCEPTION 'Active claim % (status=%) requires at least one matching intel.claim_evidence row', NEW.claim_id, NEW.verification_status; END IF; END IF; RETURN NULL; END; $$; DROP TRIGGER IF EXISTS trg_claims_require_evidence ON intel.claims; CREATE CONSTRAINT TRIGGER trg_claims_require_evidence AFTER INSERT OR UPDATE ON intel.claims DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION intel.enforce_claim_has_evidence(); CREATE OR REPLACE FUNCTION intel.enforce_evidence_not_emptied() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE parent_status TEXT; BEGIN IF TG_OP = 'UPDATE' AND OLD.claim_id = NEW.claim_id AND OLD.tenant_id = NEW.tenant_id THEN RETURN NULL; END IF; SELECT c.verification_status INTO parent_status FROM intel.claims c WHERE c.claim_id = OLD.claim_id AND c.tenant_id = OLD.tenant_id AND c.lifecycle_status = 'active' AND c.verification_status IN ('auto_sourced', 'conflicting'); IF FOUND THEN IF NOT EXISTS ( SELECT 1 FROM intel.claim_evidence ev WHERE ev.claim_id = OLD.claim_id AND ev.tenant_id = OLD.tenant_id ) THEN RAISE EXCEPTION 'Cannot remove last evidence row from active % claim %', parent_status, OLD.claim_id; END IF; END IF; RETURN NULL; END; $$; DROP TRIGGER IF EXISTS trg_claim_evidence_nonempty ON intel.claim_evidence; CREATE CONSTRAINT TRIGGER trg_claim_evidence_nonempty AFTER UPDATE OR DELETE ON intel.claim_evidence DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION intel.enforce_evidence_not_emptied(); -- d. human_confirmed / human_rejected DB-level redline triggers on intel.claims CREATE OR REPLACE FUNCTION intel.enforce_human_actor_before() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.verification_status IN ('human_confirmed', 'human_rejected') AND (TG_OP = 'INSERT' OR OLD.verification_status IS DISTINCT FROM NEW.verification_status) THEN IF COALESCE(current_setting('octu.actor_type', true), '') <> 'human' OR btrim(COALESCE(current_setting('octu.actor_id', true), '')) = '' THEN RAISE EXCEPTION 'Setting verification_status=% on claim % requires octu.actor_type=human and non-empty octu.actor_id', NEW.verification_status, NEW.claim_id; END IF; END IF; RETURN NEW; END; $$; DROP TRIGGER IF EXISTS trg_claims_human_actor_before ON intel.claims; CREATE TRIGGER trg_claims_human_actor_before BEFORE INSERT OR UPDATE ON intel.claims FOR EACH ROW EXECUTE FUNCTION intel.enforce_human_actor_before(); CREATE OR REPLACE FUNCTION intel.enforce_human_feedback_after() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.verification_status IN ('human_confirmed', 'human_rejected') THEN IF NOT EXISTS ( SELECT 1 FROM intel.feedback fb WHERE fb.tenant_id = NEW.tenant_id AND fb.target_type = 'claim' AND fb.target_id = NEW.claim_id AND fb.actor_type = 'human' AND fb.feedback_type = NEW.verification_status ) THEN RAISE EXCEPTION 'Claim % with verification_status=% requires a matching human row in intel.feedback', NEW.claim_id, NEW.verification_status; END IF; END IF; RETURN NULL; END; $$; DROP TRIGGER IF EXISTS trg_claims_human_feedback_after ON intel.claims; CREATE CONSTRAINT TRIGGER trg_claims_human_feedback_after AFTER INSERT OR UPDATE ON intel.claims DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION intel.enforce_human_feedback_after(); -- e. intel.entities vs intel.entity_aliases visibility consistency triggers CREATE OR REPLACE FUNCTION intel.enforce_entity_alias_visibility() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE ent_tenant TEXT; ent_visibility TEXT; BEGIN SELECT e.tenant_id, e.visibility INTO ent_tenant, ent_visibility FROM intel.entities e WHERE e.entity_id = NEW.entity_id; IF NOT FOUND THEN RAISE EXCEPTION 'Target entity % does not exist', NEW.entity_id; END IF; IF ent_visibility = 'tenant_private' THEN IF NEW.visibility <> 'tenant_private' OR NEW.tenant_id <> ent_tenant THEN RAISE EXCEPTION 'Entity % is tenant_private (tenant=%), alias must have visibility=tenant_private and matching tenant_id (got visibility=%, tenant=%)', NEW.entity_id, ent_tenant, NEW.visibility, NEW.tenant_id; END IF; ELSIF ent_visibility = 'public_graph' AND NEW.tenant_id <> ent_tenant THEN IF NEW.visibility <> 'tenant_private' THEN RAISE EXCEPTION 'Cross-tenant alias on public_graph entity % (owner=%) must have visibility=tenant_private (got %)', NEW.entity_id, ent_tenant, NEW.visibility; END IF; END IF; RETURN NEW; END; $$; DROP TRIGGER IF EXISTS trg_entity_aliases_visibility ON intel.entity_aliases; CREATE TRIGGER trg_entity_aliases_visibility BEFORE INSERT OR UPDATE ON intel.entity_aliases FOR EACH ROW EXECUTE FUNCTION intel.enforce_entity_alias_visibility(); CREATE OR REPLACE FUNCTION intel.enforce_entity_visibility_demotion() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (OLD.visibility = 'public_graph' AND NEW.visibility = 'tenant_private') OR (NEW.visibility = 'tenant_private' AND OLD.tenant_id IS DISTINCT FROM NEW.tenant_id) THEN IF EXISTS ( SELECT 1 FROM intel.entity_aliases a WHERE a.entity_id = NEW.entity_id AND (a.visibility = 'public_graph' OR a.tenant_id <> NEW.tenant_id) ) THEN RAISE EXCEPTION 'Cannot change entity % to tenant_private/tenant=% while public_graph or cross-tenant aliases exist', NEW.entity_id, NEW.tenant_id; END IF; END IF; RETURN NEW; END; $$; DROP TRIGGER IF EXISTS trg_entities_visibility_demotion ON intel.entities; CREATE TRIGGER trg_entities_visibility_demotion BEFORE UPDATE ON intel.entities FOR EACH ROW EXECUTE FUNCTION intel.enforce_entity_visibility_demotion();