dodil/erp-brick-timesheets
DODIL ERP brick: timesheets
src/brick_timesheets/schema.sql
-- timesheets (ts_): one person's week as a sheet per revision, its lines and entries, a project lead's line approvals, the activities, the dated
-- standard cost rates and each person's rate group, the claim guard rows, the billing claims, the outbox of side effects waiting for a
-- neighbour and the dated settings. Every table prefixed `ts_`, a natural primary key each (never SERIAL), money NUMERIC(18,2), hours and rates
-- NUMERIC(19,4), 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 ts_activity (
company_code VARCHAR NOT NULL,
activity_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
name VARCHAR,
billable_default BOOLEAN,
internal_only BOOLEAN,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, activity_code, valid_from)
);
CREATE TABLE IF NOT EXISTS ts_billing_claim (
sheet_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
line_no INTEGER NOT NULL,
purpose VARCHAR NOT NULL,
run_id VARCHAR,
delivery_ref VARCHAR,
sales_order_id VARCHAR,
so_line_no INTEGER,
quantity NUMERIC(19,4),
unit_price NUMERIC(19,4),
priced_from VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
product_id VARCHAR,
tax_category VARCHAR,
PRIMARY KEY (sheet_id, revision, line_no, purpose)
);
CREATE TABLE IF NOT EXISTS ts_billing_run (
run_id VARCHAR NOT NULL,
project_id VARCHAR,
period_id VARCHAR,
run_no INTEGER,
company_code VARCHAR,
bp_id BIGINT,
currency VARCHAR,
status VARCHAR,
source_id VARCHAR,
lines_json VARCHAR,
total_amount NUMERIC(18,2),
claim_count INTEGER,
invoice_id VARCHAR,
reason VARCHAR,
created_by VARCHAR,
released_by VARCHAR,
released_sub VARCHAR,
released_at TIMESTAMP,
released_on DATE,
ended_by VARCHAR,
ended_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS ts_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 ts_cost_rate (
company_code VARCHAR NOT NULL,
rate_group VARCHAR NOT NULL,
valid_from DATE NOT NULL,
rate_amount NUMERIC(19,4),
currency VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, rate_group, valid_from)
);
CREATE TABLE IF NOT EXISTS ts_entry (
sheet_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
line_no INTEGER NOT NULL,
work_date DATE NOT NULL,
hours NUMERIC(19,4),
note VARCHAR,
unit_cost NUMERIC(19,4),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sheet_id, revision, line_no, work_date)
);
CREATE TABLE IF NOT EXISTS ts_line (
sheet_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
line_no INTEGER NOT NULL,
kind VARCHAR,
activity_code VARCHAR,
project_id VARCHAR,
item_id VARCHAR,
cost_centre VARCHAR,
billable BOOLEAN,
bill_via VARCHAR,
sales_order_id VARCHAR,
so_line_no INTEGER,
product_id VARCHAR,
rate_group VARCHAR,
unit_cost NUMERIC(19,4),
absence_ref VARCHAR,
description VARCHAR,
lead_person_id BIGINT,
billing VARCHAR,
stale BOOLEAN,
stale_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sheet_id, revision, line_no)
);
CREATE TABLE IF NOT EXISTS ts_line_approval (
sheet_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
line_no INTEGER NOT NULL,
approver_person_id BIGINT,
decided_by VARCHAR,
decided_sub VARCHAR,
decided_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sheet_id, revision, line_no)
);
CREATE TABLE IF NOT EXISTS ts_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 ts_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 ts_outbox (
target VARCHAR NOT NULL,
ref VARCHAR NOT NULL,
sheet_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 ts_person_rate_group (
person_id BIGINT NOT NULL,
valid_from DATE NOT NULL,
rate_group VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (person_id, valid_from)
);
CREATE TABLE IF NOT EXISTS ts_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 ts_sheet (
sheet_id VARCHAR NOT NULL,
revision INTEGER NOT NULL,
person_id BIGINT,
company_code VARCHAR,
week_start DATE,
person_email VARCHAR,
person_sub VARCHAR,
status VARCHAR,
expected_minutes INTEGER,
expected_json VARCHAR,
home_cost_centre VARCHAR,
approval_route VARCHAR,
approver_person_id BIGINT,
approver_email VARCHAR,
approver_sub VARCHAR,
rerouted_from VARCHAR,
currency VARCHAR,
submitted_at TIMESTAMP,
submitted_by VARCHAR,
submitted_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
approved_at TIMESTAMP,
approved_on DATE,
journal_id BIGINT,
ended_by VARCHAR,
ended_sub VARCHAR,
ended_at TIMESTAMP,
reason VARCHAR,
last_line_no INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (sheet_id, revision)
);