dodil/erp-brick-payments
DODIL ERP brick: payments
src/brick_payments/migrations/00003_statements_dd/up.sql
-- 00003_statements_dd - additive only (payments v4, slice A: PAY-02 changed, PAY-16..PAY-23). New columns on the bank account and the
-- receipt; the house banks, statements and their lines, line suggestions, reconciliation rules, remittances, direct-debit mandates,
-- runs and collections; the generic claim table (one guard key per bounded thing) and the object store for the files.
ALTER TABLE pay_bank_account ADD COLUMN house_bank_id VARCHAR;
ALTER TABLE pay_bank_account ADD COLUMN company_code VARCHAR;
ALTER TABLE pay_bank_account ADD COLUMN purposes VARCHAR;
ALTER TABLE pay_bank_account ADD COLUMN account_name VARCHAR;
ALTER TABLE pay_bank_account ADD COLUMN status VARCHAR;
ALTER TABLE pay_bank_account ADD COLUMN closed_from DATE;
ALTER TABLE pay_bank_account ADD COLUMN account_ref VARCHAR;
ALTER TABLE pay_receipt ADD COLUMN return_reason_code VARCHAR;
ALTER TABLE pay_receipt ADD COLUMN statement_line_id VARCHAR;
CREATE TABLE IF NOT EXISTS pay_claim (
claim_scope VARCHAR NOT NULL,
claim_key VARCHAR NOT NULL,
n INTEGER NOT NULL,
ref VARCHAR,
claimed_by VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (claim_scope, claim_key, n)
);
CREATE TABLE IF NOT EXISTS pay_object (
object_key VARCHAR NOT NULL,
content_type VARCHAR,
sha256 VARCHAR,
body VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (object_key)
);
CREATE TABLE IF NOT EXISTS pay_house_bank (
house_bank_id VARCHAR NOT NULL,
name VARCHAR,
bic VARCHAR,
sort_code VARCHAR,
address VARCHAR,
sun VARCHAR,
creditor_id VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (house_bank_id)
);
CREATE TABLE IF NOT EXISTS pay_bank_statement (
statement_id VARCHAR NOT NULL,
bank_account_id VARCHAR,
from_date DATE,
to_date DATE,
opening_balance NUMERIC(18,2),
closing_balance NUMERIC(18,2),
format VARCHAR,
file_key VARCHAR,
lifecycle VARCHAR,
reconciled_by VARCHAR,
reconciled_at TIMESTAMP,
imported_by VARCHAR,
imported_on DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (statement_id)
);
CREATE TABLE IF NOT EXISTS pay_statement_line (
line_id VARCHAR NOT NULL,
statement_id VARCHAR,
bank_account_id VARCHAR,
booked_on DATE,
value_on DATE,
amount NUMERIC(18,2),
currency VARCHAR,
counterparty_name VARCHAR,
counterparty_account_masked VARCHAR,
text VARCHAR,
bank_tx_code VARCHAR,
lifecycle VARCHAR,
matched_kind VARCHAR,
matched_ref VARCHAR,
account_id VARCHAR,
tax_code VARCHAR,
cost_centre VARCHAR,
journal_id VARCHAR,
decided_how VARCHAR,
decided_by VARCHAR,
decided_on DATE,
receipt_image VARCHAR,
vat_amount NUMERIC(18,2),
vat_reclaimed VARCHAR,
reversal_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (line_id)
);
CREATE TABLE IF NOT EXISTS pay_line_suggestion (
suggestion_id VARCHAR NOT NULL,
line_id VARCHAR,
account_id VARCHAR,
tax_code VARCHAR,
cost_centre VARCHAR,
receipt_needed VARCHAR,
evidence VARCHAR,
confidence VARCHAR,
model VARCHAR,
status VARCHAR,
decided_by VARCHAR,
decided_on DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (suggestion_id)
);
CREATE TABLE IF NOT EXISTS pay_reconciliation_rule (
rule_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
bank_account_id VARCHAR,
direction VARCHAR,
text_contains VARCHAR,
counterparty_contains VARCHAR,
amount_min NUMERIC(18,2),
amount_max NUMERIC(18,2),
account_id VARCHAR,
tax_code VARCHAR,
cost_centre VARCHAR,
mode VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rule_id, valid_from)
);
CREATE TABLE IF NOT EXISTS pay_remittance (
remittance_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
file_ref VARCHAR,
format VARCHAR,
payer VARCHAR,
payment_id VARCHAR,
bp_id BIGINT,
invoice_ref VARCHAR,
invoice_id VARCHAR,
amount NUMERIC(18,2),
currency VARCHAR,
deduction_reason VARCHAR,
status VARCHAR,
suggestion_id VARCHAR,
imported_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (remittance_id, line_no)
);
CREATE TABLE IF NOT EXISTS pay_dd_mandate (
sun VARCHAR NOT NULL,
mandate_ref VARCHAR NOT NULL,
bp_id BIGINT,
bank_account_id VARCHAR,
payer_sort_code VARCHAR,
payer_account_masked VARCHAR,
payer_account_name VARCHAR,
scheme VARCHAR,
lifecycle VARCHAR,
lodged_on DATE,
auddis_result VARCHAR,
cancelled_reason VARCHAR,
cancel_lodged_on DATE,
last_collected_on DATE,
recorded_on DATE,
recorded_by VARCHAR,
notice_days INTEGER,
ends_on DATE,
disputed_on DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sun, mandate_ref)
);
CREATE TABLE IF NOT EXISTS pay_dd_mandate_details (
sun VARCHAR NOT NULL,
mandate_ref VARCHAR NOT NULL,
valid_from DATE NOT NULL,
sort_code VARCHAR,
account_number VARCHAR,
source VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sun, mandate_ref, valid_from)
);
CREATE TABLE IF NOT EXISTS pay_dd_run (
run_id VARCHAR NOT NULL,
bank_account_id VARCHAR,
collection_date DATE,
input_day DATE,
lifecycle VARCHAR,
prepared_by VARCHAR,
prepared_by_sub VARCHAR,
approved_by VARCHAR,
approved_by_sub VARCHAR,
file_key VARCHAR,
submission_ref VARCHAR,
total NUMERIC(18,2),
count INTEGER,
currency VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS pay_dd_collection (
run_id VARCHAR NOT NULL,
mandate_ref VARCHAR NOT NULL,
bp_id BIGINT,
sun VARCHAR,
invoice_ids VARCHAR,
amount NUMERIC(18,2),
transaction_code VARCHAR,
notice_sent_on DATE,
status VARCHAR,
payment_id VARCHAR,
return_reason_code VARCHAR,
returned_on DATE,
represented VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id, mandate_ref)
);