dodil/erp-brick-ar

DODIL ERP brick: accounts receivable

src/brick_ar/schema.sql

-- Tables of the ar brick (module `ar`, invoicing). Every table prefixed `ar_`, a natural primary key each (never SERIAL),
-- money NUMERIC(18,2), a rate NUMERIC(19,4), creation columns last so a later column is an append. Additive only.
-- Dated rows (ar_settings, ar_customer, ar_account_map) are only ever inserted. *_versions tables are the optimistic-
-- version claims (erp_platform.repository): a save claims version n+1 with a plain INSERT, a racer fails 23505 -> 409.

CREATE TABLE IF NOT EXISTS ar_account_map (
    purpose    VARCHAR NOT NULL,
    tax_code   VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    account_id BIGINT,
    set_by     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purpose, tax_code, valid_from)
);

CREATE TABLE IF NOT EXISTS ar_ai_finding (
    invoice_id    VARCHAR NOT NULL,
    step          VARCHAR NOT NULL,
    run_no        INTEGER NOT NULL,
    model         VARCHAR,
    inputs_digest VARCHAR,
    finding       VARCHAR,
    evidence      VARCHAR,
    decision      VARCHAR,
    reason        VARCHAR,
    decided_by    VARCHAR,
    decided_at    TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (invoice_id, step, run_no)
);

CREATE TABLE IF NOT EXISTS ar_credit_claim (
    invoice_id VARCHAR NOT NULL,
    n          INTEGER NOT NULL,
    credit_id  VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (invoice_id, n)
);

CREATE TABLE IF NOT EXISTS ar_credit_note_lines (
    credit_id      VARCHAR NOT NULL,
    line_no        INTEGER NOT NULL,
    product_id     VARCHAR,
    description    VARCHAR,
    unit_code      VARCHAR,
    quantity       NUMERIC(19,4),
    unit_price     NUMERIC(19,4),
    discount_pct   NUMERIC(9,4),
    tax_category   VARCHAR,
    tax_code       VARCHAR,
    net_amount     NUMERIC(18,2),
    tax_amount     NUMERIC(18,2),
    sales_order_id VARCHAR,
    so_line_no     INTEGER,
    odoo_line_id   VARCHAR,
    pinned_from    VARCHAR,
    cost_centre    VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (credit_id, line_no)
);

CREATE TABLE IF NOT EXISTS ar_credit_note_tax (
    credit_id       VARCHAR NOT NULL,
    tax_code        VARCHAR NOT NULL,
    rate_pct        NUMERIC(9,4),
    net_amount      NUMERIC(18,2),
    tax_amount      NUMERIC(18,2),
    rounding_method VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (credit_id, tax_code)
);

CREATE TABLE IF NOT EXISTS ar_credit_notes (
    credit_id        VARCHAR NOT NULL,
    bp_id            BIGINT,
    invoice_id       VARCHAR,
    credit_no        VARCHAR,
    series_id        VARCHAR,
    credit_date      DATE,
    currency         VARCHAR,
    reason           VARCHAR,
    status           VARCHAR,
    subtotal         NUMERIC(18,2),
    tax_total        NUMERIC(18,2),
    total            NUMERIC(18,2),
    source_kind      VARCHAR,
    drafted_by       VARCHAR,
    drafted_by_sub   VARCHAR,
    approved_by      VARCHAR,
    approved_at      TIMESTAMP,
    export_batch_id  VARCHAR,
    migration_run_id VARCHAR,
    payment_id       VARCHAR,
    company_code     VARCHAR,
    version          INTEGER,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (credit_id)
);

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

