dodil/erp-brick-consolidation

DODIL ERP brick: consolidation

src/brick_consolidation/migrations/00001_init/up.sql

-- 00001_init — the brick's first schema (Generated by `erp-compose scaffold` from module.yaml).
-- The versioned layout (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal, cons_schema_migrations.
-- erp-platform has no migrator for this layout yet: today `erp-migrate --schema schema.sql` applies the
-- same tables (schema.sql), and these files are what the migrator will run.

CREATE TABLE IF NOT EXISTS cons_schema_migrations (
    migration  VARCHAR NOT NULL,
    applied_at TIMESTAMP NOT NULL,
    PRIMARY KEY (migration)
);

CREATE TABLE IF NOT EXISTS cons_group (
    group_id              VARCHAR NOT NULL,
    name                  VARCHAR,
    group_currency        VARCHAR,
    ledger_id             VARCHAR,
    group_chart_id        VARCHAR,
    top_company_code      VARCHAR,
    fiscal_year_end_month VARCHAR,
    fsv_id                VARCHAR,
    goodwill_life_years   VARCHAR,
    cash_flow_fsv_id      VARCHAR,
    version               INTEGER,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    PRIMARY KEY (group_id)
);

CREATE TABLE IF NOT EXISTS cons_group_structure (
    group_id              VARCHAR NOT NULL,
    company_code          VARCHAR NOT NULL,
    valid_from_period     VARCHAR NOT NULL,
    investor_company_code VARCHAR,
    ownership_pct         NUMERIC(9,6),
    voting_pct            NUMERIC(9,6),
    method                VARCHAR,
    change_id             VARCHAR,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    PRIMARY KEY (group_id, company_code, valid_from_period)
);

CREATE TABLE IF NOT EXISTS cons_account_rule (
    group_id          VARCHAR NOT NULL,
    group_account_id  VARCHAR NOT NULL,
    valid_from_period VARCHAR NOT NULL,
    rate_type         VARCHAR,
    ic                VARCHAR,
    equity            VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (group_id, group_account_id, valid_from_period)
);

CREATE TABLE IF NOT EXISTS cons_account_role (
    group_id          VARCHAR NOT NULL,
    role              VARCHAR NOT NULL,
    valid_from_period VARCHAR NOT NULL,
    group_account_id  VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (group_id, role, valid_from_period)
);

CREATE TABLE IF NOT EXISTS cons_elimination_rule (
    group_id        VARCHAR NOT NULL,
    rule_id         VARCHAR NOT NULL,
    kind            VARCHAR,
    left_accounts   VARCHAR,
    right_accounts  VARCHAR,
    difference_role VARCHAR,
    tolerance       NUMERIC(18,2),
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (group_id, rule_id)
);

CREATE TABLE IF NOT EXISTS cons_group_period (
    group_id        VARCHAR NOT NULL,
    period_id       VARCHAR NOT NULL,
    run_no          INTEGER,
    stale_from_step VARCHAR,
    prepared_by     VARCHAR,
    prepared_sub    VARCHAR,
    signed_by       VARCHAR,
    signed_sub      VARCHAR,
    signed_at       TIMESTAMP,
    tb_digest       VARCHAR,
    reopened_reason VARCHAR,
    status          VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (group_id, period_id)
);

CREATE TABLE IF NOT EXISTS cons_reported_data (
    group_id         VARCHAR NOT NULL,
    period_id        VARCHAR NOT NULL,
    company_code     VARCHAR NOT NULL,
    group_account_id VARCHAR NOT NULL,
    partner_company  VARCHAR NOT NULL,
    movement_type    VARCHAR NOT NULL,
    segment          VARCHAR NOT NULL,
    ytd_local        NUMERIC(18,2),
    period_local     NUMERIC(18,2),
    local_currency   VARCHAR,
    source           VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (group_id, period_id, company_code, group_account_id, partner_company, movement_type, segment)
);

CREATE TABLE IF NOT EXISTS cons_upload (
    group_id      VARCHAR NOT NULL,
    period_id     VARCHAR NOT NULL,
    company_code  VARCHAR NOT NULL,
    upload_no     INTEGER NOT NULL,
    object_key    VARCHAR,
    sha256        VARCHAR,
    rows          VARCHAR,
    status        VARCHAR,
    submitted_by  VARCHAR,
    submitted_sub VARCHAR,
    accepted_by   VARCHAR,
    accepted_sub  VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (group_id, period_id, company_code, upload_no)
);

CREATE TABLE IF NOT EXISTS cons_translated_data (
    group_id         VARCHAR NOT NULL,
    period_id        VARCHAR NOT NULL,
    run_no           INTEGER NOT NULL,
    company_code     VARCHAR NOT NULL,
    group_account_id VARCHAR NOT NULL,
    partner_company  VARCHAR NOT NULL,
    movement_type    VARCHAR NOT NULL,
    segment          VARCHAR NOT NULL,
    ytd_group        NUMERIC(18,2),
    rate_type        VARCHAR,
    rate             NUMERIC(19,6),
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (group_id, period_id, run_no, company_code, group_account_id, partner_company, movement_type, segment)
);

CREATE TABLE IF NOT EXISTS cons_ic_inventory (
    group_id         VARCHAR NOT NULL,
    period_id        VARCHAR NOT NULL,
    holder_company   VARCHAR NOT NULL,
    seller_company   VARCHAR NOT NULL,
    inventory_amount NUMERIC(18,2),
    margin_pct       NUMERIC(9,6),
    none_stated      VARCHAR,
    entered_by       VARCHAR,
    entered_sub      VARCHAR,
    tax_rate_pct     NUMERIC(9,6),
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (group_id, period_id, holder_company, seller_company)
);

CREATE TABLE IF NOT EXISTS cons_historical_amount (
    group_id         VARCHAR NOT NULL,
    company_code     VARCHAR NOT NULL,
    group_account_id VARCHAR NOT NULL,
    arose_period     VARCHAR NOT NULL,
    amount_local     NUMERIC(18,2),
    amount_group     NUMERIC(18,2),
    rate             NUMERIC(19,6),
    change_id        VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (group_id, company_code, group_account_id, arose_period)
);

CREATE TABLE IF NOT EXISTS cons_number_series (
    group_id        VARCHAR NOT NULL,
    fiscal_year     VARCHAR NOT NULL,
    entry_no        INTEGER NOT NULL,
    cons_journal_id VARCHAR,
    claimed_at      TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (group_id, fiscal_year, entry_no)
);

CREATE TABLE IF NOT EXISTS cons_carry_forward (
    group_id         VARCHAR NOT NULL,
    fiscal_year      VARCHAR NOT NULL,
    group_account_id VARCHAR NOT NULL,
    company_code     VARCHAR NOT NULL,
    partner_company  VARCHAR NOT NULL,
    segment          VARCHAR NOT NULL,
    opening          VARCHAR,
    derived_at       TIMESTAMP,
    source_digest    VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (group_id, fiscal_year, group_account_id, company_code, partner_company, segment)
);

-- 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 cons_group_versions (
    group_id VARCHAR NOT NULL,
    version  INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (group_id, version)
);

CREATE TABLE IF NOT EXISTS cons_structure_change (
    change_id                   VARCHAR NOT NULL,
    kind                        VARCHAR,
    company_code                VARCHAR,
    investor_company_code       VARCHAR,
    ownership_pct               NUMERIC(9,6),
    voting_pct                  NUMERIC(9,6),
    method                      VARCHAR,
    currency                    VARCHAR,
    effective_date              DATE,
    consideration               NUMERIC(18,2),
    proceeds                    NUMERIC(18,2),
    previously_held_fair_value  NUMERIC(18,2),
    net_assets_json             VARCHAR,
    fair_value_adjustments_json VARCHAR,
    net_assets                  NUMERIC(18,2),
    goodwill                    NUMERIC(18,2),
    nci                         NUMERIC(18,2),
    gain_loss                   NUMERIC(18,2),
    rate                        NUMERIC(19,6),
    goodwill_group              NUMERIC(18,2),
    nci_group                   NUMERIC(18,2),
    bargain_purchase            VARCHAR,
    proposed_by                 VARCHAR,
    proposed_sub                VARCHAR,
    approved_by                 VARCHAR,
    approved_sub                VARCHAR,
    status                      VARCHAR,
    version                     INTEGER,
    created_at                  TIMESTAMP,
    updated_at                  TIMESTAMP,
    PRIMARY KEY (change_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 cons_structure_change_versions (
    change_id VARCHAR NOT NULL,
    version   INTEGER NOT NULL,
    saved_at  TIMESTAMP NOT NULL,
    PRIMARY KEY (change_id, version)
);

CREATE TABLE IF NOT EXISTS cons_journal (
    cons_journal_id  VARCHAR NOT NULL,
    group_id         VARCHAR,
    period_id        VARCHAR,
    document_type    VARCHAR,
    auto             VARCHAR,
    task             VARCHAR,
    run_no           INTEGER,
    period_specific  VARCHAR,
    entry_no         INTEGER,
    proposed_by      VARCHAR,
    proposed_sub     VARCHAR,
    approved_by      VARCHAR,
    approved_sub     VARCHAR,
    reverses_id      VARCHAR,
    memo             VARCHAR,
    migration_run_id VARCHAR,
    status           VARCHAR,
    version          INTEGER,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (cons_journal_id)
);

CREATE TABLE IF NOT EXISTS cons_journal_line (
    cons_journal_id  VARCHAR NOT NULL,
    line_no          VARCHAR NOT NULL,
    group_account_id VARCHAR,
    company_code     VARCHAR,
    partner_company  VARCHAR,
    movement_type    VARCHAR,
    segment          VARCHAR,
    amount           NUMERIC(18,2),
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (cons_journal_id, line_no)
);

-- 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 cons_journal_versions (
    cons_journal_id VARCHAR NOT NULL,
    version         INTEGER NOT NULL,
    saved_at        TIMESTAMP NOT NULL,
    PRIMARY KEY (cons_journal_id, version)
);

CREATE TABLE IF NOT EXISTS cons_statement (
    report      VARCHAR NOT NULL,
    group_id    VARCHAR NOT NULL,
    period_id   VARCHAR NOT NULL,
    node_id     VARCHAR NOT NULL,
    amount      NUMERIC(18,2),
    prior_year  VARCHAR,
    produced_at TIMESTAMP,
    digest      VARCHAR,
    version     INTEGER,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (report, group_id, period_id, node_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 cons_statement_versions (
    report    VARCHAR NOT NULL,
    group_id  VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    node_id   VARCHAR NOT NULL,
    version   INTEGER NOT NULL,
    saved_at  TIMESTAMP NOT NULL,
    PRIMARY KEY (report, group_id, period_id, node_id, version)
);

CREATE TABLE IF NOT EXISTS cons_ai_finding (
    finding_id    VARCHAR NOT NULL,
    group_id      VARCHAR,
    period_id     VARCHAR,
    pair          VARCHAR,
    model         VARCHAR,
    inputs_digest VARCHAR,
    finding       VARCHAR,
    evidence      VARCHAR,
    decision      VARCHAR,
    decided_by    VARCHAR,
    decided_at    TIMESTAMP,
    version       INTEGER,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (finding_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 cons_ai_finding_versions (
    finding_id VARCHAR NOT NULL,
    version    INTEGER NOT NULL,
    saved_at   TIMESTAMP NOT NULL,
    PRIMARY KEY (finding_id, version)
);

CREATE TABLE IF NOT EXISTS cons_migration_run (
    run_id      VARCHAR NOT NULL,
    status      VARCHAR,
    doors_json  VARCHAR,
    checks_json VARCHAR,
    signed_by   VARCHAR,
    signed_at   TIMESTAMP,
    checksums   VARCHAR,
    version     INTEGER,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (run_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 cons_migration_run_versions (
    run_id   VARCHAR NOT NULL,
    version  INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (run_id, version)
);

CREATE TABLE IF NOT EXISTS cons_migration_map (
    odoo_db     VARCHAR NOT NULL,
    odoo_model  VARCHAR NOT NULL,
    odoo_id     VARCHAR NOT NULL,
    natural_key VARCHAR,
    run_id      VARCHAR,
    version     INTEGER,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_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 cons_migration_map_versions (
    odoo_db    VARCHAR NOT NULL,
    odoo_model VARCHAR NOT NULL,
    odoo_id    VARCHAR NOT NULL,
    version    INTEGER NOT NULL,
    saved_at   TIMESTAMP NOT NULL,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id, version)
);

CREATE TABLE IF NOT EXISTS cons_cons_migration_runs (
    cons_migration_run_id VARCHAR NOT NULL,
    status                VARCHAR,
    version               INTEGER,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    PRIMARY KEY (cons_migration_run_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 cons_cons_migration_runs_versions (
    cons_migration_run_id VARCHAR NOT NULL,
    version               INTEGER NOT NULL,
    saved_at              TIMESTAMP NOT NULL,
    PRIMARY KEY (cons_migration_run_id, version)
);

-- append-only history: rows are appended on save, never updated.
CREATE TABLE IF NOT EXISTS cons_run_claim (
    group_id   VARCHAR NOT NULL,
    period_id  VARCHAR NOT NULL,
    task       VARCHAR NOT NULL,
    run_no     INTEGER NOT NULL,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (group_id, period_id, task, run_no)
);

CREATE TABLE IF NOT EXISTS cons_signoff (
    group_id   VARCHAR NOT NULL,
    period_id  VARCHAR NOT NULL,
    seq        INTEGER NOT NULL,
    action     VARCHAR,
    by_email   VARCHAR,
    by_sub     VARCHAR,
    at         TIMESTAMP,
    tb_digest  VARCHAR,
    reason     VARCHAR,
    run_no     INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (group_id, period_id, seq)
);

CREATE TABLE IF NOT EXISTS cons_collection (
    group_id       VARCHAR NOT NULL,
    period_id      VARCHAR NOT NULL,
    company_code   VARCHAR NOT NULL,
    run_no         INTEGER NOT NULL,
    gl_digest      VARCHAR,
    period_status  VARCHAR,
    local_currency VARCHAR,
    provisional    VARCHAR,
    collected_at   TIMESTAMP,
    source         VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (group_id, period_id, company_code, run_no)
);

CREATE TABLE IF NOT EXISTS cons_audit (
    audit_id   VARCHAR NOT NULL,
    group_id   VARCHAR,
    period_id  VARCHAR,
    subject    VARCHAR,
    event      VARCHAR,
    sub        VARCHAR,
    event_at   TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (audit_id)
);

-- module.yaml config_rows: one row per setting (JSON value); NULL = the reference behaviour.
CREATE TABLE IF NOT EXISTS cons_settings (
    setting_key VARCHAR NOT NULL,
    value       VARCHAR,
    changes     VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (setting_key)
);

INSERT INTO cons_settings (setting_key, value, changes) VALUES ('signoff_second_person', NULL, 'whether manual entries, uploads, structure changes and the sign-off need a second person; cons:settings:controls');
INSERT INTO cons_settings (setting_key, value, changes) VALUES ('allow_open_period_collect', NULL, 'collect from a company whose gl period is still open (flagged provisional); cons:settings:controls');
INSERT INTO cons_settings (setting_key, value, changes) VALUES ('translation_pl_rate', NULL, 'monthly_average | ytd_average: how income and expense are translated (IAS 21.40 permits an average that approximates the transaction rates)');
INSERT INTO cons_settings (setting_key, value, changes) VALUES ('nci_measurement', NULL, 'proportionate share of net assets (IFRS 3.19); full goodwill is an extension');
INSERT INTO cons_settings (setting_key, value, changes) VALUES ('ai.explain_elimination_difference.mode', NULL, 'a pair whose intercompany balances differ comes with a likely cause (an invoice in one month and the bill in the next, a different rate, a missing recharge): off or suggest');

INSERT INTO cons_schema_migrations (migration, applied_at) VALUES ('00001_init', CURRENT_TIMESTAMP);