19 lines
902 B
SQL
19 lines
902 B
SQL
-- Phase 5: Application tracker — event log + indexes for state-machine queries.
|
|
-- Existing applications table already has the status field with the right values.
|
|
-- This migration adds an immutable event log for audit + follow-up cadence support.
|
|
|
|
CREATE TABLE IF NOT EXISTS tracker_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
application_id INTEGER NOT NULL,
|
|
from_status TEXT,
|
|
to_status TEXT NOT NULL,
|
|
note TEXT,
|
|
occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (application_id) REFERENCES applications(id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tracker_events_application ON tracker_events(application_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tracker_events_occurred ON tracker_events(occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_applications_applied_at ON applications(applied_at);
|
|
CREATE INDEX IF NOT EXISTS idx_follow_ups_at ON follow_ups(follow_up_at);
|