dodil/erp-brick-contracts

src/brick_contracts/schema.sql

-- brick-contracts: contracts, the clause library, templates and simple e-signature evidence. GENERATED from
-- src/brick_contracts/infrastructure/tables.py by scripts/gen_schema.py — edit tables.py, then regenerate.
-- Additive only: a new column goes at the END of its CREATE and as an explicit ALTER below it (erp-migrate refuses
-- DROP / RENAME / type changes). Natural keys only (never SERIAL); every child table's key starts with its
-- aggregate root's key. ct_signature_event is APPEND-ONLY (written by plain INSERT, never updated).
-- DataK3 enforces no foreign key: this brick being the ONLY writer of ct_* is the integrity.

CREATE TABLE IF NOT EXISTS ct_amendment (
    contract_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    kind VARCHAR,
    child_contract_id VARCHAR,
    note VARCHAR,
    recorded_by VARCHAR,
    recorded_at TIMESTAMP,
    PRIMARY KEY (contract_id, seq)
);

CREATE TABLE IF NOT EXISTS ct_approval (
    contract_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    draft_fingerprint VARCHAR,
    deviations_json VARCHAR,
    approver_group VARCHAR,
    fallback_group VARCHAR,
    original_group VARCHAR,
    due_at TIMESTAMP,
    state VARCHAR,
    decided_by VARCHAR,
    decided_at TIMESTAMP,
    reason VARCHAR,
    requested_by VARCHAR,
    requested_at TIMESTAMP,
    escalated_at TIMESTAMP,
    PRIMARY KEY (contract_id, seq)
);

CREATE TABLE IF NOT EXISTS ct_approval_policy (
    policy_code VARCHAR NOT NULL,
    rule_no INTEGER NOT NULL,
    applies_to VARCHAR,
    approver_group VARCHAR,
    fallback_group VARCHAR,
    timeout_working_days INTEGER,
    updated_at TIMESTAMP,
    PRIMARY KEY (policy_code, rule_no)
);

CREATE TABLE IF NOT EXISTS ct_clause (
    clause_id VARCHAR NOT NULL,
    family VARCHAR,
    position VARCHAR,
    deviation_level INTEGER,
    category VARCHAR,
    status VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (clause_id)
);

CREATE TABLE IF NOT EXISTS ct_clause_version (
    clause_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    title VARCHAR,
    body VARCHAR,
    created_by VARCHAR,
    created_at TIMESTAMP,
    PRIMARY KEY (clause_id, version)
);

CREATE TABLE IF NOT EXISTS ct_config_version (
    scope VARCHAR NOT NULL,
    token VARCHAR,
    changed_at TIMESTAMP,
    PRIMARY KEY (scope)
);

CREATE TABLE IF NOT EXISTS ct_consent_text (
    version VARCHAR NOT NULL,
    text VARCHAR,
    created_at TIMESTAMP,
    PRIMARY KEY (version)
);

CREATE TABLE IF NOT EXISTS ct_consumer (
    namespace VARCHAR NOT NULL,
    principals VARCHAR,
    owner_group VARCHAR,
    label VARCHAR,
    updated_at TIMESTAMP,
    PRIMARY KEY (namespace)
);

CREATE TABLE IF NOT EXISTS ct_contract (
    contract_id VARCHAR NOT NULL,
    type_code VARCHAR,
    template_id VARCHAR,
    template_version INTEGER,
    bp_id BIGINT,
    context_ref VARCHAR,
    source_ref VARCHAR,
    origin VARCHAR,
    owner VARCHAR,
    owner_group VARCHAR,
    title VARCHAR,
    state VARCHAR,
    sensitivity VARCHAR,
    execution VARCHAR,
    starts_on DATE,
    ends_on DATE,
    notice_days INTEGER,
    amends_contract_id VARCHAR,
    amendment_kind VARCHAR,
    draft_fingerprint VARCHAR,
    request_hash VARCHAR,
    missing_json VARCHAR,
    deviations_json VARCHAR,
    reference VARCHAR,
    current_version INTEGER,
    sent_at TIMESTAMP,
    link_expires_at TIMESTAMP,
    signed_at TIMESTAMP,
    principal_statement_issued_at TIMESTAMP,
    last_note VARCHAR,
    retention_until DATE,
    next_due_at TIMESTAMP,
    created_at TIMESTAMP,
    created_by VARCHAR,
    updated_at TIMESTAMP,
    version INTEGER,
    PRIMARY KEY (contract_id)
);

CREATE TABLE IF NOT EXISTS ct_contract_section (
    contract_id VARCHAR NOT NULL,
    position INTEGER NOT NULL,
    slot_no INTEGER,
    family VARCHAR,
    clause_id VARCHAR,
    clause_version INTEGER,
    position_kind VARCHAR,
    deviation_level INTEGER,
    overridden BOOLEAN,
    required BOOLEAN,
    title VARCHAR,
    body VARCHAR,
    PRIMARY KEY (contract_id, position)
);

