Query

pilot.db — audit_log

SQL

CREATE TABLE audit_log  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ts TEXT NOT NULL DEFAULT (datetime('now')),
  actor_kind TEXT,
  -- 'trainer' | 'student' | 'system'
  actor_id INTEGER,
  action TEXT NOT NULL,
  -- 'booking_created',
  'card_purchased',
  etc.
  entity_type TEXT,
  entity_id INTEGER,
  meta TEXT                               -- JSON-Stringified
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
ts TEXT Rename | Drop
actor_kind TEXT ✓ Rename | Drop
actor_id INTEGER ✓ Rename | Drop
action TEXT Rename | Drop
entity_type TEXT ✓ Rename | Drop
entity_id INTEGER ✓ Rename | Drop
meta TEXT ✓ Rename | Drop

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_audit_actor
  • actor_kind
  • actor_id
 SQL 
CREATE INDEX idx_audit_actor
ON audit_log(actor_kind, actor_id)
Drop
idx_audit_ts ts SQL 
CREATE INDEX idx_audit_ts
ON audit_log(ts)
Drop