dodil/erp-brick-payments

DODIL ERP brick: payments

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

-- 00001_init - the brick's first schema. The versioned layout (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick
-- journal, pay_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.

CREATE TABLE IF NOT EXISTS pay_schema_migrations (
    migration  VARCHAR NOT NULL,
    applied_at TIMESTAMP NOT NULL,
    PRIMARY KEY (migration)
);

CREATE TABLE IF NOT EXISTS pay_allocation (
    source_kind   VARCHAR NOT NULL,
    source_id     VARCHAR NOT NULL,
    invoice_id    VARCHAR NOT NULL,
    seq           INTEGER NOT NULL,
    amount        NUMERIC(18,2),
    currency      VARCHAR,
    allocated_on  DATE,
    allocated_by  VARCHAR,
    reason        VARCHAR,
    reverses_seq  INTEGER,
    suggestion_id VARCHAR,
    decided_how   VARCHAR,
    origin        VARCHAR,
    bp_id         BIGINT,
    source_amount NUMERIC(18,2),
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (source_kind, source_id, invoice_id, seq)
);

CREATE TABLE IF NOT EXISTS pay_bank_account (
    bank_account_id          VARCHAR NOT NULL,
    name                     VARCHAR,
    currency                 VARCHAR,
    iban_or_sort_code_masked VARCHAR,
    gl_account_id            VARCHAR,
    origin                   VARCHAR,
    created_at               TIMESTAMP,
    updated_at               TIMESTAMP,
    PRIMARY KEY (bank_account_id)
);

CREATE TABLE IF NOT EXISTS pay_collection (
    bp_id        BIGINT NOT NULL,
    kind         VARCHAR NOT NULL,
    level        INTEGER NOT NULL,
    ref_id       VARCHAR NOT NULL,
    invoice_id   VARCHAR,
    occurred_on  DATE,
    promise_date DATE,
    reason       VARCHAR,
    outcome      VARCHAR,
    note         VARCHAR,
    draft_text   VARCHAR,
    status       VARCHAR,
    origin       VARCHAR,
    recorded_by  VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (bp_id, kind, level, ref_id)
);

CREATE TABLE IF NOT EXISTS pay_credit (
    bp_id               BIGINT NOT NULL,
    valid_from          DATE NOT NULL,
    credit_limit_amount NUMERIC(18,2),
    currency            VARCHAR,
    reason              VARCHAR,
    set_by              VARCHAR,
    set_at              TIMESTAMP,
    origin              VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS pay_credit_override (
    bp_id      BIGINT NOT NULL,
    kind       VARCHAR NOT NULL,
    starts_at  TIMESTAMP NOT NULL,
    ends_on    DATE,
    reason     VARCHAR,
    set_by     VARCHAR,
    origin     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, kind, starts_at)
);

CREATE TABLE IF NOT EXISTS pay_match_suggestion (
    suggestion_id  VARCHAR NOT NULL,
    payment_id     VARCHAR,
    proposed_lines VARCHAR,
    evidence       VARCHAR,
    confidence     NUMERIC(5,2),
    model          VARCHAR,
    exact          INTEGER,
    status         VARCHAR,
    decided_by     VARCHAR,
    decided_at     TIMESTAMP,
    decided_lines  VARCHAR,
    reviewed_by    VARCHAR,
    reviewed_at    TIMESTAMP,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (suggestion_id)
);

CREATE TABLE IF NOT EXISTS pay_migration_run (
    run_id         VARCHAR NOT NULL,
    params         VARCHAR,
    odoo_figures   VARCHAR,
    counts         VARCHAR,
    ran_by         VARCHAR,
    ran_at         TIMESTAMP,
    signed_by      VARCHAR,
    signed_at      TIMESTAMP,
    signed_figures VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS pay_odoo_map (
    odoo_ref     VARCHAR NOT NULL,
    target_table VARCHAR,
    target_key   VARCHAR,
    migrated_at  TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (odoo_ref)
);

CREATE TABLE IF NOT EXISTS pay_receipt (
    payment_id      VARCHAR NOT NULL,
    bp_id           BIGINT,
    currency        VARCHAR,
    amount          NUMERIC(18,2),
    received_on     DATE,
    method          VARCHAR,
    bank_account_id VARCHAR,
    bank_reference  VARCHAR,
    remittance_text VARCHAR,
    status          VARCHAR,
    batch_id        VARCHAR,
    fx_rate         NUMERIC(19,4),
    returned_on     DATE,
    bank_return_ref VARCHAR,
    reversed_on     DATE,
    origin          VARCHAR,
    recorded_by     VARCHAR,
    recorded_at     TIMESTAMP,
    cleared_by      VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS pay_receipt_ref (
    bank_account_id VARCHAR NOT NULL,
    bank_reference  VARCHAR NOT NULL,
    payment_id      VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (bank_account_id, bank_reference)
);

CREATE TABLE IF NOT EXISTS pay_refund (
    refund_id    VARCHAR NOT NULL,
    bp_id        BIGINT,
    currency     VARCHAR,
    amount       NUMERIC(18,2),
    status       VARCHAR,
    requested_by VARCHAR,
    requested_at TIMESTAMP,
    approved_by  VARCHAR,
    approved_at  TIMESTAMP,
    approved_on  DATE,
    reason       VARCHAR,
    bank_ref     VARCHAR,
    paid_on      DATE,
    origin       VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (refund_id)
);

CREATE TABLE IF NOT EXISTS pay_reminder_level (
    policy_id      VARCHAR NOT NULL,
    level          INTEGER NOT NULL,
    days_after_due INTEGER,
    channel        VARCHAR,
    template       VARCHAR,
    mode           VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (policy_id, level)
);

CREATE TABLE IF NOT EXISTS pay_settings (
    setting_key VARCHAR NOT NULL,
    value       VARCHAR,
    changes     VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (setting_key)
);

CREATE TABLE IF NOT EXISTS pay_settings_log (
    setting_key VARCHAR NOT NULL,
    seq         INTEGER NOT NULL,
    old_value   VARCHAR,
    new_value   VARCHAR,
    changed_by  VARCHAR,
    changed_at  TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (setting_key, seq)
);

CREATE TABLE IF NOT EXISTS pay_statement (
    bp_id           BIGINT NOT NULL,
    as_at           DATE NOT NULL,
    currency        VARCHAR NOT NULL,
    closing_balance NUMERIC(18,2),
    sent_at         TIMESTAMP,
    sent_by         VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (bp_id, as_at, currency)
);