109 lines
4.4 KiB
SQL
109 lines
4.4 KiB
SQL
-- Bell v5 Brain event ingress identity bindings and durable receipts.
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.event_ingress_bindings (
|
|
producer_id text NOT NULL,
|
|
tenant_id bigint NOT NULL,
|
|
site_id bigint NOT NULL,
|
|
device_id bigint NOT NULL,
|
|
logical_tenant_id text NOT NULL,
|
|
logical_site_id text NOT NULL,
|
|
logical_device_id text NOT NULL,
|
|
logical_area_id text NOT NULL,
|
|
modality text NOT NULL,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
PRIMARY KEY (producer_id, tenant_id, site_id, device_id),
|
|
CONSTRAINT bell_ingress_binding_site_fk
|
|
FOREIGN KEY (logical_tenant_id, logical_site_id)
|
|
REFERENCES bell.sites(tenant_id, id),
|
|
CONSTRAINT bell_ingress_binding_area_fk
|
|
FOREIGN KEY (logical_tenant_id, logical_area_id)
|
|
REFERENCES bell.areas(tenant_id, id),
|
|
CONSTRAINT bell_ingress_binding_device_fk
|
|
FOREIGN KEY (logical_tenant_id, logical_site_id, logical_device_id)
|
|
REFERENCES sense.devices(tenant_id, site_id, id),
|
|
CONSTRAINT bell_ingress_binding_numeric_ids CHECK (
|
|
tenant_id >= 1 AND site_id >= 1 AND device_id >= 1
|
|
),
|
|
CONSTRAINT bell_ingress_binding_producer CHECK (
|
|
char_length(producer_id) BETWEEN 1 AND 64
|
|
AND producer_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
|
|
),
|
|
CONSTRAINT bell_ingress_binding_logical_ids CHECK (
|
|
btrim(logical_tenant_id) <> '' AND btrim(logical_site_id) <> ''
|
|
AND btrim(logical_device_id) <> '' AND btrim(logical_area_id) <> ''
|
|
),
|
|
CONSTRAINT bell_ingress_binding_modality CHECK (
|
|
modality IN ('video', 'radar', 'contact', 'button', 'wearable', 'other')
|
|
)
|
|
);
|
|
ALTER TABLE bell.event_ingress_bindings OWNER TO bell_app;
|
|
|
|
CREATE INDEX IF NOT EXISTS bell_event_ingress_binding_numeric_idx
|
|
ON bell.event_ingress_bindings(tenant_id, site_id, device_id)
|
|
WHERE enabled;
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.event_ingress_receipts (
|
|
producer_id text NOT NULL,
|
|
source_event_id text NOT NULL,
|
|
candidate_hash bytea NOT NULL,
|
|
event_id text NOT NULL REFERENCES bell.events(id),
|
|
received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
PRIMARY KEY (producer_id, source_event_id),
|
|
CONSTRAINT bell_ingress_receipt_producer CHECK (
|
|
char_length(producer_id) BETWEEN 1 AND 64
|
|
AND producer_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
|
|
),
|
|
CONSTRAINT bell_ingress_receipt_source CHECK (
|
|
source_event_id ~ '^[A-Za-z0-9_-]{1,128}$'
|
|
),
|
|
CONSTRAINT bell_ingress_receipt_hash CHECK (octet_length(candidate_hash) = 32),
|
|
CONSTRAINT bell_ingress_receipt_event_id CHECK (
|
|
event_id ~ '^evt_[0-9A-HJKMNP-TV-Z]{26}$'
|
|
)
|
|
);
|
|
ALTER TABLE bell.event_ingress_receipts OWNER TO bell_app;
|
|
|
|
DROP TRIGGER IF EXISTS bell_event_ingress_receipts_immutable
|
|
ON bell.event_ingress_receipts;
|
|
CREATE TRIGGER bell_event_ingress_receipts_immutable
|
|
BEFORE UPDATE OR DELETE ON bell.event_ingress_receipts
|
|
FOR EACH ROW EXECUTE FUNCTION bell.reject_immutable_change();
|
|
|
|
CREATE INDEX IF NOT EXISTS bell_event_ingress_receipt_event_idx
|
|
ON bell.event_ingress_receipts(event_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.event_ingress_nonces (
|
|
key_id text NOT NULL,
|
|
nonce text NOT NULL,
|
|
request_hash bytea NOT NULL,
|
|
response_status integer NOT NULL,
|
|
response_body jsonb NOT NULL,
|
|
received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
expires_at timestamptz NOT NULL,
|
|
PRIMARY KEY (key_id, nonce),
|
|
CONSTRAINT bell_ingress_nonce_key CHECK (
|
|
char_length(key_id) BETWEEN 1 AND 64
|
|
AND key_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
|
|
),
|
|
CONSTRAINT bell_ingress_nonce_value CHECK (
|
|
char_length(nonce) BETWEEN 22 AND 64
|
|
AND nonce ~ '^[A-Za-z0-9_-]+$'
|
|
),
|
|
CONSTRAINT bell_ingress_nonce_hash CHECK (octet_length(request_hash) = 32),
|
|
CONSTRAINT bell_ingress_nonce_status CHECK (response_status IN (200, 201)),
|
|
CONSTRAINT bell_ingress_nonce_body CHECK (jsonb_typeof(response_body) = 'object'),
|
|
CONSTRAINT bell_ingress_nonce_ttl CHECK (
|
|
expires_at >= received_at + interval '10 minutes'
|
|
AND expires_at <= received_at + interval '11 minutes'
|
|
)
|
|
);
|
|
ALTER TABLE bell.event_ingress_nonces OWNER TO bell_app;
|
|
|
|
CREATE INDEX IF NOT EXISTS bell_event_ingress_nonce_expiry_idx
|
|
ON bell.event_ingress_nonces(expires_at, key_id, nonce);
|
|
|
|
INSERT INTO bell.schema_migrations(version) VALUES (5)
|
|
ON CONFLICT (version) DO NOTHING;
|