dodil/erp-brick-time-off
DODIL ERP brick: time off
src/brick_time_off/schema.sql
-- time-off (tof_): leave requests, the half-day claim rows, the append-only balance ledger, the claim guard rows, leave types, policies, dated
-- settings and the leaver notices from hr. Every table prefixed `tof_`, a natural primary key each (never SERIAL), quantities 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 tof_balance_entry (
entry_ref VARCHAR NOT NULL,
person_id BIGINT,
leave_type_code VARCHAR,
leave_year INTEGER,
kind VARCHAR,
quantity NUMERIC(19,4),
unit VARCHAR,
on_date DATE,
by_email VARCHAR,
by_sub VARCHAR,
reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (entry_ref)
);
CREATE TABLE IF NOT EXISTS tof_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 tof_day_claim (
person_id BIGINT NOT NULL,
absence_date DATE NOT NULL,
part VARCHAR NOT NULL,
absence_ref VARCHAR,
minutes INTEGER,
quantity NUMERIC(19,4),
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (person_id, absence_date, part)
);
CREATE TABLE IF NOT EXISTS tof_leave_type (
company_code VARCHAR NOT NULL,
leave_type_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
name VARCHAR,
pay_treatment VARCHAR,
recorded_or_requested VARCHAR,
counts_against VARCHAR,
evidence_rule VARCHAR,
max_days_per_year INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, leave_type_code, valid_from)
);
CREATE TABLE IF NOT EXISTS tof_leaver (
source_ref VARCHAR NOT NULL,
person_id BIGINT,
n INTEGER,
last_day DATE,
cancelled_json VARCHAR,
balance_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (source_ref)
);
CREATE TABLE IF NOT EXISTS tof_policy (
company_code VARCHAR NOT NULL,
policy_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
leave_type_code VARCHAR,
applies_json VARCHAR,
unit VARCHAR,
annual_days NUMERIC(9,4),
bank_holidays VARCHAR,
accrual VARCHAR,
accrual_pct NUMERIC(9,4),
leave_year_start VARCHAR,
rounding VARCHAR,
carry_over_max NUMERIC(9,4),
carry_over_expires VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, policy_code, valid_from)
);
CREATE TABLE IF NOT EXISTS tof_request (
request_id VARCHAR NOT NULL,
person_id BIGINT,
company_code VARCHAR,
requester_email VARCHAR,
requester_sub VARCHAR,
leave_type_code VARCHAR,
policy_code VARCHAR,
leave_year INTEGER,
from_date DATE,
from_part VARCHAR,
to_date DATE,
to_part VARCHAR,
cost NUMERIC(19,4),
unit VARCHAR,
status VARCHAR,
revision INTEGER,
approver_person_id BIGINT,
approver_email VARCHAR,
approver_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
decided_at TIMESTAMP,
reason VARCHAR,
note VARCHAR,
overlap_ack VARCHAR,
stale BOOLEAN,
stale_cost NUMERIC(19,4),
cancel_asked_at TIMESTAMP,
cancel_asked_by VARCHAR,
cancel_refused_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (request_id)
);
CREATE TABLE IF NOT EXISTS tof_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 tof_sickness (
absence_id VARCHAR NOT NULL,
person_id BIGINT,
company_code VARCHAR,
first_day DATE,
expected_return DATE,
last_day_off DATE,
unit VARCHAR,
status VARCHAR,
revision INTEGER,
reason_detail VARCHAR,
self_certified BOOLEAN,
evidence_ok BOOLEAN,
recorded_by VARCHAR,
recorded_sub VARCHAR,
person_email VARCHAR,
person_sub VARCHAR,
approver_person_id BIGINT,
approver_email VARCHAR,
approver_sub VARCHAR,
confirmed_by VARCHAR,
confirmed_sub VARCHAR,
void_reason VARCHAR,
qualifying_days INTEGER,
piw BOOLEAN,
linked_to VARCHAR,
linked_group VARCHAR,
changed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (absence_id)
);
CREATE TABLE IF NOT EXISTS tof_evidence (
object_key VARCHAR NOT NULL,
absence_ref VARCHAR,
kind VARCHAR,
covers_from DATE,
covers_to DATE,
sha256 VARCHAR,
content_type VARCHAR,
size_bytes INTEGER,
uploaded_by VARCHAR,
uploaded_sub VARCHAR,
uploaded_at TIMESTAMP,
retain_until DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (object_key)
);
CREATE TABLE IF NOT EXISTS tof_closure_day (
company_code VARCHAR NOT NULL,
closure_date DATE NOT NULL,
name VARCHAR,
leave_type_code VARCHAR,
published_by VARCHAR,
published_sub VARCHAR,
published_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, closure_date)
);
CREATE TABLE IF NOT EXISTS tof_adjustment (
adjustment_id VARCHAR NOT NULL,
person_id BIGINT,
company_code VARCHAR,
leave_type_code VARCHAR,
leave_year INTEGER,
quantity NUMERIC(19,4),
unit VARCHAR,
on_date DATE,
reason VARCHAR,
proposed_by VARCHAR,
proposed_sub VARCHAR,
status VARCHAR,
decided_by VARCHAR,
decided_sub VARCHAR,
decided_at TIMESTAMP,
decision_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (adjustment_id)
);
CREATE TABLE IF NOT EXISTS tof_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 tof_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 tof_family_leave (
request_id VARCHAR NOT NULL,
person_id BIGINT,
company_code VARCHAR,
leave_type_code VARCHAR,
ewc DATE,
evidence_received_on DATE,
weeks INTEGER,
recorded_by VARCHAR,
recorded_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (request_id)
);