dodil/erp-brick-ar

DODIL ERP brick: accounts receivable

src/brick_ar/migrations/00001_init/up.sql

-- 00001_init: the brick's first schema (the same tables as schema.sql).

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_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,
    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,
    approved_by      VARCHAR,
    approved_at      TIMESTAMP,
    export_batch_id  VARCHAR,
    migration_run_id 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,
    set_by          VARCHAR,
    set_at          TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

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_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,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (invoice_id, line_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,
    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_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_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,
    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_terms (
    terms_id           VARCHAR NOT NULL,
    days               INTEGER,
    basis              VARCHAR,
    description        VARCHAR,
    early_discount_pct NUMERIC(9,4),
    active             BOOLEAN,
    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,
    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)
);