Query

event_store.db — facts

SQL

CREATE TABLE facts  (
  id TEXT PRIMARY KEY,
  entity_id TEXT NOT NULL,
  field TEXT NOT NULL,
  value TEXT NOT NULL,
  as_of TEXT NOT NULL,
  derived_from_event_ids TEXT,
  -- optional JSON array; normalized in fact_sources
  confidence REAL DEFAULT 0.0,
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
entity_id TEXT Rename | Drop
field TEXT Rename | Drop
value TEXT Rename | Drop
as_of TEXT Rename | Drop
derived_from_event_ids TEXT ✓ Rename | Drop
confidence REAL ✓ Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
entity_id entities.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_facts_entity_field_unique
  • entity_id
  • field
 ✓ SQL 
CREATE UNIQUE INDEX idx_facts_entity_field_unique
ON facts (entity_id, field)
Drop
idx_facts_field_asof
  • field
  • as_of
 SQL 
CREATE INDEX idx_facts_field_asof
ON facts (field, as_of)
Drop
sqlite_autoindex_facts_1 id ✓ SQL
-- no sql found --
 Drop