# SQLite schema

All identifiers are stable strings. Every primary collection has a `record_json` column preserving the full JSON object. `source_id`/`target_id` in relationships mean the two subjects; evidence is joined through `relationship_sources` to `sources`.

```sql
CREATE TABLE metadata(key TEXT PRIMARY KEY,value TEXT NOT NULL);
CREATE TABLE entities(id TEXT PRIMARY KEY,name TEXT NOT NULL,role TEXT,area TEXT,intro TEXT,why TEXT,record_json TEXT NOT NULL);
CREATE TABLE relationships(id TEXT PRIMARY KEY,source_id TEXT NOT NULL REFERENCES entities(id),target_id TEXT NOT NULL REFERENCES entities(id),label TEXT,detail TEXT,type TEXT,status TEXT,date TEXT,record_json TEXT NOT NULL);
CREATE TABLE sources(id TEXT PRIMARY KEY,title TEXT,url TEXT,record_json TEXT NOT NULL);
CREATE TABLE notes(id TEXT PRIMARY KEY,title TEXT,description TEXT,markdown TEXT,file TEXT,record_json TEXT NOT NULL);
CREATE TABLE stories(id TEXT PRIMARY KEY,title TEXT,deck TEXT,record_json TEXT NOT NULL);
CREATE TABLE maps(id TEXT PRIMARY KEY,title TEXT,description TEXT,topic TEXT,record_json TEXT NOT NULL);
CREATE TABLE questions(id TEXT PRIMARY KEY,question TEXT,status TEXT,answer TEXT,record_json TEXT NOT NULL);
CREATE TABLE hypotheses(id TEXT PRIMARY KEY,title TEXT,status TEXT,statement TEXT,record_json TEXT NOT NULL);
CREATE TABLE decision_models(id TEXT PRIMARY KEY,title TEXT,summary TEXT,record_json TEXT NOT NULL);
CREATE TABLE entity_aliases(alias_id TEXT PRIMARY KEY,entity_id TEXT REFERENCES entities(id));
CREATE TABLE entity_notes(entity_id TEXT REFERENCES entities(id),note_id TEXT REFERENCES notes(id),PRIMARY KEY(entity_id,note_id));
CREATE TABLE relationship_sources(relationship_id TEXT REFERENCES relationships(id),source_id TEXT REFERENCES sources(id),PRIMARY KEY(relationship_id,source_id));
CREATE TABLE map_entities(map_id TEXT REFERENCES maps(id),entity_id TEXT REFERENCES entities(id),PRIMARY KEY(map_id,entity_id));
CREATE TABLE map_relationships(map_id TEXT REFERENCES maps(id),relationship_id TEXT REFERENCES relationships(id),PRIMARY KEY(map_id,relationship_id));
CREATE TABLE citations(id INTEGER PRIMARY KEY,owner_type TEXT,owner_id TEXT,title TEXT,url TEXT,source_id TEXT REFERENCES sources(id));
CREATE INDEX relationship_from ON relationships(source_id);
CREATE INDEX relationship_to ON relationships(target_id);
CREATE INDEX citation_owner ON citations(owner_type,owner_id);
CREATE VIRTUAL TABLE search USING fts5(kind UNINDEXED,id UNINDEXED,title,body,tokenize='unicode61');
```

For JSON field details, inspect a full record in database.json. In particular, `notes.markdown` is complete local text, `maps.nodes[].pos` is the original display position, and `relationships.facts` contains financial and qualification details.