92 lines
2.8 KiB
SQL
92 lines
2.8 KiB
SQL
-- Initial schema for as4-411 directory (data-model.md)
|
|
-- Run with psql or migration runner; uses snake_case for columns.
|
|
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS participants (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_participants_tenant_id ON participants(tenant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS identifiers (
|
|
id TEXT PRIMARY KEY,
|
|
participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
|
|
identifier_type TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
scope TEXT,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
verified_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX idx_identifiers_lookup ON identifiers(identifier_type, value);
|
|
CREATE INDEX idx_identifiers_participant_id ON identifiers(participant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS endpoints (
|
|
id TEXT PRIMARY KEY,
|
|
participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
|
|
protocol TEXT NOT NULL,
|
|
address TEXT NOT NULL,
|
|
profile TEXT,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'draining'))
|
|
);
|
|
|
|
CREATE INDEX idx_endpoints_participant_id ON endpoints(participant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS capabilities (
|
|
id TEXT PRIMARY KEY,
|
|
participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
|
|
service TEXT,
|
|
action TEXT,
|
|
process TEXT,
|
|
document_type TEXT,
|
|
constraints_json JSONB
|
|
);
|
|
|
|
CREATE INDEX idx_capabilities_participant_id ON capabilities(participant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS credentials (
|
|
id TEXT PRIMARY KEY,
|
|
participant_id TEXT NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
|
|
credential_type TEXT NOT NULL CHECK (credential_type IN ('tls', 'sign', 'encrypt')),
|
|
vault_ref TEXT NOT NULL,
|
|
fingerprint TEXT,
|
|
valid_from TIMESTAMPTZ,
|
|
valid_to TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX idx_credentials_participant_id ON credentials(participant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS policies (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
|
rule_json JSONB NOT NULL DEFAULT '{}',
|
|
effect TEXT NOT NULL CHECK (effect IN ('allow', 'deny')),
|
|
priority INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX idx_policies_tenant_id ON policies(tenant_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id TEXT PRIMARY KEY,
|
|
at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
actor TEXT,
|
|
action TEXT NOT NULL,
|
|
resource TEXT NOT NULL,
|
|
resource_id TEXT NOT NULL,
|
|
payload JSONB,
|
|
hash_prev TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_audit_log_resource ON audit_log(resource, resource_id);
|