CREATE TABLE IF NOT EXISTS ct_contract_variable (
    contract_id VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    type VARCHAR,
    value VARCHAR,
    currency VARCHAR,
    source VARCHAR,
    resolved_at VARCHAR,
    frozen_in_version INTEGER,
    PRIMARY KEY (contract_id, name)
);

CREATE TABLE IF NOT EXISTS ct_contract_version (
    contract_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    object_key VARCHAR,
    sha256 VARCHAR,
    size_bytes BIGINT,
    frozen_at TIMESTAMP,
    frozen_by VARCHAR,
    draft_fingerprint VARCHAR,
    status VARCHAR,
    text_body VARCHAR,
    PRIMARY KEY (contract_id, version)
);

CREATE TABLE IF NOT EXISTS ct_contract_versions (
    contract_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (contract_id, version)
);

CREATE TABLE IF NOT EXISTS ct_delivery (
    contract_id VARCHAR NOT NULL,
    signer_no INTEGER NOT NULL,
    kind VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    channel VARCHAR,
    capability VARCHAR,
    idempotency_key VARCHAR,
    recipient VARCHAR,
    subject VARCHAR,
    message VARCHAR,
    plan VARCHAR,
    reason VARCHAR,
    status VARCHAR,
    provider_ref VARCHAR,
    done_by VARCHAR,
    done_at TIMESTAMP,
    created_at TIMESTAMP,
    retry_at TIMESTAMP,
    PRIMARY KEY (contract_id, signer_no, kind, seq)
);

CREATE TABLE IF NOT EXISTS ct_executed_copy (
    contract_id VARCHAR NOT NULL,
    object_key VARCHAR,
    sha256 VARCHAR,
    size_bytes BIGINT,
    provenance VARCHAR,
    signed_version INTEGER,
    signed_where VARCHAR,
    filed_by VARCHAR,
    filed_at TIMESTAMP,
    filename VARCHAR,
    content_type VARCHAR,
    PRIMARY KEY (contract_id)
);

CREATE TABLE IF NOT EXISTS ct_key_date (
    contract_id VARCHAR NOT NULL,
    kind VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    due_on DATE,
    owner_group VARCHAR,
    days_before_json VARCHAR,
    reminded_json VARCHAR,
    next_remind_on DATE,
    done_at TIMESTAMP,
    done_by VARCHAR,
    note VARCHAR,
    PRIMARY KEY (contract_id, kind, seq)
);

CREATE TABLE IF NOT EXISTS ct_object_chunk (
    object_key VARCHAR NOT NULL,
    part_no INTEGER NOT NULL,
    content_b64 VARCHAR,
    sha256 VARCHAR,
    size_bytes BIGINT,
    content_type VARCHAR,
    stored_at TIMESTAMP,
    PRIMARY KEY (object_key, part_no)
);

CREATE TABLE IF NOT EXISTS ct_reminder_cadence (
    cadence_code VARCHAR NOT NULL,
    kind VARCHAR,
    every_days INTEGER,
    max_count INTEGER,
    first_after_days INTEGER,
    unit VARCHAR,
    days_before VARCHAR,
    updated_at TIMESTAMP,
    PRIMARY KEY (cadence_code)
);

CREATE TABLE IF NOT EXISTS ct_retention_log (
    run_date DATE NOT NULL,
    contract_id VARCHAR NOT NULL,
    category VARCHAR NOT NULL,
    purged_at TIMESTAMP,
    detail VARCHAR,
    PRIMARY KEY (run_date, contract_id, category)
);

CREATE TABLE IF NOT EXISTS ct_retention_rule (
    rule_code VARCHAR NOT NULL,
    years_after_end INTEGER,
    dead_draft_days INTEGER,
    basis VARCHAR,
    updated_at TIMESTAMP,
    PRIMARY KEY (rule_code)
);

CREATE TABLE IF NOT EXISTS ct_settings (
    setting_key VARCHAR NOT NULL,
    value VARCHAR,
    updated_at TIMESTAMP,
    updated_by VARCHAR,
    PRIMARY KEY (setting_key)
);

CREATE TABLE IF NOT EXISTS ct_signature_event (
    contract_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    kind VARCHAR,
    signer_no INTEGER,
    signer_kind VARCHAR,
    version INTEGER,
    version_sha256_shown VARCHAR,
    email VARCHAR,
    token_ref VARCHAR,
    pool_sub VARCHAR,
    session_id VARCHAR,
    ip VARCHAR,
    user_agent VARCHAR,
    typed_name VARCHAR,
    consent_text_version VARCHAR,
    consent_text VARCHAR,
    auth_method VARCHAR,
    occurred_at TIMESTAMP,
    detail VARCHAR,
    prev_hash VARCHAR,
    row_hash VARCHAR,
    PRIMARY KEY (contract_id, seq)
);

