dodil/erp-brick-expenses
DODIL ERP brick: expenses
src/brick_expenses/schema.sql
-- expenses (exp_): expense reports, their lines and approval steps, the claim guard rows, the outbox of side effects waiting for a neighbour,
-- categories, dated settings and the leaver notices from hr. Every table prefixed `exp_`, a natural primary key each (never SERIAL), money
-- NUMERIC(18,2), creation columns last so a later column is an append. Additive only. Kept equal to infrastructure/tables.py by tests/test_schema.py.
CREATE TABLE IF NOT EXISTS exp_ai_finding (
report_id VARCHAR NOT NULL,
line_no INTEGER NOT NULL,
finding_no INTEGER NOT NULL,
step_key VARCHAR,
model VARCHAR,
inputs_digest VARCHAR,
fields_json VARCHAR,
confirmed_json VARCHAR,
decided_by VARCHAR,
decided_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (report_id, line_no, finding_no)
);
CREATE TABLE IF NOT EXISTS exp_approval_step (
report_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
level INTEGER NOT NULL,
kind VARCHAR,
approver_person_id BIGINT,
decision VARCHAR,
decided_by VARCHAR,
decided_sub VARCHAR,
decided_at TIMESTAMP,
reason VARCHAR,
delegate_for VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (report_id, revision, level)
);
CREATE TABLE IF NOT EXISTS exp_card (
card_id VARCHAR NOT NULL,
company_code VARCHAR,
holder_person_id BIGINT,
provider VARCHAR,
currency VARCHAR,
account_ref VARCHAR,
status VARCHAR,
stopped_from DATE,
holder_email VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (card_id)
);
CREATE TABLE IF NOT EXISTS exp_card_claim (
card_txn_id VARCHAR NOT NULL,
operation VARCHAR NOT NULL,
report_id VARCHAR,
payout_ref VARCHAR,
payment_id VARCHAR,
claimed_by VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (card_txn_id, operation)
);
CREATE TABLE IF NOT EXISTS exp_card_txn (
card_txn_id VARCHAR NOT NULL,
card_id VARCHAR,
company_code VARCHAR,
holder_person_id BIGINT,
posted_on DATE,
merchant VARCHAR,
billed_amount NUMERIC(18,2),
billed_currency VARCHAR,
original_amount NUMERIC(18,2),
original_currency VARCHAR,
status VARCHAR,
statement_ref VARCHAR,
matched_report_id VARCHAR,
matched_line_no INTEGER,
recovery_journal_id BIGINT,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (card_txn_id)
);
CREATE TABLE IF NOT EXISTS exp_category (
company_code VARCHAR NOT NULL,
category_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
name VARCHAR,
gl_purpose VARCHAR,
tax_category VARCHAR,
receipt_required BOOLEAN,
kind VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, category_code, valid_from)
);
CREATE TABLE IF NOT EXISTS exp_claim (
claim_scope VARCHAR NOT NULL,
claim_key VARCHAR NOT NULL,
n INTEGER NOT NULL,
ref VARCHAR,
claimed_by VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (claim_scope, claim_key, n)
);
CREATE TABLE IF NOT EXISTS exp_consent (
person_id BIGINT NOT NULL,
step_key VARCHAR NOT NULL,
given_at TIMESTAMP NOT NULL,
decision VARCHAR,
text_version VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (person_id, step_key, given_at)
);
CREATE TABLE IF NOT EXISTS exp_delegation (
delegator VARCHAR NOT NULL,
delegate VARCHAR NOT NULL,
valid_from DATE NOT NULL,
delegator_email VARCHAR,
delegator_sub VARCHAR,
delegate_email VARCHAR,
delegate_sub VARCHAR,
valid_to DATE,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (delegator, delegate, valid_from)
);
CREATE TABLE IF NOT EXISTS exp_leaver (
source_ref VARCHAR NOT NULL,
person_id BIGINT,
last_day DATE,
claims_until DATE,
open_reports INTEGER,
cards_stopped INTEGER,
rerouted_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (source_ref)
);
CREATE TABLE IF NOT EXISTS exp_line (
report_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
line_no INTEGER NOT NULL,
kind VARCHAR,
category_code VARCHAR,
expense_date DATE,
merchant VARCHAR,
receipt_amount NUMERIC(18,2),
receipt_currency VARCHAR,
amount NUMERIC(18,2),
paid_with VARCHAR,
vat_amount NUMERIC(18,2),
cost_centre VARCHAR,
receipt_key VARCHAR,
description VARCHAR,
city VARCHAR,
nights INTEGER,
attendees_json VARCHAR,
justification VARCHAR,
policy_result_json VARCHAR,
ai_fields_json VARCHAR,
stale BOOLEAN,
stale_reason VARCHAR,
card_txn_id VARCHAR,
recomputed_json VARCHAR,
fx_rate NUMERIC(19,6),
treatment_code VARCHAR,
contents_json VARCHAR,
tax_code VARCHAR,
vat_reclaimable NUMERIC(18,2),
vat_non_reclaimable NUMERIC(18,2),
tax_json VARCHAR,
receipt_vat NUMERIC(18,2),
possible_duplicate_of VARCHAR,
miles_or_km NUMERIC(19,4),
trip_from TIMESTAMP,
trip_to TIMESTAMP,
meals_provided_json VARCHAR,
calc_json VARCHAR,
rate_country VARCHAR,
rate_variant VARCHAR,
rebill_bp_id BIGINT,
rebill_markup_pct NUMERIC(19,4),
rebill_tax_category VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (report_id, revision, line_no)
);
CREATE TABLE IF NOT EXISTS exp_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id VARCHAR NOT NULL,
ours VARCHAR,
run_id VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
CREATE TABLE IF NOT EXISTS exp_migration_run (
run_id VARCHAR NOT NULL,
odoo_db VARCHAR,
cutover_date DATE,
company_code VARCHAR,
status VARCHAR,
doors_json VARCHAR,
checks_json VARCHAR,
summary_json VARCHAR,
loaded_by VARCHAR,
signed_by VARCHAR,
signed_sub VARCHAR,
signed_on DATE,
discarded_by VARCHAR,
discard_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS exp_outbox (
target VARCHAR NOT NULL,
ref VARCHAR NOT NULL,
report_id VARCHAR,
payload_json VARCHAR,
status VARCHAR,
attempts INTEGER,
last_error VARCHAR,
sent_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (target, ref)
);
CREATE TABLE IF NOT EXISTS exp_policy_rule (
company_code VARCHAR NOT NULL,
rule_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
category_code VARCHAR,
scope VARCHAR,
city_or_country VARCHAR,
limit_amount NUMERIC(18,2),
currency VARCHAR,
mode VARCHAR,
requires VARCHAR,
message VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, rule_code, valid_from)
);
CREATE TABLE IF NOT EXISTS exp_rate (
kind VARCHAR NOT NULL,
country VARCHAR NOT NULL,
variant VARCHAR NOT NULL,
band INTEGER NOT NULL,
valid_from DATE NOT NULL,
rate_amount NUMERIC(19,4),
currency VARCHAR,
band_upto NUMERIC(19,4),
unit VARCHAR,
partial_day_amount NUMERIC(19,4),
breakfast_pct NUMERIC(19,4),
lunch_pct NUMERIC(19,4),
dinner_pct NUMERIC(19,4),
year_start VARCHAR,
source VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (kind, country, variant, band, valid_from)
);
CREATE TABLE IF NOT EXISTS exp_receipt_image (
object_key VARCHAR NOT NULL,
report_id VARCHAR,
line_no INTEGER,
sha256 VARCHAR,
content_type VARCHAR,
size_bytes INTEGER,
uploaded_by VARCHAR,
uploaded_at TIMESTAMP,
retain_until DATE,
possible_duplicate_of VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (object_key)
);
CREATE TABLE IF NOT EXISTS exp_report (
report_id VARCHAR NOT NULL,
person_id BIGINT,
company_code VARCHAR,
bp_id BIGINT,
claimant_email VARCHAR,
claimant_sub VARCHAR,
revision INTEGER,
title VARCHAR,
currency VARCHAR,
status VARCHAR,
submitted_at TIMESTAMP,
submitted_by VARCHAR,
submitted_sub VARCHAR,
submitted_on DATE,
chain_json VARCHAR,
waiting_level INTEGER,
waiting_kind VARCHAR,
waiting_email VARCHAR,
waiting_sub VARCHAR,
journal_id BIGINT,
payout_ref VARCHAR,
approved_on DATE,
paid_on DATE,
payment_id VARCHAR,
fail_reason VARCHAR,
total_amount NUMERIC(18,2),
reimbursable_amount NUMERIC(18,2),
netted_amount NUMERIC(18,2),
waiting_since DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (report_id)
);
CREATE TABLE IF NOT EXISTS exp_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)
);