05 · Persistence Layer

Fifth installment of the architecture deep-dive: SQLite WAL + file-tree layered storage, migration system, and path self-healing mechanism. Corresponds to ARCHITECTURE.md §6.

---

§6.1 Why SQLite

Dimension	Choice	Rationale
Data scale	Single user, thousands to tens of thousands of rows	No need for Postgres / MySQL concurrency power
Operations	Zero ops	Single file, no server process
Transactions	WAL mode	Multi-process safe (serialized writer)
Privacy	Local file	Data never leaves the machine
Concurrency	check_same_thread=True + thread-local	Independent connection per thread, no lock contention

§6.2 Data Layering

SQLite (workspace/familiars.db)

Structured data:

Table	Purpose
agents	Agent metadata
dialogues	Dialogue tree index
cron_jobs	Cron jobs
schedules	Schedules
todos	To-do items
reminders	Reminders
watch_jobs	Monitor jobs
skills	Skill manifests
approvals	Permissions / approvals
rag_feedback	RAG feedback
proactive_feedback	Proactive message feedback
broker_*	Broker metadata (v18+)
guild_*	Guild (v20+)
flow_*	Flow instances (v25+)

File Tree (workspace/)

workspace/
├── familiars.db                    # SQLite main database
├── .bridge_token                   # Bridge auth token
├── .bridge.port                    # Bridge actual port
├── .bridge.pid                     # Daemon pidfile
├── .bridge.log                     # Daemon log (rotating)
├── .bridge.crt                     # Self-signed HTTPS certificate
├── .bridge.key                     # Certificate private key
├── .salt                           # Encryption salt (crypto.py)
├── .paths_aliases.json             # External directory aliases
├── SECRET.json                     # Encrypted credentials
├── gpu_tier.json                   # GPU tier cache
├── info.json                       # OS info
├── USER.md                         # User profile (RAG target)
├── IDENTITY.md                     # User identity
├── AGENTS.md                       # Project-level agent rules
├── tools.json                      # Tool schema merge source
├── user/                           # User-scope memory
│   ├── profile.md
│   ├── preference.md
│   ├── relation.md
│   ├── knowledge.md
│   └── event.md
├── agents/
│   └── agent_<id>/
│       ├── IDENTITY.md             # Agent identity
│       ├── dialogue_<n>/
│       │   ├── dialogue.json       # Dialogue tree (Node list)
│       │   ├── turn_<k>/
│       │   │   └── plan.json       # Tool plan
│       │   ├── step_<k>.json       # Step record
│       │   ├── dialogue/           # Dialogue-scope memory
│       │   │   ├── preference.md
│       │   │   ├── relation.md
│       │   │   ├── knowledge.md
│       │   │   ├── event.md
│       │   │   ├── state.md
│       │   │   └── directive.md
│       │   └── ephemeral/          # Ephemeral-scope memory
│       │       ├── state.md
│       │       └── directive.md
│       ├── identity/               # Identity-scope memory
│       │   ├── preference.md
│       │   ├── procedure.md
│       │   └── directive.md
│       ├── voice.wav               # Voice sample (digital human)
│       ├── client_portraits/       # Client portraits
│       └── reports/                # Reports (deprecated → manager_review skill)
├── manager/                        # Agent 0 (manager) data
├── reminders/
│   └── inbox.jsonl                 # Reminder events (SSE stream source)
├── rewriter/
│   └── prompt.md                   # Rewrite prompt
├── reports/                        # Report templates + system prompts
├── skills/                         # Skill definitions
├── tool_skills/                    # Tool skills
├── external_kb/                    # External knowledge bases
├── manager/brokers/                # Installed broker apps
│   └── <broker_id>/
│       ├── manifest.json
│       ├── cover.png
│       └── ...
└── manager/flows/                  # Committed flow templates
    └── <template_id>/
        ├── template.json
        ├── layout.json
        ├── renderers/              # Custom field renderer .vue files
        └── docs/                   # Design / development docs

§6.3 SQLite Connection Management

6.3.1 connect() Factory

# src/storage/db.py

