The database

The complete data layer: the entity relationship diagram, then every table column by column, with who writes it, who reads it, and why it is shaped the way it is. One SQLite file in WAL mode; schema created idempotently at boot; every query a prepared statement (src/db.ts).

The ERD

The full schema in crow's-foot notation. A caller places calls, calls record transcripts and produce facts, and one-row-per-number tables (consent, outreach) hang directly off the person.

Two spines organize everything: a phone number is a person, a call SID is one conversation. Relationships are correlational by convention rather than enforced FOREIGN KEYs, a deliberate SQLite simplicity choice: the write paths are few and all owned by this codebase, so the constraint machinery would buy little and cost migration flexibility.

callers

Column	Type	Meaning
`phone_number`	TEXT PK	E.164 number; the identity spine for a person.
`first_seen_at`	TEXT	First contact on any channel; defaults to now.

Written by the first call or text from a number. Read by the abuse policy (as the root for call counting) and anything joining person-level data.

calls

Column	Type	Meaning
`call_sid`	TEXT PK	Twilio's id for the call; the conversation spine.
`phone_number`	TEXT	Who called.
`started_at` / `ended_at`	TEXT	Boundaries; `ended_at` NULL while live.

Written by the bridge at call start and end. Read by /admin/calls, and by callerPolicy.ts counting a number's calls inside the abuse window, which is why this table exists rather than relying on Twilio's console: the abuse decision must be answerable locally, before the paid pipeline starts.

facts

Column	Type	Meaning
`id`	INTEGER PK	Autoincrement.
`phone_number`	TEXT · indexed	Whose memory this is.
`category`	TEXT	Short label: name, company, role, reason_for_calling, sms_consent.
`fact`	TEXT	A standalone sentence.
`call_sid`	TEXT · nullable	Which conversation produced it, when known.
`created_at`	TEXT	When learned.

Written by the save_user_fact tool on every channel. Read by personaContext() and smsContext(), always injected with the framing that these are notes about a number, never the current speaker's identity (the rule that came from the shared-phone naming bug). The sms_consent category doubles as a trigger: saving it fires the confirmation text.

transcripts

Column	Type	Meaning
`id`	INTEGER PK	Autoincrement; insertion order is turn order.
`call_sid`	TEXT · indexed	Which call.
`role`	TEXT	`caller` or `assistant`.
`content`	TEXT	One utterance or reply.
`created_at`	TEXT	Timestamp.

Written by the bridge's transcript handlers as each side speaks. Read by /admin/calls (the expandable transcripts) and the recording sidecar. A regulated deployment adds retention rules here first (see compliance).

bookings

Column	Type	Meaning
`id`	INTEGER PK	Autoincrement.
`phone_number` / `call_sid`	TEXT · nullable	Where the request came from; web bookings may lack both.
`name` / `email`	TEXT	From THIS conversation, never from stored notes.
`topic`	TEXT · nullable	What the intro call is about.
`start_at`	TEXT	ISO 8601 with offset, validated on the way in.
`status`	TEXT	`pending` until the owner confirms.
`event_id` / `event_link`	TEXT · nullable	The tentative Google Calendar hold, when a calendar is configured. (`event_id` arrived by migration; see below.)

Written by book_intro_call. Read by the admin views and the SMS agent's booking tools (check, reschedule, cancel), which is what lets a booking made on a call be managed later by text.

outreach

Column	Type	Meaning
`phone_number`	TEXT PK	One live outreach thread per number, by design.
`name` / `topic` / `message`	TEXT	Who the owner asked Elle to contact, about what, and the text sent.
`status`	TEXT	`sent` → `replied` when they text back.

Written by the owner intake channel. Read by the SMS agent (context injection when the contact replies) and the intake channel's status tool. The PK-per-number shape is the feature: a reply is recognized by a single lookup.

sms_consent

Column	Type	Meaning
`phone_number`	TEXT PK	One consent state per number.
`status`	TEXT	Current consent state.
`source`	TEXT	How consent was given (e.g. verbal on a call).
`updated_at`	TEXT	Last change.

The A2P audit ledger: paired with the confirmation text, it answers "who agreed to receive texts, how, and when." Inbound STOP is enforced by Twilio upstream, so the app cannot forget to honor it.

settings

Column	Type	Meaning
`key`	TEXT PK	Setting name, e.g. `voice_provider`.
`value`	TEXT	Current value.
`updated_at`	TEXT	Last change.

The runtime KV store behind the architecture switch: the owner's voice-provider override lives here so it survives restarts without a deploy.

Indexes, WAL, and migrations

Indexes exist exactly where queries loop: facts(phone_number), transcripts(call_sid), bookings(phone_number).
WAL mode lets the live call path write transcripts while admin reads run, without writer/reader blocking.
Migrations are additive and inline: at boot, a PRAGMA table_info check adds bookings.event_id to databases created before it existed. The pattern scales to any additive change without a migration framework.
Durability trade: the file rides the Cloud Run instance, persisting across calls but not revision replacements. The documented upgrade is Cloud SQL or Firestore when that trade stops being acceptable (and a regulated deployment makes it mandatory).