dodil/erp-brick-tax

DODIL ERP brick: tax

src/brick_tax/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, tax_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.

-- 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,
    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,
    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,
    PRIMARY KEY (document_ref, line_no)
);

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