def connect(db_path: Optional[str] = None, *, run_migrations: bool = True) -> sqlite3.Connection:
    """Return a configured SQLite connection. Cached per-thread per-path."""
    path = db_path or get_default_db_path()
    cache: dict = getattr(_thread_local, "conns", None) or {}
    if path in cache:
        return cache[path]

    conn = sqlite3.connect(
        path,
        isolation_level=None,     # autocommit; manual BEGIN IMMEDIATE
        timeout=30.0,             # wait up to 30s for writer lock
        check_same_thread=True,   # independent connection per thread
    )
    conn.row_factory = sqlite3.Row
    _configure(conn)
    if run_migrations:
        _run_migrations(conn)

    cache[path] = conn
    _thread_local.conns = cache
    return conn

6.3.2 PRAGMA Configuration

def _configure(conn: sqlite3.Connection) -> None:
    cur = conn.cursor()
    cur.execute("PRAGMA journal_mode = WAL")        # Write-Ahead Logging
    cur.execute("PRAGMA synchronous = NORMAL")       # Balance performance / safety
    cur.execute("PRAGMA foreign_keys = ON")          # Foreign key constraints
    cur.execute("PRAGMA busy_timeout = 5000")        # Busy wait 5s
    cur.execute("PRAGMA temp_store = MEMORY")        # Temp tables in memory

6.3.3 DB Path Resolution

def get_default_db_path() -> str:
    explicit = os.getenv("FAMILIARS_DB_PATH")
    if explicit:
        return explicit
    workspace = os.getenv("WORKSPACE_ROUTE") or os.getcwd()
    return str(Path(workspace) / "workspace" / "familiars.db")

FAMILIARS_DB_PATH=:memory: is commonly used for tests.

§6.4 Migration System

6.4.1 Migration Runner

# src/storage/db.py
_MIGRATIONS_DIR = Path(__file__).parent / "migrations"
_MIGRATION_NAME_RE = re.compile(r"^v(\d+)__.+\.(sql|py)$")

def _run_migrations(conn: sqlite3.Connection) -> None:
    """Execute pending migrations in version order."""

6.4.2 Migration Manifest (v1–v50)

Version	File	Type	Description
v1	v1__init.sql	SQL	Initial schema
v2	v2__cron_jobs.sql	SQL	Cron jobs table
v3	v3__rag_feedback.sql	SQL	RAG feedback
v4	v4__memory_category.sql	SQL	Memory categories
v5	v5__schedules.sql	SQL	Schedules table
v6	v6__todos.sql	SQL	To-do table
v7	v7__cron_runs.sql	SQL	Cron run history
v8	v8__schedules_status.sql	SQL	Schedule status
v9	v9__reminders.sql	SQL	Reminders table
v10	v10__proactive_feedback.sql	SQL	Proactive message feedback
v11	v11__node_origin.sql	SQL	Node origin field
v12	v12__watch_jobs.sql	SQL	Monitor jobs
v13	v13__recurrence.sql	SQL	Recurring schedules
v14	v14__watch_jobs_metadata.sql	SQL	Monitor job metadata
v15	v15__agent_turn_counter.sql	SQL	Agent turn counter
v16	v16__schedule_dedup_and_unique.py	Python	Schedule dedup + unique constraints
v17	v17__cron_last_fired_and_feedback.sql	SQL	Cron last-fired + feedback
v18	v18__broker.sql	SQL	Broker layer
v19	v19__broker_workshop.sql	SQL	Broker workshop
v20	v20__broker_local_package_dir.py	Python	Broker local package directory
v20	v20__guild.sql	SQL	Guild system
v21	v21__broker_identity_assets.sql	SQL	Broker identity assets
v21	v21__guild_task_type.sql	SQL	Guild task types
v22	v22__broker_messages.sql	SQL	Broker messages
v23	v23__broker_member_caps.sql	SQL	Broker member capabilities
v24	v24__broker_approvals.sql	SQL	Broker approvals
v25	v25__flow_core.py	Python	Flow core tables
v26	v26__flow_trace_index.sql	SQL	Flow trace index
v27	v27__guild_medal_images.sql	SQL	Guild medal images
v28	v28__broker_author_crl.sql	SQL	Broker author CRL (certificate revocation)
v29	v29__broker_trusted_authors.sql	SQL	Broker trusted authors
v30	v30__broker_room_federation.sql	SQL	Broker room federation
v31	v31__broker_thread_federation.sql	SQL	Broker thread federation
v32	v32__broker_message_federation.sql	SQL	Broker message federation
v33	v33__guild_task_federation.sql	SQL	Guild task federation
v34	v34__guild_medal_federation.sql	SQL	Guild medal federation
v35	v35__guild_ledger_federation.sql	SQL	Guild ledger federation
v36	v36__broker_user_consents.sql	SQL	Broker user consents
v37	v37__guild_scope_approvals.sql	SQL	Guild scope approvals
v38	v38__guild_task_lifecycle.sql	SQL	Guild task lifecycle
v39	v39__guild_merge_requests.sql	SQL	Guild merge requests
v40	v40__task_edit_history.sql	SQL	Task edit history
v41	v41__guild_modification_requests.sql	SQL	Guild modification requests
v41	v41__message_reactions.py	Python	Message reactions
v42	v42__guild_experience_reports.sql	SQL	Guild experience reports
v42	v42__guild_modification_votes.sql	SQL	Guild modification votes
v43	v43__guild_task_recall_votes.sql	SQL	Guild task recall votes
v44	v44__guild_scope_members.sql	SQL	Guild scope members
v45	v45__task_message_channels.sql	SQL	Task message channels
v46	v46__guild_task_tags.sql	SQL	Guild task tags
v47	v47__demo_accounts.sql	SQL	Demo accounts
v48	v48__flow_pause.sql	SQL	Flow pause
v49	v49__user_auth.sql	SQL	User authentication
v50	v50__broker_role_requests.sql	SQL	Broker role requests

