Database Schema — V3 vs V4

-- workers table
CREATE TABLE workers (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(200),
  phone VARCHAR(20),
  id_number VARCHAR(12),
  status VARCHAR(20),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
  -- NO tenant_id
  -- NO deleted_at
  -- NO RLS
);

-- Query from app layer
SELECT * FROM workers
WHERE status = 'active';

-- workers table V4
CREATE TABLE workers (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  worker_account_id BIGINT,
  full_name VARCHAR(200),
  phone VARCHAR(20),
  id_number VARCHAR(12),
  status VARCHAR(20),
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE workers ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON workers
  USING (tenant_id = current_setting('app.current_tenant_id')::BIGINT);

-- Risk: precision loss on VND hourly
CREATE TABLE payslips (
  gross_pay DECIMAL(15,2),
  net_pay DECIMAL(15,2),
  ...
);

-- Float used elsewhere (BUG prone)
CREATE TABLE commission_formulas (
  rate FLOAT  -- DANGEROUS
);

-- Financial-grade precision
CREATE TABLE payslips (
  gross_pay NUMERIC(19,4),
  net_pay NUMERIC(19,4),
  ...
);

-- All rate columns also NUMERIC
CREATE TABLE commission_rules (
  amount NUMERIC(19,4) NOT NULL,
  percentage NUMERIC(5,3)  -- safe
);

-- Invariant enforced by CHECK
ALTER TABLE payslips
ADD CONSTRAINT chk_net_le_gross
CHECK (net_pay <= gross_pay);

-- 15+ morphmap types tight coupling
CREATE TABLE activity_log (
  subject_type VARCHAR(255),
  subject_id BIGINT,
  ...
);

-- morphmap in AppServiceProvider
Relation::morphMap([
  'worker' => Worker::class,
  'job' => JobPosting::class,
  'application' => Application::class,
  ...15+ entries
]);

-- Hard to split to services later

-- Generic via audit_logs + entity_versions
CREATE TABLE audit_logs (
  tenant_id BIGINT,
  entity_type VARCHAR(50),
  entity_id BIGINT,
  action VARCHAR(20),
  old_values JSONB,
  new_values JSONB,
  ...
);

-- entity_type is plain string, no morphmap
-- Services define their own entity types
-- Easier to split to microservices later

-- 5M rows/month, no partition
-- Full-table scan risk
CREATE TABLE attendance_records (
  id BIGINT PRIMARY KEY,
  worker_id BIGINT,
  work_date DATE,
  ...
);

CREATE INDEX idx_attendance_date
  ON attendance_records(work_date);

-- Eventually degrades without partition

-- PostgreSQL 16 native
CREATE TABLE attendance_records (
  id BIGSERIAL,
  tenant_id BIGINT NOT NULL,
  worker_id BIGINT NOT NULL,
  work_date DATE NOT NULL,
  ...
) PARTITION BY RANGE (work_date);

CREATE TABLE attendance_2026_04
  PARTITION OF attendance_records
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Query planner auto-prunes partitions
-- Easy archival (drop old partitions)

-- Partial audit trail
activity_log table:
  - description (text)
  - subject (polymorphic)
  - causer (polymorphic)
  - properties (JSON)

-- Missing:
- Full entity snapshots
- Version numbers
- Policy snapshots at decision time
- Replay capability

-- 3 tables complete coverage
audit_logs         -- what changed
entity_versions    -- full snapshot
domain_events      -- outbox pattern
policy_snapshots   -- (in M10)

-- Critical for V4 financial:
SELECT snapshot FROM entity_versions
WHERE entity_type = 'payslips'
  AND entity_id = 42
  AND version_number = 3;
-- Replay any entity state at any point