CREATE TABLE IF NOT EXISTS ct_signer (
    contract_id VARCHAR NOT NULL,
    signer_no INTEGER NOT NULL,
    kind VARCHAR,
    side VARCHAR,
    role VARCHAR,
    signing_order INTEGER,
    email VARCHAR,
    full_name VARCHAR,
    title VARCHAR,
    authority_basis VARCHAR,
    bp_id BIGINT,
    pool_sub VARCHAR,
    status VARCHAR,
    link_id VARCHAR,
    copy_link_id VARCHAR,
    signed_at TIMESTAMP,
    signed_version INTEGER,
    first_opened_at TIMESTAMP,
    last_opened_at TIMESTAMP,
    delivered_at TIMESTAMP,
    chase_count INTEGER,
    last_chased_at TIMESTAMP,
    PRIMARY KEY (contract_id, signer_no)
);

CREATE TABLE IF NOT EXISTS ct_template (
    template_id VARCHAR NOT NULL,
    type_code VARCHAR,
    name VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (template_id)
);

CREATE TABLE IF NOT EXISTS ct_template_slot (
    template_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    slot_no INTEGER NOT NULL,
    family VARCHAR,
    kind VARCHAR,
    alternative_of_slot INTEGER,
    clause_id VARCHAR,
    clause_version INTEGER,
    condition_json VARCHAR,
    PRIMARY KEY (template_id, version, slot_no)
);

CREATE TABLE IF NOT EXISTS ct_template_version (
    template_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    status VARCHAR,
    authored_by VARCHAR,
    authored_at TIMESTAMP,
    approved_by VARCHAR,
    approved_at TIMESTAMP,
    retired_at TIMESTAMP,
    note VARCHAR,
    PRIMARY KEY (template_id, version)
);

CREATE TABLE IF NOT EXISTS ct_tick_claim (
    job VARCHAR NOT NULL,
    slot VARCHAR NOT NULL,
    claimed_at TIMESTAMP,
    PRIMARY KEY (job, slot)
);

CREATE TABLE IF NOT EXISTS ct_type (
    type_code VARCHAR NOT NULL,
    label VARCHAR,
    allowed_bp_roles VARCHAR,
    execution VARCHAR,
    esign_allowed BOOLEAN,
    sensitivity VARCHAR,
    retention_rule VARCHAR,
    approval_policy VARCHAR,
    pack VARCHAR,
    pack_checks VARCHAR,
    allow_roleless_person BOOLEAN,
    status VARCHAR,
    updated_at TIMESTAMP,
    updated_by VARCHAR,
    PRIMARY KEY (type_code)
);

CREATE TABLE IF NOT EXISTS ct_variable_def (
    template_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    name VARCHAR NOT NULL,
    type VARCHAR,
    source VARCHAR,
    required BOOLEAN,
    label VARCHAR,
    validation_json VARCHAR,
    enum_values_json VARCHAR,
    aliases_json VARCHAR,
    condition_json VARCHAR,
    default_value VARCHAR,
    currency_var VARCHAR,
    PRIMARY KEY (template_id, version, name)
);

-- The capability claims and action log of a PROVIDER (contracts.draft / send / status / fetch_executed),
-- named by the platform after the brick (contracts_), not ct_.
-- Capability claims (erp_platform.capabilities, rule 1). A claim is a PLAIN INSERT: a duplicate key fails
-- with 23505 and the caller re-reads the winner. generation 1 is the first claim, and a takeover after the
-- lease expired inserts generation n+1, so a takeover race has one winner too. Only terminal results
-- (done, or failed with retryable=false) are kept in `result`, and a non-terminal outcome deletes its claim.
CREATE TABLE IF NOT EXISTS contracts_capability_claims (
    capability       VARCHAR NOT NULL,
    idempotency_key  VARCHAR NOT NULL,
    generation       INTEGER NOT NULL,
    input_hash       VARCHAR NOT NULL,      -- SHA-256 of the canonical validated input: same key, other input = 409
    attempt_id       VARCHAR NOT NULL,
    claimed_at       TIMESTAMP NOT NULL,
    lease_until      TIMESTAMP NOT NULL,    -- UTC; an unfinished claim past this is re-attempted
    status           VARCHAR,               -- NULL while in progress; done | failed once terminal
    result           VARCHAR,               -- the terminal RESULT, as JSON
    finished_at      TIMESTAMP,
    PRIMARY KEY (capability, idempotency_key, generation)
);

-- One row per call (rule 5) — dry runs, replays and in_progress answers included. Secret inputs redacted.
CREATE TABLE IF NOT EXISTS contracts_capability_log (
    capability       VARCHAR NOT NULL,
    idempotency_key  VARCHAR NOT NULL,
    attempt_id       VARCHAR NOT NULL,
    event_at         TIMESTAMP NOT NULL,
    actor            VARCHAR,
    dry_run          BOOLEAN NOT NULL,
    input            VARCHAR NOT NULL,      -- JSON, secret fields replaced by "[redacted]"
    status           VARCHAR NOT NULL,      -- a RESULT status, or "error" when the handler crashed
    retryable        BOOLEAN,
    provider_ref     VARCHAR,
    message          VARCHAR,
    replay           BOOLEAN NOT NULL,
    tries            INTEGER NOT NULL,      -- vendor attempts inside this call (rule 7)
    error            VARCHAR,               -- an exception's TYPE, never its message
    request_id       VARCHAR,
    PRIMARY KEY (capability, idempotency_key, attempt_id)
);