yovision/deploy/postgres/014_audit_relay.sql

-- Sense Outbox relay fencing and Bell global audit facts.

ALTER TABLE sense.device_operation_outbox
    ADD COLUMN IF NOT EXISTS relay_lease_owner text,
    ADD COLUMN IF NOT EXISTS relay_lease_token bigint NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS relay_lease_until timestamptz,
    ADD COLUMN IF NOT EXISTS last_error_code text,
    ADD COLUMN IF NOT EXISTS dead_lettered_at timestamptz;

ALTER TABLE sense.device_operation_outbox
    DROP CONSTRAINT IF EXISTS sense_outbox_relay_lease_pair;
ALTER TABLE sense.device_operation_outbox
    ADD CONSTRAINT sense_outbox_relay_lease_pair CHECK (
        (relay_lease_owner IS NULL AND relay_lease_until IS NULL)
        OR (relay_lease_owner IS NOT NULL AND btrim(relay_lease_owner) <> ''
            AND relay_lease_until IS NOT NULL)
    );
ALTER TABLE sense.device_operation_outbox
    DROP CONSTRAINT IF EXISTS sense_outbox_relay_token_nonnegative;
ALTER TABLE sense.device_operation_outbox
    ADD CONSTRAINT sense_outbox_relay_token_nonnegative CHECK (relay_lease_token >= 0);
ALTER TABLE sense.device_operation_outbox
    DROP CONSTRAINT IF EXISTS sense_outbox_relay_error_length;
ALTER TABLE sense.device_operation_outbox
    ADD CONSTRAINT sense_outbox_relay_error_length CHECK (
        last_error_code IS NULL OR (
            char_length(last_error_code) BETWEEN 1 AND 64
            AND last_error_code ~ '^[a-z][a-z0-9_]*$'
        )
    );
ALTER TABLE sense.device_operation_outbox
    DROP CONSTRAINT IF EXISTS sense_outbox_relay_terminal_state;
ALTER TABLE sense.device_operation_outbox
    ADD CONSTRAINT sense_outbox_relay_terminal_state CHECK (
        delivered_at IS NULL OR dead_lettered_at IS NULL
    );

CREATE INDEX IF NOT EXISTS sense_outbox_relay_due_idx
    ON sense.device_operation_outbox(
        COALESCE(next_attempt_at, available_at), event_id
    )
    WHERE delivered_at IS NULL AND dead_lettered_at IS NULL;

CREATE TABLE IF NOT EXISTS bell.audit_events (
    source_system text NOT NULL,
    event_id text NOT NULL,
    schema_version smallint NOT NULL,
    event_type text NOT NULL,
    tenant_id text NOT NULL,
    site_id text NOT NULL,
    device_id text NOT NULL,
    actor_type text NOT NULL,
    actor_id text NOT NULL,
    reason text,
    trace_id text,
    aggregate_generation bigint NOT NULL,
    quota_source_version bigint,
    area_policy_source_version bigint,
    payload jsonb NOT NULL,
    occurred_at timestamptz NOT NULL,
    received_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    record_hash bytea NOT NULL,
    PRIMARY KEY (source_system, event_id),
    CONSTRAINT bell_audit_source_system CHECK (source_system = 'sense'),
    CONSTRAINT bell_audit_event_id CHECK (event_id ~ '^audit_[0-9a-f]{32}$'),
    CONSTRAINT bell_audit_schema_version CHECK (schema_version IN (1, 2)),
    CONSTRAINT bell_audit_event_type CHECK (event_type IN (
        'device.created',
        'device.desired_state.accepted',
        'device.configuration.accepted'
    )),
    CONSTRAINT bell_audit_identity_not_blank CHECK (
        btrim(tenant_id) <> '' AND btrim(site_id) <> '' AND btrim(device_id) <> ''
        AND btrim(actor_id) <> ''
    ),
    CONSTRAINT bell_audit_actor_type CHECK (actor_type IN ('user', 'service', 'system')),
    CONSTRAINT bell_audit_generation_positive CHECK (aggregate_generation >= 1),
    CONSTRAINT bell_audit_projection_versions CHECK (
        (quota_source_version IS NULL OR quota_source_version >= 1)
        AND (area_policy_source_version IS NULL OR area_policy_source_version >= 1)
    ),
    CONSTRAINT bell_audit_reason_length CHECK (reason IS NULL OR char_length(reason) <= 500),
    CONSTRAINT bell_audit_trace_length CHECK (trace_id IS NULL OR char_length(trace_id) <= 128),
    CONSTRAINT bell_audit_payload_object CHECK (jsonb_typeof(payload) = 'object'),
    CONSTRAINT bell_audit_record_hash_length CHECK (octet_length(record_hash) = 32)
);
ALTER TABLE bell.audit_events OWNER TO bell_app;

DROP TRIGGER IF EXISTS bell_audit_events_immutable ON bell.audit_events;
CREATE TRIGGER bell_audit_events_immutable
BEFORE UPDATE OR DELETE ON bell.audit_events
FOR EACH ROW EXECUTE FUNCTION bell.reject_immutable_change();

CREATE INDEX IF NOT EXISTS bell_audit_scope_time_idx
    ON bell.audit_events(tenant_id, site_id, occurred_at DESC, event_id DESC);
CREATE INDEX IF NOT EXISTS bell_audit_device_time_idx
    ON bell.audit_events(tenant_id, site_id, device_id, occurred_at DESC, event_id DESC);

CREATE TABLE IF NOT EXISTS bell.audit_relay_receipts (
    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_audit_receipt_key_id CHECK (
        char_length(key_id) BETWEEN 1 AND 64 AND key_id ~ '^[A-Za-z0-9][A-Za-z0-9._-]*$'
    ),
    CONSTRAINT bell_audit_receipt_nonce CHECK (
        char_length(nonce) BETWEEN 22 AND 64 AND nonce ~ '^[A-Za-z0-9_-]+$'
    ),
    CONSTRAINT bell_audit_receipt_hash_length CHECK (octet_length(request_hash) = 32),
    CONSTRAINT bell_audit_receipt_status CHECK (response_status = 200),
    CONSTRAINT bell_audit_receipt_body CHECK (jsonb_typeof(response_body) = 'object'),
    CONSTRAINT bell_audit_receipt_ttl CHECK (
        expires_at >= received_at + interval '10 minutes'
        AND expires_at <= received_at + interval '11 minutes'
    )
);
ALTER TABLE bell.audit_relay_receipts OWNER TO bell_app;

CREATE INDEX IF NOT EXISTS bell_audit_receipt_expiry_idx
    ON bell.audit_relay_receipts(expires_at, key_id, nonce);

INSERT INTO bell.schema_migrations(version) VALUES (4)
ON CONFLICT (version) DO NOTHING;
INSERT INTO sense.schema_migrations(version) VALUES (6)
ON CONFLICT (version) DO NOTHING;