CREATE TABLE IF NOT EXISTS ar_customer (
    bp_id                     BIGINT NOT NULL,
    valid_from                DATE NOT NULL,
    bill_to_name              VARCHAR,
    bill_to_address           VARCHAR,
    invoice_email             VARCHAR,
    terms_id                  VARCHAR,
    currency                  VARCHAR,
    intercompany_company_code VARCHAR,
    einvoice_channel          VARCHAR,
    peppol_participant_id     VARCHAR,
    default_cost_centre       VARCHAR,
    set_by                    VARCHAR,
    set_at                    TIMESTAMP,
    created_at                TIMESTAMP,
    updated_at                TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS ar_deferral_release (
    invoice_id   VARCHAR NOT NULL,
    line_no      INTEGER NOT NULL,
    period_id    VARCHAR NOT NULL,
    kind         VARCHAR,
    amount       NUMERIC(18,2),
    journal_date DATE,
    released_at  TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (invoice_id, line_no, period_id)
);

CREATE TABLE IF NOT EXISTS ar_deposit_claim (
    deposit_invoice_id VARCHAR NOT NULL,
    n                  INTEGER NOT NULL,
    final_invoice_id   VARCHAR,
    net                NUMERIC(18,2),
    claimed_at         TIMESTAMP,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (deposit_invoice_id, n)
);

CREATE TABLE IF NOT EXISTS ar_einvoice_message (
    message_id    VARCHAR NOT NULL,
    document_id   VARCHAR,
    document_kind VARCHAR,
    provider      VARCHAR,
    sent_at       TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (message_id)
);

CREATE TABLE IF NOT EXISTS ar_einvoice_status (
    message_id VARCHAR NOT NULL,
    status     VARCHAR NOT NULL,
    status_at  TIMESTAMP,
    reason     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (message_id, status)
);

CREATE TABLE IF NOT EXISTS ar_export_batch (
    batch_id       VARCHAR NOT NULL,
    period         VARCHAR,
    format         VARCHAR,
    document_count INTEGER,
    file_key       VARCHAR,
    content        VARCHAR,
    created_by     VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (batch_id)
);

CREATE TABLE IF NOT EXISTS ar_ic_handover (
    document_id     VARCHAR NOT NULL,
    bill_id         VARCHAR,
    partner_company VARCHAR,
    handed_at       TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (document_id)
);

CREATE TABLE IF NOT EXISTS ar_interest_rate (
    valid_from         DATE NOT NULL,
    reference_rate_pct NUMERIC(9,4),
    margin_pct         NUMERIC(9,4),
    source             VARCHAR,
    set_by             VARCHAR,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (valid_from)
);

CREATE TABLE IF NOT EXISTS ar_invoice_lines (
    invoice_id             VARCHAR NOT NULL,
    line_no                INTEGER NOT NULL,
    product_id             VARCHAR,
    description            VARCHAR,
    unit_code              VARCHAR,
    quantity               NUMERIC(19,4),
    unit_price             NUMERIC(19,4),
    discount_pct           NUMERIC(9,4),
    tax_category           VARCHAR,
    tax_code               VARCHAR,
    net_amount             NUMERIC(18,2),
    tax_amount             NUMERIC(18,2),
    sales_order_id         VARCHAR,
    so_line_no             INTEGER,
    service_from           DATE,
    service_to             DATE,
    schedule_id            VARCHAR,
    item_id                VARCHAR,
    deducts_invoice_id     VARCHAR,
    odoo_line_id           VARCHAR,
    usage                  BOOLEAN,
    pinned_from            VARCHAR,
    interest_on_invoice_id VARCHAR,
    interest_days          INTEGER,
    interest_rate_pct      NUMERIC(9,4),
    cost_centre            VARCHAR,
    pricing_quantity       NUMERIC(19,4),
    pricing_unit_code      VARCHAR,
    quantity_priced        NUMERIC(19,4),
    net_given              BOOLEAN,
    created_at             TIMESTAMP,
    updated_at             TIMESTAMP,
    PRIMARY KEY (invoice_id, line_no)
);

CREATE TABLE IF NOT EXISTS ar_invoice_no_claims (
    invoice_no  VARCHAR NOT NULL,
    document_id VARCHAR,
    claimed_at  TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (invoice_no)
);

CREATE TABLE IF NOT EXISTS ar_invoice_tax (
    invoice_id      VARCHAR NOT NULL,
    tax_code        VARCHAR NOT NULL,
    rate_pct        NUMERIC(9,4),
    net_amount      NUMERIC(18,2),
    tax_amount      NUMERIC(18,2),
    rounding_method VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (invoice_id, tax_code)
);

CREATE TABLE IF NOT EXISTS ar_invoices (
    invoice_id                VARCHAR NOT NULL,
    bp_id                     BIGINT,
    lifecycle                 VARCHAR,
    kind                      VARCHAR,
    source_kind               VARCHAR,
    source_id                 VARCHAR,
    invoice_no                VARCHAR,
    series_id                 VARCHAR,
    currency                  VARCHAR,
    fx_rate                   NUMERIC(19,4),
    issue_date                DATE,
    tax_point_date            DATE,
    due_date                  DATE,
    terms_id                  VARCHAR,
    customer_po_ref           VARCHAR,
    bill_to_json              VARCHAR,
    subtotal                  NUMERIC(18,2),
    tax_total                 NUMERIC(18,2),
    total                     NUMERIC(18,2),
    total_functional          NUMERIC(18,2),
    export_batch_id           VARCHAR,
    migration_run_id          VARCHAR,
    company_code              VARCHAR,
    trading_partner           VARCHAR,
    settlement_discount_pct   NUMERIC(9,4),
    settlement_discount_until DATE,
    einvoice_message_id       VARCHAR,
    sold_to_bp_id             BIGINT,
    ship_to_bp_id             BIGINT,
    bill_to_bp_id             BIGINT,
    created_by                VARCHAR,
    reviewed_by               VARCHAR,
    reviewed_at               TIMESTAMP,
    void_reason               VARCHAR,
    voided_on                 DATE,
    version                   INTEGER,
    created_at                TIMESTAMP,
    updated_at                TIMESTAMP,
    PRIMARY KEY (invoice_id)
);

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

CREATE TABLE IF NOT EXISTS ar_migration_check (
    run_id       VARCHAR NOT NULL,
    check_key    VARCHAR NOT NULL,
    slice        VARCHAR NOT NULL,
    odoo_value   VARCHAR,
    ours_value   VARCHAR,
    difference   VARCHAR,
    equal        BOOLEAN,
    must         VARCHAR,
    explanation  VARCHAR,
    explained_by VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (run_id, check_key, slice)
);

CREATE TABLE IF NOT EXISTS ar_migration_map (
    odoo_db     VARCHAR NOT NULL,
    odoo_model  VARCHAR NOT NULL,
    odoo_id     VARCHAR NOT NULL,
    natural_key VARCHAR,
    run_id      VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);

CREATE TABLE IF NOT EXISTS ar_migration_run (
    run_id         VARCHAR NOT NULL,
    source         VARCHAR,
    source_db      VARCHAR,
    cutover_date   DATE,
    doors_json     VARCHAR,
    status         VARCHAR,
    loaded_at      TIMESTAMP,
    checksums_json VARCHAR,
    signed_by      VARCHAR,
    signed_at      TIMESTAMP,
    counts_json    VARCHAR,
    version        INTEGER,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (run_id)
);

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

CREATE TABLE IF NOT EXISTS ar_number_claims (
    series_id   VARCHAR NOT NULL,
    seq         INTEGER NOT NULL,
    document_id VARCHAR,
    claimed_at  TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (series_id, seq)
);

CREATE TABLE IF NOT EXISTS ar_number_series (
    series_id       VARCHAR NOT NULL,
    high_water_mark INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (series_id)
);

CREATE TABLE IF NOT EXISTS ar_payment_request (
    request_id          VARCHAR NOT NULL,
    bp_id               BIGINT,
    company_code        VARCHAR,
    lifecycle           VARCHAR,
    kind                VARCHAR,
    sales_order_id      VARCHAR,
    currency            VARCHAR,
    tax_category        VARCHAR,
    net_amount          NUMERIC(18,2),
    tax_amount_display  NUMERIC(18,2),
    total               NUMERIC(18,2),
    request_no          VARCHAR,
    issue_date          DATE,
    due_date            DATE,
    deposit_invoice_ids VARCHAR,
    cancel_reason       VARCHAR,
    created_by          VARCHAR,
    version             INTEGER,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (request_id)
);

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

CREATE TABLE IF NOT EXISTS ar_postings (
    document_id  VARCHAR NOT NULL,
    event        VARCHAR NOT NULL,
    journal_id   BIGINT,
    journal_date DATE,
    posted_by    VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (document_id, event)
);

CREATE TABLE IF NOT EXISTS ar_provision_rate (
    bucket     VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rate_pct   NUMERIC(9,4),
    set_by     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bucket, valid_from)
);

