Query

cards.db — audit_log

SQL

CREATE TABLE audit_log  (
  id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  entity_data TEXT,
  performed_by TEXT,
  performed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  organisation_id TEXT,
  FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
event_type TEXT Rename | Drop
entity_type TEXT Rename | Drop
entity_id TEXT Rename | Drop
entity_data TEXT ✓ Rename | Drop
performed_by TEXT ✓ Rename | Drop
performed_at DATETIME ✓ Rename | Drop
organisation_id TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
organisation_id organisations.id
performed_by users.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_audit_log_entity
  • entity_type
  • entity_id
 SQL 
CREATE INDEX idx_audit_log_entity
ON audit_log(entity_type, entity_id)
Drop
idx_audit_log_event_type event_type SQL 
CREATE INDEX idx_audit_log_event_type
ON audit_log(event_type)
Drop
idx_audit_log_organisation organisation_id SQL 
CREATE INDEX idx_audit_log_organisation
ON audit_log(organisation_id)
Drop
idx_audit_log_performed_at performed_at SQL 
CREATE INDEX idx_audit_log_performed_at
ON audit_log(performed_at)
Drop
sqlite_autoindex_audit_log_1 id ✓ SQL
-- no sql found --
 Drop