Python migrations: v16, v20, v25, v41 are .py files — they allow complex data transformations (deduplication, index rebuilds, etc.). The runner extracts the version number from the v(\d+)__ prefix; if both .sql and .py files exist with the same name, .py takes priority.

§6.5 Path Self-Healing

6.5.1 The Problem

Absolute paths stored in dialogue trees become stale in these scenarios:

1. Repo root drift — user moves the repo folder
2. External directory aliases — user renames a data folder

If DialogueManager._load_existing_dialogues runs before path repair, the LLM's next turn reads stale paths as context, causing infinite ls loops or writing files to ghost locations.

6.5.2 The Solution

src/bridge/app.py calls align_all_paths() at import time, before route mounting:

# src/base/paths_align.py
def align_all_paths() -> dict:
    """Handle two kinds of path drift.
    
    Returns {"files_touched": int, "total_hits": int, "aliases_applied": [...]}
    """

Mechanism 1: Repo root drift (auto-detected)

Compares each dialogue.path prefix against the current _repo_root():

• Mismatch → automatically rewrites dialogue.json + turn_*/plan.json + step_*.json
• Recorded in workspace/.paths_aliases.json

Mechanism 2: External directory aliases (manually registered)

python -m src.tools.paths_migrate <old> <new> --record

• Run --dry-run first to preview
• --record writes to .paths_aliases.json
• Replayed on every startup
• Each rewritten file is backed up as <path>.bak_<ts>

Coverage:

• dialogue.json (node content + metadata.tool_calls args / results + attachments)
• turn_*/plan.json + step_*.json

Never rewritten:

• .bridge.log, audit.log — preserved as the authentic historical record

Idempotent: 0-hit files are skipped; stable boots have zero overhead.

§6.6 Encrypted Storage

6.6.1 SECRET.json

Credentials are encrypted and stored in workspace/SECRET.json. The encryption chain:

hostname + username + salt_bytes
    → HKDF (SHA-256, info="familiars-secrets-v1")
    → 32-byte Fernet key
    → AES-128-CBC + HMAC-SHA256

# src/base/crypto.py
_SALT_FILENAME = ".salt"
_KEY_INFO = b"familiars-secrets-v1"

def _derive_fernet_key() -> bytes:
    salt = _load_or_create_salt()   # os.urandom(32)
    identity = f"{platform.node()}|{os.getenv('USER', ...)}"
    hkdf = HKDF(algorithm=hashes.SHA256(), length=32, salt=salt, info=_KEY_INFO)
    raw_key = hkdf.derive(identity.encode("utf-8"))
    return base64.urlsafe_b64encode(raw_key)

Security model:

• Salt is randomly generated, not shared → without machine identity + salt, the key cannot be reconstructed
• The salt file alone is useless without machine identity
• Machine identity alone is useless without salt
• The HKDF info string "familiars-secrets-v1" is a versioned crypto primitive → changing it invalidates all existing stored secrets

---

Next: 06 - Proactive Decision & Gifts →