dodil/erp-brick-fixed-assets

DODIL ERP brick: fixed assets

src/brick_fixed_assets/schema.sql

-- Tables of the fixed-assets brick (module `fixed-assets`). Every table prefixed `fa_`, a natural primary key each (never
-- SERIAL), money NUMERIC(18,2), units and rates NUMERIC(19,4), creation columns last. Additive only.

CREATE TABLE IF NOT EXISTS fa_account_map (
    class_code VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    cost_account_id BIGINT,
    accumulated_depreciation_account_id BIGINT,
    depreciation_expense_account_id BIGINT,
    gain_account_id BIGINT,
    loss_account_id BIGINT,
    revaluation_reserve_account_id BIGINT,
    impairment_account_id BIGINT,
    impairment_reversal_account_id BIGINT,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (class_code, area_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_acquisition_claim (
    acquisition_ref VARCHAR NOT NULL,
    bill_id VARCHAR,
    line_no INTEGER,
    asset_id VARCHAR,
    amount NUMERIC(18,2),
    transaction_id VARCHAR,
    taken_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (acquisition_ref)
);

CREATE TABLE IF NOT EXISTS fa_ai_finding (
    subject_ref VARCHAR NOT NULL,
    step VARCHAR NOT NULL,
    run_no INTEGER NOT NULL,
    finding_no INTEGER NOT NULL,
    model VARCHAR,
    inputs_digest VARCHAR,
    field VARCHAR,
    value VARCHAR,
    evidence VARCHAR,
    decision VARCHAR,
    decided_by_email VARCHAR,
    decided_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (subject_ref, step, run_no, finding_no)
);

CREATE TABLE IF NOT EXISTS fa_area (
    area_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    name VARCHAR,
    purpose VARCHAR,
    ledger_id VARCHAR,
    posting VARCHAR,
    value_model VARCHAR,
    values_from VARCHAR,
    currency VARCHAR,
    company_code VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (area_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_asset (
    asset_id VARCHAR NOT NULL,
    company_code VARCHAR,
    asset_no VARCHAR,
    sub_no INTEGER,
    class_code VARCHAR,
    description VARCHAR,
    serial_no VARCHAR,
    inventory_no VARCHAR,
    bp_id BIGINT,
    capitalised_on DATE,
    status VARCHAR,
    last_counted_on DATE,
    quantity NUMERIC(19,4),
    request_id VARCHAR,
    is_auc BOOLEAN,
    is_car BOOLEAN,
    new_and_unused BOOLEAN,
    ca_pool VARCHAR,
    deactivated_reason VARCHAR,
    created_by VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_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 fa_asset_versions (
    asset_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (asset_id, version)
);

CREATE TABLE IF NOT EXISTS fa_asset_area (
    asset_id VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    key_code VARCHAR,
    life_periods INTEGER,
    total_units NUMERIC(19,4),
    scrap_value NUMERIC(18,2),
    dep_start VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_id, area_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_asset_master (
    asset_id VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    cost_centre VARCHAR,
    location_id VARCHAR,
    custodian_email VARCHAR,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_id, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_asset_request (
    request_id VARCHAR NOT NULL,
    asset_id VARCHAR,
    company_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (request_id)
);

CREATE TABLE IF NOT EXISTS fa_auc_settlement (
    auc_asset_id VARCHAR NOT NULL,
    settlement_date DATE NOT NULL,
    seq INTEGER NOT NULL,
    status VARCHAR,
    rule VARCHAR,
    settled_by_email VARCHAR,
    settled_on DATE,
    reversed_reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (auc_asset_id, settlement_date, seq)
);

CREATE TABLE IF NOT EXISTS fa_ca_claim (
    company_code VARCHAR NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    asset_id VARCHAR NOT NULL,
    claim VARCHAR,
    pool VARCHAR,
    amount NUMERIC(18,2),
    disposal_value NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, period_start, period_end, asset_id)
);

CREATE TABLE IF NOT EXISTS fa_ca_computation (
    company_code VARCHAR NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status VARCHAR,
    months INTEGER,
    aia_limit_allocated NUMERIC(18,2),
    opening VARCHAR,
    result VARCHAR,
    prepared_by_email VARCHAR,
    prepared_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    return_reference VARCHAR,
    returned_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, period_start, period_end)
);

-- 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 fa_ca_computation_versions (
    company_code VARCHAR NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (company_code, period_start, period_end, version)
);

CREATE TABLE IF NOT EXISTS fa_ca_rate (
    rate_key VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    value NUMERIC(19,4),
    legislation_ref VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rate_key, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_class (
    class_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    name VARCHAR,
    is_auc BOOLEAN,
    is_low_value BOOLEAN,
    default_ca_pool VARCHAR,
    number_format VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (class_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_class_area (
    class_code VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    key_code VARCHAR,
    life_periods INTEGER,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (class_code, area_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_clock (
    clock_key VARCHAR NOT NULL,
    last_today DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (clock_key)
);

CREATE TABLE IF NOT EXISTS fa_count (
    location_id VARCHAR NOT NULL,
    count_date DATE NOT NULL,
    custodian_email VARCHAR,
    status VARCHAR,
    opened_by_email VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (location_id, count_date)
);

-- 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 fa_count_versions (
    location_id VARCHAR NOT NULL,
    count_date DATE NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (location_id, count_date, version)
);

CREATE TABLE IF NOT EXISTS fa_count_line (
    location_id VARCHAR NOT NULL,
    count_date DATE NOT NULL,
    line_no INTEGER NOT NULL,
    asset_id VARCHAR,
    tag VARCHAR,
    description VARCHAR,
    serial_no VARCHAR,
    result VARCHAR,
    counted_by_email VARCHAR,
    counted_on DATE,
    decision VARCHAR,
    decision_ref VARCHAR,
    note VARCHAR,
    moved_in BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (location_id, count_date, line_no)
);

CREATE TABLE IF NOT EXISTS fa_dep_key (
    key_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    method VARCHAR,
    rate_pct NUMERIC(19,4),
    life_periods INTEGER,
    base VARCHAR,
    acq_conv VARCHAR,
    ret_conv VARCHAR,
    stop_at VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (key_code, valid_from)
);

CREATE TABLE IF NOT EXISTS fa_dep_post_claim (
    company_code VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    post_seq INTEGER NOT NULL,
    run_id VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, period_id, area_code, post_seq)
);

CREATE TABLE IF NOT EXISTS fa_dep_posting (
    asset_id VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    run_id VARCHAR NOT NULL,
    amount NUMERIC(18,2),
    cost_centre VARCHAR,
    class_code VARCHAR,
    journal_id BIGINT,
    kind VARCHAR,
    posted_on DATE,
    company_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_id, area_code, period_id, run_id)
);

CREATE TABLE IF NOT EXISTS fa_dep_run (
    company_code VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    run_no INTEGER NOT NULL,
    run_id VARCHAR,
    status VARCHAR,
    started_by_email VARCHAR,
    started_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    posted_on DATE,
    posted_by VARCHAR,
    reverses_run_id VARCHAR,
    reversed_by_email VARCHAR,
    reason VARCHAR,
    totals VARCHAR,
    gates VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, period_id, run_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 fa_dep_run_versions (
    company_code VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    run_no INTEGER NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (company_code, period_id, run_no, version)
);

CREATE TABLE IF NOT EXISTS fa_dep_run_line (
    company_code VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    run_no INTEGER NOT NULL,
    asset_id VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    class_code VARCHAR,
    cost_centre VARCHAR,
    planned_to_date NUMERIC(18,2),
    posted_before NUMERIC(18,2),
    amount NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, period_id, run_no, asset_id, area_code)
);

CREATE TABLE IF NOT EXISTS fa_migration_map (
    odoo_db VARCHAR NOT NULL,
    odoo_model VARCHAR NOT NULL,
    odoo_id VARCHAR NOT NULL,
    natural_key VARCHAR,
    run_id VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);

CREATE TABLE IF NOT EXISTS fa_migration_run (
    run_id VARCHAR NOT NULL,
    odoo_db VARCHAR,
    cutover_date DATE,
    status VARCHAR,
    doors VARCHAR,
    checks VARCHAR,
    checksums VARCHAR,
    loaded_by VARCHAR,
    signed_by_email VARCHAR,
    signed_by_sub VARCHAR,
    signed_on DATE,
    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 fa_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 fa_number_claim (
    asset_no VARCHAR NOT NULL,
    series_id VARCHAR,
    year INTEGER,
    seq INTEGER,
    request_id VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_no)
);

CREATE TABLE IF NOT EXISTS fa_number_series (
    series_id VARCHAR NOT NULL,
    year INTEGER NOT NULL,
    format VARCHAR,
    high_water_mark INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (series_id, year)
);

CREATE TABLE IF NOT EXISTS fa_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 fa_settlement_claim (
    auc_asset_id VARCHAR NOT NULL,
    line_item_transaction_id VARCHAR NOT NULL,
    receiver_asset_id VARCHAR NOT NULL,
    settlement_seq INTEGER,
    settlement_date DATE,
    percent NUMERIC(19,4),
    amount NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (auc_asset_id, line_item_transaction_id, receiver_asset_id)
);

CREATE TABLE IF NOT EXISTS fa_settlement_line_seq (
    auc_asset_id VARCHAR NOT NULL,
    line_item_transaction_id VARCHAR NOT NULL,
    n INTEGER NOT NULL,
    settlement_seq INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (auc_asset_id, line_item_transaction_id, n)
);

CREATE TABLE IF NOT EXISTS fa_task (
    task_id VARCHAR NOT NULL,
    step VARCHAR,
    subject_ref VARCHAR,
    status VARCHAR,
    capability VARCHAR,
    input_json VARCHAR,
    plan VARCHAR,
    reason VARCHAR,
    destructive BOOLEAN,
    summary VARCHAR,
    output_json VARCHAR,
    created_by VARCHAR,
    resolved_by VARCHAR,
    note VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (task_id)
);

CREATE TABLE IF NOT EXISTS fa_transaction (
    transaction_id VARCHAR NOT NULL,
    asset_id VARCHAR,
    company_code VARCHAR,
    group_id VARCHAR,
    leg VARCHAR,
    kind VARCHAR,
    value_date DATE,
    posting_period_id VARCHAR,
    fiscal_year INTEGER,
    status VARCHAR,
    proposed_by_email VARCHAR,
    proposed_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    posted_on DATE,
    rejected_reason VARCHAR,
    reverses_transaction_id VARCHAR,
    evidence_key VARCHAR,
    source_ref VARCHAR,
    params VARCHAR,
    asset_version INTEGER,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (transaction_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 fa_transaction_versions (
    transaction_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (transaction_id, version)
);

CREATE TABLE IF NOT EXISTS fa_transaction_area (
    transaction_id VARCHAR NOT NULL,
    area_code VARCHAR NOT NULL,
    cost NUMERIC(18,2),
    accumulated_depreciation NUMERIC(18,2),
    depreciation_in_year NUMERIC(18,2),
    revaluation_reserve NUMERIC(18,2),
    gain_loss NUMERIC(18,2),
    proceeds NUMERIC(18,2),
    catch_up NUMERIC(18,2),
    journal_id BIGINT,
    ledger_id VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (transaction_id, area_code)
);

CREATE TABLE IF NOT EXISTS fa_usage (
    asset_id VARCHAR NOT NULL,
    period_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    units NUMERIC(19,4),
    recorded_by_email VARCHAR,
    reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (asset_id, period_id, seq)
);

CREATE TABLE IF NOT EXISTS fa_year (
    company_code VARCHAR NOT NULL,
    fiscal_year INTEGER NOT NULL,
    status VARCHAR,
    closed_by_email VARCHAR,
    closed_by_sub VARCHAR,
    closed_on DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (company_code, fiscal_year)
);