dodil/erp-brick-tax

DODIL ERP brick: tax

src/brick_tax/schema.sql

-- Tables of the tax brick (module `tax`). Every table prefixed `tax_`, a natural primary key each (never SERIAL), a
-- rate NUMERIC(19,4), money NUMERIC(18,2), creation columns last so a later column is an append. Additive only.

CREATE TABLE IF NOT EXISTS tax_category (
    category_code VARCHAR NOT NULL,
    description VARCHAR,
    created_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (category_code)
);

CREATE TABLE IF NOT EXISTS tax_code (
    tax_code VARCHAR NOT NULL,
    jurisdiction VARCHAR,
    treatment VARCHAR,
    description VARCHAR,
    document_wording VARCHAR,
    reporting_box VARCHAR,
    lifecycle VARCHAR,
    used BOOLEAN,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    direction VARCHAR,
    input_reporting_box VARCHAR,
    value_box VARCHAR,
    reclaimable_pct NUMERIC(19,4),
    attribution VARCHAR,
    eu_goods BOOLEAN,
    PRIMARY KEY (tax_code)
);

-- 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_code_versions (
    tax_code VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (tax_code, version)
);

CREATE TABLE IF NOT EXISTS tax_document (
    document_ref VARCHAR NOT NULL,
    bp_id BIGINT,
    tax_point DATE,
    currency VARCHAR,
    treatment_code VARCHAR,
    status VARCHAR,
    rounding_method VARCHAR,
    prices_include_tax BOOLEAN,
    registered BOOLEAN,
    vat_number VARCHAR,
    title VARCHAR,
    fx_rate NUMERIC(19,8),
    vat_total_gbp NUMERIC(18,2),
    frozen_by VARCHAR,
    frozen_at TIMESTAMP,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    direction VARCHAR,
    ship_to_country VARCHAR,
    self_billed BOOLEAN,
    due_date DATE,
    PRIMARY KEY (document_ref)
);

CREATE TABLE IF NOT EXISTS tax_document_line (
    document_ref VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    description VARCHAR,
    category_code VARCHAR,
    supply VARCHAR,
    amount NUMERIC(18,2),
    quantity NUMERIC(19,4),
    unit_price NUMERIC(19,4),
    tax_code VARCHAR,
    treatment VARCHAR,
    rate_pct NUMERIC(19,4),
    charged_pct NUMERIC(19,4),
    wording VARCHAR,
    reporting_box VARCHAR,
    source VARCHAR,
    rule_ref VARCHAR,
    rule_code VARCHAR,
    refusal VARCHAR,
    override_reason VARCHAR,
    override_by VARCHAR,
    override_state VARCHAR,
    override_approved_by VARCHAR,
    override_approval_reason VARCHAR,
    lowers_tax BOOLEAN,
    suggestion_state VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    override_by_sub VARCHAR,
    calc_net NUMERIC(18,2),
    calc_vat NUMERIC(18,2),
    calc_vat_due NUMERIC(18,2),
    calc_gross NUMERIC(18,2),
    reclaimable_pct NUMERIC(19,4),
    input_reporting_box VARCHAR,
    value_box VARCHAR,
    pinned_from VARCHAR,
    attribution VARCHAR,
    eu_goods BOOLEAN,
    consumption VARCHAR,
    PRIMARY KEY (document_ref, line_no)
);

CREATE TABLE IF NOT EXISTS tax_document_summary (
    document_ref VARCHAR NOT NULL,
    tax_code VARCHAR NOT NULL,
    rate_pct NUMERIC(19,4),
    charged_pct NUMERIC(19,4),
    net NUMERIC(18,2),
    vat NUMERIC(18,2),
    vat_due NUMERIC(18,2),
    gross NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (document_ref, tax_code)
);

CREATE TABLE IF NOT EXISTS tax_document_suggestion (
    document_ref VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    seq INTEGER NOT NULL,
    tax_code VARCHAR,
    reason VARCHAR,
    confidence INTEGER,
    model VARCHAR,
    inputs VARCHAR,
    state VARCHAR,
    created_by VARCHAR,
    decided_by VARCHAR,
    decided_at TIMESTAMP,
    decision_reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (document_ref, line_no, seq)
);