CREATE TABLE IF NOT EXISTS ar_provision_run (
    run_id          VARCHAR NOT NULL,
    company_code    VARCHAR,
    period_id       VARCHAR,
    lifecycle       VARCHAR,
    as_at           DATE,
    lines_json      VARCHAR,
    total           NUMERIC(18,2),
    previous_total  NUMERIC(18,2),
    movement        NUMERIC(18,2),
    proposed_by     VARCHAR,
    proposed_by_sub VARCHAR,
    approved_by     VARCHAR,
    approved_at     TIMESTAMP,
    rejected_reason VARCHAR,
    journal_id      BIGINT,
    version         INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (run_id)
);

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

CREATE TABLE IF NOT EXISTS ar_request_settlement (
    payment_id         VARCHAR NOT NULL,
    request_id         VARCHAR,
    n                  INTEGER,
    received_on        DATE,
    amount             NUMERIC(18,2),
    deposit_invoice_id VARCHAR,
    settled_at         TIMESTAMP,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS ar_schedule (
    schedule_id   VARCHAR NOT NULL,
    bp_id         BIGINT,
    source_kind   VARCHAR,
    source_id     VARCHAR,
    line_id       VARCHAR,
    product_id    VARCHAR,
    description   VARCHAR,
    billing_kind  VARCHAR,
    billing_plan  VARCHAR,
    starts_on     DATE,
    periods       INTEGER,
    quantity      NUMERIC(19,4),
    unit_price    NUMERIC(19,4),
    discount_pct  NUMERIC(9,4),
    unit_code     VARCHAR,
    tax_category  VARCHAR,
    tax_code      VARCHAR,
    currency      VARCHAR,
    in_advance    BOOLEAN,
    billing_day   VARCHAR,
    prorate_first BOOLEAN,
    status        VARCHAR,
    stopped_at    DATE,
    stop_reason   VARCHAR,
    created_by    VARCHAR,
    version       INTEGER,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (schedule_id)
);

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

CREATE TABLE IF NOT EXISTS ar_sends (
    invoice_id    VARCHAR NOT NULL,
    sent_at       TIMESTAMP NOT NULL,
    channel       VARCHAR,
    address       VARCHAR,
    format        VARCHAR,
    result        VARCHAR,
    message_id    VARCHAR,
    statuses_json VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (invoice_id, sent_at)
);

CREATE TABLE IF NOT EXISTS ar_settings (
    setting_key VARCHAR NOT NULL,
    valid_from  DATE NOT NULL,
    value       VARCHAR,
    set_by      VARCHAR,
    set_at      TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (setting_key, valid_from)
);

CREATE TABLE IF NOT EXISTS ar_settlement_discount (
    payment_id VARCHAR NOT NULL,
    invoice_id VARCHAR,
    credit_id  VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS ar_terms (
    terms_id            VARCHAR NOT NULL,
    days                INTEGER,
    basis               VARCHAR,
    description         VARCHAR,
    early_discount_pct  NUMERIC(9,4),
    active              BOOLEAN,
    early_discount_days INTEGER,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (terms_id)
);

CREATE TABLE IF NOT EXISTS ar_writeoffs (
    writeoff_id     VARCHAR NOT NULL,
    invoice_id      VARCHAR,
    amount          NUMERIC(18,2),
    reason          VARCHAR,
    status          VARCHAR,
    proposed_by     VARCHAR,
    proposed_by_sub VARCHAR,
    writeoff_date   DATE,
    approved_by     VARCHAR,
    approved_at     TIMESTAMP,
    rejected_reason VARCHAR,
    version         INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (writeoff_id)
);

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