-- HSIMC registry P0-001. Independent local DEMO / design store, NOT identity or Work authority.
-- Every connection MUST enable foreign_keys. No production tables are attached.
PRAGMA foreign_keys = ON;
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE registry_scopes (
 id TEXT PRIMARY KEY, external_workspace_ref TEXT NOT NULL UNIQUE, label TEXT NOT NULL,
 source_kind TEXT NOT NULL CHECK(source_kind IN ('FIXTURE','READ_ONLY_PROJECTION')), revision INTEGER NOT NULL DEFAULT 1 CHECK(revision>0)
);
CREATE TABLE principal_refs (
 scope_id TEXT NOT NULL REFERENCES registry_scopes(id), id TEXT NOT NULL, external_id TEXT NOT NULL,
 kind TEXT NOT NULL CHECK(kind IN ('HUMAN','AGENT','SERVICE')), state TEXT NOT NULL CHECK(state IN ('ACTIVE','SUSPENDED','UNKNOWN')),
 source_kind TEXT NOT NULL CHECK(source_kind IN ('FIXTURE','READ_ONLY_PROJECTION')),
 observed_at TEXT NOT NULL, expires_at TEXT NOT NULL, PRIMARY KEY(scope_id,id), UNIQUE(scope_id,external_id)
);
CREATE TABLE parties (
 scope_id TEXT NOT NULL REFERENCES registry_scopes(id), id TEXT NOT NULL, kind TEXT NOT NULL CHECK(kind IN ('PERSON','ORGANIZATION')),
 label TEXT NOT NULL, external_subject_ref TEXT, verification TEXT NOT NULL CHECK(verification IN ('DECLARED','PENDING','FIXTURE_CONFIRMED','EXTERNAL_VERIFIED')),
 PRIMARY KEY(scope_id,id)
);
CREATE TABLE resources (
 scope_id TEXT NOT NULL REFERENCES registry_scopes(id), id TEXT NOT NULL, kind TEXT NOT NULL,
 title TEXT NOT NULL, summary TEXT NOT NULL DEFAULT '', visibility TEXT NOT NULL CHECK(visibility IN ('PRIVATE','WORK_SHARED','PUBLIC')),
 state TEXT NOT NULL CHECK(state IN ('DECLARED','EVIDENCED','UNAVAILABLE','WITHDRAWN')), version INTEGER NOT NULL CHECK(version>0),
 PRIMARY KEY(scope_id,id)
);
CREATE TABLE resource_versions (
 scope_id TEXT NOT NULL, resource_id TEXT NOT NULL, version INTEGER NOT NULL CHECK(version>0), content_sha256 TEXT NOT NULL CHECK(length(content_sha256)=64),
 locator TEXT NOT NULL, metadata_json TEXT NOT NULL CHECK(json_valid(metadata_json)), created_at TEXT NOT NULL,
 PRIMARY KEY(scope_id,resource_id,version), FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id)
);
CREATE TABLE resource_parties (
 scope_id TEXT NOT NULL, resource_id TEXT NOT NULL, party_id TEXT NOT NULL,
 role TEXT NOT NULL CHECK(role IN ('DECLARED_OWNER','CUSTODIAN','CONTROLLER','OPERATOR','LICENSOR','SUPERVISOR')),
 evidence_ref TEXT, PRIMARY KEY(scope_id,resource_id,party_id,role),
 FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id), FOREIGN KEY(scope_id,party_id) REFERENCES parties(scope_id,id)
);
CREATE TABLE evidence_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, title TEXT NOT NULL, locator TEXT NOT NULL,
 content_sha256 TEXT CHECK(content_sha256 IS NULL OR length(content_sha256)=64), source_kind TEXT NOT NULL,
 assurance TEXT NOT NULL CHECK(assurance IN ('DECLARED','PENDING','FIXTURE_CONFIRMED','EXTERNAL_VERIFIED')),
 observed_at TEXT NOT NULL, expires_at TEXT NOT NULL, PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id) REFERENCES registry_scopes(id)
);
CREATE TABLE authority_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, parent_id TEXT, resource_id TEXT NOT NULL, grantor_id TEXT NOT NULL, grantee_id TEXT NOT NULL,
 domain TEXT NOT NULL CHECK(domain IN ('LEGAL_RIGHTS','BUSINESS_DELEGATION','EXECUTION','DATA_CONSENT')),
 basis TEXT NOT NULL, effect TEXT NOT NULL CHECK(effect IN ('PERMIT','PROHIBIT')), actions_json TEXT NOT NULL CHECK(json_valid(actions_json)),
 constraints_json TEXT NOT NULL CHECK(json_valid(constraints_json)), can_delegate INTEGER NOT NULL CHECK(can_delegate IN (0,1)),
 state TEXT NOT NULL CHECK(state IN ('PENDING','VALID','REVOKED','DISPUTED')),
 valid_from TEXT NOT NULL, valid_until TEXT NOT NULL, evidence_id TEXT NOT NULL, revision INTEGER NOT NULL DEFAULT 1 CHECK(revision>0),
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id),
 FOREIGN KEY(scope_id,grantor_id) REFERENCES parties(scope_id,id), FOREIGN KEY(scope_id,grantee_id) REFERENCES parties(scope_id,id),
 FOREIGN KEY(scope_id,parent_id) REFERENCES authority_refs(scope_id,id) DEFERRABLE INITIALLY DEFERRED,
 FOREIGN KEY(scope_id,evidence_id) REFERENCES evidence_refs(scope_id,id), CHECK(parent_id IS NULL OR parent_id<>id), CHECK(valid_until>valid_from)
);
CREATE TABLE listings (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, direction TEXT NOT NULL CHECK(direction IN ('OFFER','NEED')),
 layer INTEGER NOT NULL CHECK(layer BETWEEN 1 AND 6), title TEXT NOT NULL, summary TEXT NOT NULL,
 publisher_id TEXT NOT NULL, beneficiary_id TEXT, resource_id TEXT,
 status TEXT NOT NULL CHECK(status IN ('DRAFT','PUBLISHED','WITHDRAWN')), visibility TEXT NOT NULL CHECK(visibility IN ('PRIVATE','PUBLIC')),
 conditions_json TEXT NOT NULL CHECK(json_valid(conditions_json)), version INTEGER NOT NULL DEFAULT 1 CHECK(version>0),
 created_at TEXT NOT NULL, PRIMARY KEY(scope_id,id),
 FOREIGN KEY(scope_id,publisher_id) REFERENCES parties(scope_id,id), FOREIGN KEY(scope_id,beneficiary_id) REFERENCES parties(scope_id,id),
 FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id)
);
CREATE TABLE requirements (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, need_id TEXT NOT NULL, layer INTEGER NOT NULL CHECK(layer BETWEEN 1 AND 6),
 title TEXT NOT NULL, resource_id TEXT NOT NULL, domain TEXT NOT NULL, action TEXT NOT NULL, grantee_id TEXT NOT NULL,
 request_json TEXT NOT NULL CHECK(json_valid(request_json)),
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,need_id) REFERENCES listings(scope_id,id),
 FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id), FOREIGN KEY(scope_id,grantee_id) REFERENCES parties(scope_id,id)
);
CREATE TABLE requirement_dependencies (
 scope_id TEXT NOT NULL, requirement_id TEXT NOT NULL, prerequisite_id TEXT NOT NULL,
 PRIMARY KEY(scope_id,requirement_id,prerequisite_id), CHECK(requirement_id<>prerequisite_id),
 FOREIGN KEY(scope_id,requirement_id) REFERENCES requirements(scope_id,id), FOREIGN KEY(scope_id,prerequisite_id) REFERENCES requirements(scope_id,id)
);
CREATE TABLE capability_bindings (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, resource_id TEXT NOT NULL, external_runtime_ref TEXT,
 external_capability_ref TEXT NOT NULL, binding_state TEXT NOT NULL CHECK(binding_state IN ('UNBOUND','CANDIDATE','VERIFIED_REFERENCE','WITHDRAWN')),
 action_schema_json TEXT NOT NULL CHECK(json_valid(action_schema_json)), PRIMARY KEY(scope_id,id),
 FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id)
);
CREATE TABLE availability_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, resource_id TEXT NOT NULL, units_available INTEGER NOT NULL CHECK(units_available>=0),
 valid_until TEXT NOT NULL, source_ref TEXT NOT NULL, assurance TEXT NOT NULL,
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id)
);
CREATE TABLE work_proposals (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, need_id TEXT NOT NULL, status TEXT NOT NULL CHECK(status IN ('DRAFT','NEEDS_CONDITIONS','CAN_PROPOSE','SUPERSEDED')),
 assessment_json TEXT NOT NULL CHECK(json_valid(assessment_json)), snapshot_sha256 TEXT NOT NULL CHECK(length(snapshot_sha256)=64),
 native_work_ref TEXT, native_work_revision TEXT, created_at TEXT NOT NULL, version INTEGER NOT NULL DEFAULT 1 CHECK(version>0),
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,need_id) REFERENCES listings(scope_id,id)
);
CREATE TABLE proposal_parties (
 scope_id TEXT NOT NULL, proposal_id TEXT NOT NULL, party_id TEXT NOT NULL,
 role TEXT NOT NULL CHECK(role IN ('REQUESTER','PROVIDER','RIGHTS_HOLDER','AUTHORIZER','OPERATOR','SUPERVISOR','ACCEPTOR','PAYER','PAYEE','BENEFICIARY')),
 PRIMARY KEY(scope_id,proposal_id,party_id,role), FOREIGN KEY(scope_id,proposal_id) REFERENCES work_proposals(scope_id,id),
 FOREIGN KEY(scope_id,party_id) REFERENCES parties(scope_id,id)
);
CREATE TABLE execution_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, proposal_id TEXT NOT NULL, native_work_ref TEXT NOT NULL, external_run_ref TEXT NOT NULL,
 observed_state TEXT NOT NULL CHECK(observed_state IN ('UNKNOWN','QUEUED','RUNNING','SUCCEEDED','FAILED','CANCELLED')),
 observed_at TEXT NOT NULL, source_ref TEXT NOT NULL, PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,proposal_id) REFERENCES work_proposals(scope_id,id)
);
CREATE TABLE artifact_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, resource_id TEXT NOT NULL, version INTEGER NOT NULL, source_run_ref TEXT,
 content_sha256 TEXT NOT NULL CHECK(length(content_sha256)=64), mime_type TEXT NOT NULL, byte_length INTEGER NOT NULL CHECK(byte_length>=0),
 rights_ref TEXT, publication_state TEXT NOT NULL CHECK(publication_state IN ('PRIVATE','APPROVED','PUBLISHED','WITHDRAWN')),
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,resource_id,version) REFERENCES resource_versions(scope_id,resource_id,version)
);
CREATE TABLE acceptance_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, proposal_id TEXT NOT NULL, acceptor_id TEXT NOT NULL,
 decision TEXT NOT NULL CHECK(decision IN ('PENDING','ACCEPTED','REWORK','REJECTED')), evidence_id TEXT NOT NULL, observed_at TEXT NOT NULL,
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,proposal_id) REFERENCES work_proposals(scope_id,id),
 FOREIGN KEY(scope_id,acceptor_id) REFERENCES parties(scope_id,id), FOREIGN KEY(scope_id,evidence_id) REFERENCES evidence_refs(scope_id,id)
);
CREATE TABLE commercial_terms (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, proposal_id TEXT NOT NULL, version INTEGER NOT NULL CHECK(version>0),
 payer_id TEXT NOT NULL, payee_id TEXT NOT NULL, currency TEXT NOT NULL CHECK(length(currency)=3), amount_minor INTEGER NOT NULL CHECK(amount_minor>=0),
 terms_json TEXT NOT NULL CHECK(json_valid(terms_json)), state TEXT NOT NULL CHECK(state IN ('DRAFT','AGREED','SUPERSEDED')),
 PRIMARY KEY(scope_id,id), FOREIGN KEY(scope_id,proposal_id) REFERENCES work_proposals(scope_id,id),
 FOREIGN KEY(scope_id,payer_id) REFERENCES parties(scope_id,id), FOREIGN KEY(scope_id,payee_id) REFERENCES parties(scope_id,id)
);
CREATE TABLE settlement_refs (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, terms_id TEXT NOT NULL, provider_ref TEXT NOT NULL, provider_event_id TEXT NOT NULL,
 state TEXT NOT NULL CHECK(state IN ('UNKNOWN','PENDING','CONFIRMED','REFUNDED')), amount_minor INTEGER NOT NULL CHECK(amount_minor>=0),
 currency TEXT NOT NULL, evidence_id TEXT NOT NULL, observed_at TEXT NOT NULL,
 PRIMARY KEY(scope_id,id), UNIQUE(scope_id,provider_ref,provider_event_id),
 FOREIGN KEY(scope_id,terms_id) REFERENCES commercial_terms(scope_id,id), FOREIGN KEY(scope_id,evidence_id) REFERENCES evidence_refs(scope_id,id)
);
CREATE TABLE source_links (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, resource_id TEXT NOT NULL, source_system TEXT NOT NULL, source_id TEXT NOT NULL,
 source_revision TEXT NOT NULL, content_sha256 TEXT, observed_at TEXT NOT NULL,
 PRIMARY KEY(scope_id,id), UNIQUE(scope_id,source_system,source_id,source_revision), FOREIGN KEY(scope_id,resource_id) REFERENCES resources(scope_id,id)
);
CREATE TABLE operation_receipts (
 scope_id TEXT NOT NULL, operation_key TEXT NOT NULL, actor_ref TEXT NOT NULL, input_sha256 TEXT NOT NULL,
 status TEXT NOT NULL CHECK(status IN ('COMPLETED','UNKNOWN_OUTCOME')), result_json TEXT NOT NULL CHECK(json_valid(result_json)),
 created_at TEXT NOT NULL, PRIMARY KEY(scope_id,operation_key), FOREIGN KEY(scope_id,actor_ref) REFERENCES principal_refs(scope_id,id)
);
CREATE TABLE handoff_outbox (
 scope_id TEXT NOT NULL, id TEXT NOT NULL, proposal_id TEXT NOT NULL, payload_json TEXT NOT NULL CHECK(json_valid(payload_json)),
 state TEXT NOT NULL CHECK(state IN ('DRAFT_NOT_SENT','ACKNOWLEDGED','UNKNOWN_OUTCOME')),
 native_work_ref TEXT, created_at TEXT NOT NULL, PRIMARY KEY(scope_id,id),
 FOREIGN KEY(scope_id,proposal_id) REFERENCES work_proposals(scope_id,id)
);
CREATE TABLE audit_events (
 seq INTEGER PRIMARY KEY AUTOINCREMENT, scope_id TEXT NOT NULL, actor_ref TEXT NOT NULL, event_type TEXT NOT NULL,
 object_id TEXT NOT NULL, event_json TEXT NOT NULL CHECK(json_valid(event_json)), created_at TEXT NOT NULL,
 FOREIGN KEY(scope_id,actor_ref) REFERENCES principal_refs(scope_id,id)
);
CREATE TRIGGER audit_no_update BEFORE UPDATE ON audit_events BEGIN SELECT RAISE(ABORT,'APPEND_ONLY_AUDIT'); END;
CREATE TRIGGER audit_no_delete BEFORE DELETE ON audit_events BEGIN SELECT RAISE(ABORT,'APPEND_ONLY_AUDIT'); END;
CREATE TRIGGER versions_no_update BEFORE UPDATE ON resource_versions BEGIN SELECT RAISE(ABORT,'IMMUTABLE_VERSION'); END;
CREATE TRIGGER versions_no_delete BEFORE DELETE ON resource_versions BEGIN SELECT RAISE(ABORT,'IMMUTABLE_VERSION'); END;
CREATE INDEX listing_scope_direction_layer ON listings(scope_id,direction,layer,status);
CREATE INDEX grant_scope_resource ON authority_refs(scope_id,resource_id,domain,state);
CREATE INDEX requirements_need ON requirements(scope_id,need_id);
CREATE INDEX audit_scope_seq ON audit_events(scope_id,seq);