-- 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_document_versions (
    document_ref VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (document_ref, version)
);

CREATE TABLE IF NOT EXISTS tax_migration_map (
    odoo_db VARCHAR NOT NULL,
    odoo_model VARCHAR NOT NULL,
    odoo_id VARCHAR NOT NULL,
    our_table VARCHAR,
    our_key VARCHAR,
    created_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);

CREATE TABLE IF NOT EXISTS tax_party (
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    treatment_code VARCHAR,
    vat_number VARCHAR,
    checked_on DATE,
    check_result VARCHAR,
    recorded_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    check_name VARCHAR,
    check_address VARCHAR,
    consultation_number VARCHAR,
    check_flag VARCHAR,
    confirmed_by VARCHAR,
    confirm_reason VARCHAR,
    notice_kind VARCHAR,
    notice_ref VARCHAR,
    notice_date DATE,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS tax_rate (
    tax_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rate_pct NUMERIC(19,4),
    charged_pct NUMERIC(19,4),
    change_state VARCHAR,
    created_by VARCHAR,
    approved_by VARCHAR,
    withdrawn_by VARCHAR,
    withdrawn_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_sub VARCHAR,
    PRIMARY KEY (tax_code, valid_from)
);

CREATE TABLE IF NOT EXISTS tax_registration (
    jurisdiction VARCHAR NOT NULL,
    effective_from DATE NOT NULL,
    status VARCHAR,
    vat_number VARCHAR,
    scheme VARCHAR,
    effective_to DATE,
    change_state VARCHAR,
    created_by VARCHAR,
    approved_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    created_sub VARCHAR,
    accounting_basis VARCHAR,
    flat_rate_sector VARCHAR,
    flat_rate_pct NUMERIC(19,4),
    first_year_until DATE,
    ni_goods BOOLEAN,
    partial_exemption_year_end VARCHAR,
    expected_turnover NUMERIC(18,2),
    PRIMARY KEY (jurisdiction, effective_from)
);

CREATE TABLE IF NOT EXISTS tax_rule (
    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_settings (
    setting_key VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    value VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (setting_key, valid_from)
);

CREATE TABLE IF NOT EXISTS tax_treatment (
    treatment_code VARCHAR NOT NULL,
    description VARCHAR,
    needs_vat_number BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (treatment_code)
);

-- 00002_audit1 (v0.1.1): the author's sub beside their email (a rule, a rate, a registration, an override: the approver is
-- refused if EITHER identity matches), and the tax of an ISSUED document stored at freeze (per line and per rate), so a
-- later change to the rounding code never changes issued tax. At the END of each CREATE above (a new bucket) and added
-- here for a bucket on 00001. Migrate the test copy first, the live one at a quiet hour, before rolling the 0.1.1 image.
ALTER TABLE tax_registration ADD COLUMN created_sub VARCHAR;
ALTER TABLE tax_rate ADD COLUMN created_sub VARCHAR;
ALTER TABLE tax_rule ADD COLUMN created_sub VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN override_by_sub VARCHAR;
ALTER TABLE tax_document_line ADD COLUMN calc_net NUMERIC(18,2);
ALTER TABLE tax_document_line ADD COLUMN calc_vat NUMERIC(18,2);
ALTER TABLE tax_document_line ADD COLUMN calc_vat_due NUMERIC(18,2);
ALTER TABLE tax_document_line ADD COLUMN calc_gross NUMERIC(18,2);

-- v4 (00003): purchase rules, HMRC, the VAT return, bad debt relief.

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_consumption (
    category_code VARCHAR NOT NULL,
    treatment_code VARCHAR NOT NULL,
    supply VARCHAR NOT NULL,
    direction VARCHAR NOT NULL,
    consumption 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, direction, consumption, valid_from)
);

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)
);