Database Schema

The D1 database uses 10 tables. The migration is at src/db/migrations/0001_init.sql.

Core Tables

entities

Every entity in the system.

Column	Type	Notes
id	TEXT PK	lat_<prefix>_<nanoid>
type	TEXT NOT NULL	CHECK constraint on 7 valid types
parent_id	TEXT	FK to entities(id), nullable for root
name	TEXT NOT NULL	Display name
status	TEXT NOT NULL	Default 'active'
tags	TEXT	JSON array, default '[]'
extension	TEXT	JSON, type-specific data, default '{}'
telos_problems	TEXT	JSON array, default '[]'
telos_mission	TEXT	Nullable
version	INTEGER NOT NULL	Default 1, incremented on every update
created_at	TEXT NOT NULL	ISO 8601
updated_at	TEXT NOT NULL	ISO 8601

Indexes: type, parent_id, status.

events

Immutable event log.

Column	Type	Notes
id	TEXT PK	evt_<nanoid>
type	TEXT NOT NULL	Event type string
entity_id	TEXT NOT NULL	FK to entities(id) CASCADE
timestamp	TEXT NOT NULL	ISO 8601
data	TEXT	JSON, default '{}'
seq	INTEGER NOT NULL	Monotonic sequence number

Indexes: entity_id, seq, type.

event_seq

Singleton row for atomic sequence generation.

Column	Type	Notes
id	INTEGER PK	CHECK(id = 1), always exactly one row
current_seq	INTEGER NOT NULL	Default 0

policies

Access control rules.

Column	Type	Notes
id	TEXT PK	Policy identifier
subject	TEXT NOT NULL	Entity ID or role pattern
action	TEXT NOT NULL	read, write, execute
object	TEXT NOT NULL	Entity ID or * wildcard
condition	TEXT	JSON condition object, default '{}'
effect	TEXT NOT NULL	allow or deny
created_at	TEXT NOT NULL	ISO 8601

Indexes: subject, object.

subscriptions

Event subscription registrations (Phase 1: poll-only).

Column	Type	Notes
id	TEXT PK	Subscription identifier
subscriber_id	TEXT NOT NULL	Who is subscribing
source_entity_id	TEXT NOT NULL	FK to entities(id) CASCADE
event_filter	TEXT	JSON filter config, default '{}'
delivery_method	TEXT NOT NULL	Default 'poll'
created_at	TEXT NOT NULL	ISO 8601

Indexes: source_entity_id, subscriber_id.

Telos Tables

telos_goals

Queryable goal records with status, target dates, and goal hierarchy.

telos_challenges

Obstacles with severity levels and cross-references to goals.

telos_strategies

Strategic approaches linking challenges to goals.

telos_metrics

Quantitative measurements with direction and targets.

telos_work_items

Actionable tasks with assignees, priority, and Kanban status.

telos_budget

Financial allocations and spend tracking per category and period.

telos_team_members

Join table mapping entities to their team members with roles. Composite PK on (entity_id, member_entity_id).

telos_json

Flexible JSON storage for telos fields that do not need SQL querying. Composite PK on (entity_id, field). Valid fields: narratives, projects, journal, ideas, beliefs, skills, documentation, sops, logs.

All telos tables have entity_id foreign keys with ON DELETE CASCADE and indexes on entity_id.