dodil/erp-brick-tax
DODIL ERP brick: tax
src/brick_tax/migrations/00003_v4/up.sql
-- 00003_v4 (v0.2.0): tax v3 + v4 — purchase codes (direction, reclaimable_pct, input/value boxes), purchase rules, the HMRC
-- connection, obligations, the VAT return and its entries, adjustments, error notices, liabilities, bad debt relief. Additive only;
-- ADD COLUMN is DISRUPTIVE (erp-migrate): migrate the test copy first, the live one at a quiet hour, before rolling the 0.2.0 image.
ALTER TABLE tax_code ADD COLUMN direction VARCHAR;
ALTER TABLE tax_code ADD COLUMN input_reporting_box VARCHAR;
ALTER TABLE tax_code ADD COLUMN value_box VARCHAR;
ALTER TABLE tax_code ADD COLUMN reclaimable_pct NUMERIC(19,4);
ALTER TABLE tax_document ADD COLUMN direction VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN reclaimable_pct NUMERIC(19,4);
ALTER TABLE tax_document_line ADD COLUMN input_reporting_box VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN value_box VARCHAR;
ALTER TABLE tax_registration ADD COLUMN accounting_basis VARCHAR;
ALTER TABLE tax_registration ADD COLUMN flat_rate_sector VARCHAR;
ALTER TABLE tax_registration ADD COLUMN flat_rate_pct NUMERIC(19,4);
ALTER TABLE tax_registration ADD COLUMN first_year_until DATE;
ALTER TABLE tax_registration ADD COLUMN ni_goods BOOLEAN;
ALTER TABLE tax_registration ADD COLUMN partial_exemption_year_end VARCHAR;
ALTER TABLE tax_registration ADD COLUMN expected_turnover NUMERIC(18,2);
ALTER TABLE tax_code ADD COLUMN attribution VARCHAR;
ALTER TABLE tax_code ADD COLUMN eu_goods BOOLEAN;
ALTER TABLE tax_party ADD COLUMN check_name VARCHAR;
ALTER TABLE tax_party ADD COLUMN check_address VARCHAR;
ALTER TABLE tax_party ADD COLUMN consultation_number VARCHAR;
ALTER TABLE tax_party ADD COLUMN check_flag VARCHAR;
ALTER TABLE tax_party ADD COLUMN confirmed_by VARCHAR;
ALTER TABLE tax_party ADD COLUMN confirm_reason VARCHAR;
ALTER TABLE tax_party ADD COLUMN notice_kind VARCHAR;
ALTER TABLE tax_party ADD COLUMN notice_ref VARCHAR;
ALTER TABLE tax_party ADD COLUMN notice_date DATE;
ALTER TABLE tax_document ADD COLUMN ship_to_country VARCHAR;
ALTER TABLE tax_document ADD COLUMN self_billed BOOLEAN;
ALTER TABLE tax_document ADD COLUMN due_date DATE;
ALTER TABLE tax_document_line ADD COLUMN pinned_from VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN attribution VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN eu_goods BOOLEAN;
CREATE TABLE IF NOT EXISTS tax_bad_debt_claim (
invoice_id VARCHAR NOT NULL,
lifecycle VARCHAR,
bp_id BIGINT,
writeoff_id VARCHAR,
supply_date DATE,
due_date DATE,
eligible_from DATE,
written_off_on DATE,
gross NUMERIC(18,2),
vat NUMERIC(18,2),
unpaid_at_claim NUMERIC(18,2),
vat_fraction NUMERIC(19,8),
relief_amount NUMERIC(18,2),
claimed_in_period_key VARCHAR,
claimed_by VARCHAR,
claimed_on DATE,
dismissed_reason VARCHAR,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (invoice_id)
);
-- Optimistic versions (erp_platform.repository): a save CLAIMS version n+1 with a plain INSERT here,
-- in the same transaction as its rows; a racer from the same version fails 23505 -> 409.
CREATE TABLE IF NOT EXISTS tax_bad_debt_claim_versions (
invoice_id VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (invoice_id, version)
);
CREATE TABLE IF NOT EXISTS tax_hmrc_connection (
vrn VARCHAR NOT NULL,
scopes VARCHAR,
environment VARCHAR,
authorised_by VARCHAR,
authorised_at TIMESTAMP,
expires_at TIMESTAMP,
secret_ref VARCHAR,
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
sync_error VARCHAR,
sync_error_at TIMESTAMP,
PRIMARY KEY (vrn)
);
CREATE TABLE IF NOT EXISTS tax_rule_purchase (
category_code VARCHAR NOT NULL,
treatment_code VARCHAR NOT NULL,
supply VARCHAR NOT NULL,
valid_from DATE NOT NULL,
tax_code VARCHAR,
priority INTEGER,
valid_to DATE,
change_state VARCHAR,
created_by VARCHAR,
approved_by VARCHAR,
withdrawn_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
created_sub VARCHAR,
PRIMARY KEY (category_code, treatment_code, supply, valid_from)
);
CREATE TABLE IF NOT EXISTS tax_statutory (
stat_key VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
unit VARCHAR,
source VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (stat_key, valid_from)
);
CREATE TABLE IF NOT EXISTS tax_vat_adjustment (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
adjustment_no INTEGER NOT NULL,
corrects_period_key VARCHAR,
corrects_period_end DATE,
box INTEGER,
amount NUMERIC(18,2),
reason VARCHAR,
recorded_by VARCHAR,
recorded_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key, adjustment_no)
);
CREATE TABLE IF NOT EXISTS tax_vat_error_notice (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
notice_no INTEGER NOT NULL,
reference VARCHAR,
sent_on DATE,
periods VARCHAR,
amount NUMERIC(18,2),
reason VARCHAR,
recorded_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key, notice_no)
);
CREATE TABLE IF NOT EXISTS tax_vat_liability (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
charge_type VARCHAR NOT NULL,
original_amount NUMERIC(18,2),
outstanding_amount NUMERIC(18,2),
due_date DATE,
paid_amount NUMERIC(18,2),
paid_on DATE,
synced_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key, charge_type)
);
CREATE TABLE IF NOT EXISTS tax_vat_obligation (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
period_start DATE,
period_end DATE,
due_date DATE,
status VARCHAR,
received_on DATE,
synced_at TIMESTAMP,
hmrc_error VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key)
);
CREATE TABLE IF NOT EXISTS tax_vat_return (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
lifecycle VARCHAR,
accounting_basis VARCHAR,
flat_rate_pct NUMERIC(19,4),
box1_vat_due_sales NUMERIC(18,2),
box2_vat_due_acquisitions NUMERIC(18,2),
box3_total_vat_due NUMERIC(18,2),
box4_vat_reclaimed NUMERIC(18,2),
box5_net_vat_due NUMERIC(18,2),
box6_total_sales_ex_vat NUMERIC(18,2),
box7_total_purchases_ex_vat NUMERIC(18,2),
box8_goods_supplied_ex_vat NUMERIC(18,2),
box9_acquisitions_ex_vat NUMERIC(18,2),
prepared_by VARCHAR,
locked_by VARCHAR,
locked_by_sub VARCHAR,
locked_at TIMESTAMP,
submitted_by VARCHAR,
submitted_at TIMESTAMP,
declaration_text VARCHAR,
processing_date VARCHAR,
form_bundle_number VARCHAR,
payment_indicator VARCHAR,
charge_ref_number VARCHAR,
fraud_headers_json VARCHAR,
settle_journal_id VARCHAR,
hmrc_error VARCHAR,
version INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key)
);
-- Optimistic versions (erp_platform.repository): a save CLAIMS version n+1 with a plain INSERT here,
-- in the same transaction as its rows; a racer from the same version fails 23505 -> 409.
CREATE TABLE IF NOT EXISTS tax_vat_return_versions (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
version INTEGER NOT NULL,
saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
PRIMARY KEY (vrn, period_key, version)
);
CREATE TABLE IF NOT EXISTS tax_vat_return_entry (
document_ref VARCHAR NOT NULL,
tax_code VARCHAR NOT NULL,
entry_kind VARCHAR NOT NULL,
vrn VARCHAR,
period_key VARCHAR,
direction VARCHAR,
tax_point DATE,
net_amount NUMERIC(18,2),
vat_amount NUMERIC(18,2),
vat_reclaimable NUMERIC(18,2),
box1_amount NUMERIC(18,2),
box4_amount NUMERIC(18,2),
box_value VARCHAR,
source VARCHAR,
detail VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
gross_amount NUMERIC(18,2),
attribution VARCHAR,
box2_amount NUMERIC(18,2),
PRIMARY KEY (document_ref, tax_code, entry_kind)
);
CREATE TABLE IF NOT EXISTS tax_cis_subcontractor (
bp_id BIGINT NOT NULL,
valid_from DATE NOT NULL,
name VARCHAR,
utr VARCHAR,
company_number VARCHAR,
ni_number VARCHAR,
status VARCHAR,
rate_pct NUMERIC(19,4),
verification_number VARCHAR,
verified_source VARCHAR,
recorded_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (bp_id, valid_from)
);
CREATE TABLE IF NOT EXISTS tax_cis_deduction (
payment_ref VARCHAR NOT NULL,
bp_id BIGINT NOT NULL,
tax_month VARCHAR,
paid_on DATE,
bill_ids VARCHAR,
gross_ex_vat NUMERIC(18,2),
materials NUMERIC(18,2),
labour NUMERIC(18,2),
status VARCHAR,
rate_pct NUMERIC(19,4),
deduction NUMERIC(18,2),
state VARCHAR,
reversed_on DATE,
reversed_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (payment_ref, bp_id)
);
CREATE TABLE IF NOT EXISTS tax_cis_return (
tax_month VARCHAR NOT NULL,
lifecycle VARCHAR,
nil_return BOOLEAN,
total_deduction NUMERIC(18,2),
lines_json VARCHAR,
prepared_by VARCHAR,
submitted_by VARCHAR,
submitted_at TIMESTAMP,
receipt_json VARCHAR,
statements_sent_at TIMESTAMP,
statements_json VARCHAR,
declaration_text VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (tax_month)
);
CREATE TABLE IF NOT EXISTS tax_cis_suffered (
contractor_bp_id BIGINT NOT NULL,
statement_ref VARCHAR NOT NULL,
tax_month VARCHAR,
invoice_id VARCHAR,
payment_id VARCHAR,
gross NUMERIC(18,2),
materials NUMERIC(18,2),
deduction NUMERIC(18,2),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (contractor_bp_id, statement_ref)
);
CREATE TABLE IF NOT EXISTS tax_trade_declaration (
kind VARCHAR NOT NULL,
period VARCHAR NOT NULL,
version INTEGER NOT NULL,
lifecycle VARCHAR,
total NUMERIC(18,2),
file_text VARCHAR,
prepared_by VARCHAR,
submission_ref VARCHAR,
filed_on DATE,
filed_by VARCHAR,
gates_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (kind, period, version)
);
CREATE TABLE IF NOT EXISTS tax_trade_declaration_line (
kind VARCHAR NOT NULL,
period VARCHAR NOT NULL,
version INTEGER NOT NULL,
line_no INTEGER NOT NULL,
partner_vat_number VARCHAR,
partner_country VARCHAR,
commodity_code VARCHAR,
nature VARCHAR,
origin_country VARCHAR,
value NUMERIC(18,2),
net_mass_kg NUMERIC(19,4),
supplementary_units NUMERIC(19,4),
indicator VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (kind, period, version, line_no)
);
CREATE TABLE IF NOT EXISTS tax_import_entry (
mrn VARCHAR NOT NULL,
entry_date DATE,
bill_ref VARCHAR,
bp_id BIGINT,
customs_value NUMERIC(18,2),
import_vat NUMERIC(18,2),
method VARCHAR,
tax_code VARCHAR,
box_vat_output NUMERIC(18,2),
box_vat_input NUMERIC(18,2),
box_value NUMERIC(18,2),
certificate_listed BOOLEAN,
recorded_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (mrn)
);
CREATE TABLE IF NOT EXISTS tax_pva_statement (
month VARCHAR NOT NULL,
lifecycle VARCHAR,
file_text VARCHAR,
imported_by VARCHAR,
reconciled_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (month)
);
CREATE TABLE IF NOT EXISTS tax_pva_statement_line (
month VARCHAR NOT NULL,
mrn VARCHAR NOT NULL,
statement_vat NUMERIC(18,2),
our_vat NUMERIC(18,2),
status VARCHAR,
difference NUMERIC(18,2),
explanation VARCHAR,
use_hmrc_figure BOOLEAN,
explained_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (month, mrn)
);
CREATE TABLE IF NOT EXISTS tax_partial_exemption (
vrn VARCHAR NOT NULL,
year_end DATE NOT NULL,
provisional_pct NUMERIC(19,4),
final_pct NUMERIC(19,4),
taxable_supplies NUMERIC(18,2),
exempt_supplies NUMERIC(18,2),
exempt_input_vat NUMERIC(18,2),
residual_input_vat NUMERIC(18,2),
de_minimis_met BOOLEAN,
adjustment_amount NUMERIC(18,2),
adjusted_in_period_key VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, year_end)
);
CREATE TABLE IF NOT EXISTS tax_capital_goods (
item_ref VARCHAR NOT NULL,
kind VARCHAR,
bought_on DATE,
net_value NUMERIC(18,2),
input_vat NUMERIC(18,2),
intervals INTEGER,
first_pct NUMERIC(19,4),
adjustments_json VARCHAR,
disposed_on DATE,
recorded_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (item_ref)
);
CREATE TABLE IF NOT EXISTS tax_ai_finding (
vrn VARCHAR NOT NULL,
period_key VARCHAR NOT NULL,
finding_no INTEGER NOT NULL,
evidence VARCHAR,
box INTEGER,
model VARCHAR,
inputs_digest VARCHAR,
decision VARCHAR,
decided_by VARCHAR,
decided_at TIMESTAMP,
note VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (vrn, period_key, finding_no)
);