-- erp-platform's own tables (erp_platform.public). Applied by `erp-compose migrate` before the bricks, and by
-- `erp-migrate --platform`. Platform-owned so a brick can issue public links without owning a door, and so two
-- bricks in one bucket never both claim the table. The `erp_` prefix is reserved for the platform.
-- One public link: one person, one subject, one grant. The SECRET IS NEVER STORED — only its sha256.
-- Revocation is a column, never a delete. seen (first/last/count) is kept apart from used_at (the act); both are
-- a projection of the event rows below, which are the record.
CREATE TABLE IF NOT EXISTS erp_public_link (
token_hash VARCHAR NOT NULL, -- sha256 hex of secrets.token_urlsafe(32)
link_id VARCHAR NOT NULL, -- the handle bricks use; unrelated to the secret
subject_kind VARCHAR NOT NULL, -- contract | intake | …, the issuing brick's word
subject_id VARCHAR NOT NULL,
issued_by_brick VARCHAR NOT NULL, -- whose handler serves it
recipient VARCHAR NOT NULL, -- the person it was sent to, lower-case
grant_type VARCHAR NOT NULL, -- READ | APPROVE | SIGN | SUBMIT | TRACK
destination VARCHAR, -- TRACK click links only: where it goes (allow-listed)
issued_at TIMESTAMP NOT NULL, -- UTC
issued_by VARCHAR,
expires_at TIMESTAMP, -- null = never
revoked_at TIMESTAMP,
revoked_by VARCHAR,
revoke_reason VARCHAR,
first_seen_at TIMESTAMP,
last_seen_at TIMESTAMP,
seen_count INTEGER,
used_at TIMESTAMP,
reissued_from VARCHAR, -- the link_id this one replaced
PRIMARY KEY (token_hash)
);
-- One row per issue, seen, use, refusal, revoke, reissue, open and click — with time, IP and user agent.
-- A once-only event (used, opened, clicked) has its NAME as event_id: claiming it is a plain INSERT, so a second
-- claim fails 23505 — the single-use act rests on that, and on nothing else.
CREATE TABLE IF NOT EXISTS erp_public_link_event (
link_id VARCHAR NOT NULL,
event_id VARCHAR NOT NULL, -- a uuid, or used | opened | clicked
kind VARCHAR NOT NULL,
event_at TIMESTAMP NOT NULL, -- UTC
actor VARCHAR,
ip VARCHAR,
user_agent VARCHAR,
detail VARCHAR,
PRIMARY KEY (link_id, event_id)
);
-- 0.5.0 — Idempotency-Key on the commands that are not idempotent by themselves (erp_platform.idempotency).
-- A claim is a PLAIN INSERT: a duplicate (caller, use case, key, generation) fails 23505, so exactly one request
-- with a key runs; a repeat replays the stored answer. Only a success is stored (status 200); a refusal or a
-- crash deletes its claim so the key may try again. A takeover after the lease inserts generation n+1.
CREATE TABLE IF NOT EXISTS erp_idempotency (
caller VARCHAR NOT NULL, -- the identity's sub
use_case VARCHAR NOT NULL, -- <brick>.<use case>
idempotency_key VARCHAR NOT NULL, -- the client's own string (Idempotency-Key / idempotency_key)
generation INTEGER NOT NULL,
request_hash VARCHAR NOT NULL, -- SHA-256 of the canonical input: same key, other request = 422
claimed_at TIMESTAMP NOT NULL, -- UTC
lease_until TIMESTAMP NOT NULL, -- an unfinished claim past this is taken over
status INTEGER, -- NULL while running; 200 once answered
response VARCHAR, -- the answer, as JSON, replayed to a repeat
finished_at TIMESTAMP,
PRIMARY KEY (caller, use_case, idempotency_key, generation)
);