dodil/erp-brick-payments

DODIL ERP brick: payments

src/brick_payments/migrations/00004_slice_b/up.sql

-- 00004_slice_b - additive only (payments v4, slice B: PAY-24..PAY-29): the IBAN on a bank account; default receiving accounts, the
-- audit of unmasked reads, cash journals/days/entries/counts, deposit batches and forecast snapshots.

ALTER TABLE pay_bank_account ADD COLUMN iban VARCHAR;

CREATE TABLE IF NOT EXISTS pay_default_bank_account (
    company_code    VARCHAR NOT NULL,
    currency        VARCHAR NOT NULL,
    valid_from      DATE NOT NULL,
    bank_account_id VARCHAR,
    set_by          VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (company_code, currency, valid_from)
);

CREATE TABLE IF NOT EXISTS pay_bank_account_read (
    read_id         VARCHAR NOT NULL,
    bank_account_id VARCHAR,
    caller          VARCHAR,
    requested_at    TIMESTAMP,
    purpose         VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (read_id)
);

CREATE TABLE IF NOT EXISTS pay_cash_journal (
    cash_journal_id VARCHAR NOT NULL,
    company_code    VARCHAR,
    currency        VARCHAR,
    gl_account_id   VARCHAR,
    name            VARCHAR,
    status          VARCHAR,
    opening_balance NUMERIC(18,2),
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (cash_journal_id)
);

CREATE TABLE IF NOT EXISTS pay_cash_day (
    cash_journal_id       VARCHAR NOT NULL,
    day                   DATE NOT NULL,
    lifecycle             VARCHAR,
    opening_balance       NUMERIC(18,2),
    expected_balance      NUMERIC(18,2),
    counted_amount        NUMERIC(18,2),
    difference            NUMERIC(18,2),
    counted_by            VARCHAR,
    approved_by           VARCHAR,
    approval_reason       VARCHAR,
    closed_by             VARCHAR,
    closed_at             TIMESTAMP,
    difference_journal_id VARCHAR,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    PRIMARY KEY (cash_journal_id, day)
);

CREATE TABLE IF NOT EXISTS pay_cash_entry (
    cash_journal_id   VARCHAR NOT NULL,
    day               DATE NOT NULL,
    seq               INTEGER NOT NULL,
    kind              VARCHAR,
    amount            NUMERIC(18,2),
    currency          VARCHAR,
    bp_id             BIGINT,
    payment_id        VARCHAR,
    account_id        VARCHAR,
    tax_code          VARCHAR,
    description       VARCHAR,
    receipt_image     VARCHAR,
    vat_amount        NUMERIC(18,2),
    vat_reclaimed     VARCHAR,
    journal_id        VARCHAR,
    statement_line_id VARCHAR,
    recorded_by       VARCHAR,
    recorded_at       TIMESTAMP,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (cash_journal_id, day, seq)
);

CREATE TABLE IF NOT EXISTS pay_cash_count (
    cash_journal_id VARCHAR NOT NULL,
    day             DATE NOT NULL,
    seq             INTEGER NOT NULL,
    counted_amount  NUMERIC(18,2),
    denominations   VARCHAR,
    counted_by      VARCHAR,
    counted_by_ids  VARCHAR,
    counted_at      TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (cash_journal_id, day, seq)
);

CREATE TABLE IF NOT EXISTS pay_deposit_batch (
    batch_id          VARCHAR NOT NULL,
    bank_account_id   VARCHAR,
    currency          VARCHAR,
    slip_no           VARCHAR,
    lifecycle         VARCHAR,
    total             NUMERIC(18,2),
    cheque_count      INTEGER,
    deposited_on      DATE,
    statement_line_id VARCHAR,
    final_batch_id    VARCHAR,
    opened_by         VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (batch_id)
);

CREATE TABLE IF NOT EXISTS pay_forecast_snapshot (
    snapshot_id VARCHAR NOT NULL,
    saved_on    DATE,
    saved_by    VARCHAR,
    body        VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (snapshot_id)
);