Choosing the SQLite Format for the CDP Knowledge Brain

Executive Summary

The best first implementation for this repository is an ordinary upstream SQLite 3 database stored as .cdp-index/knowledge.sqlite, with one database for the whole repository and a project_id on every project-owned record. SQLite 3 uses a stable, cross-platform, single-file format, which makes it well suited to a local knowledge index and application file. SQLite: Single-file Cross-platform Database SQLite As An Application File Format

The schema should use normalized relational tables for stable concepts, STRICT tables for type enforcement, JSON stored as TEXT for provider-specific fields, and FTS5 for full-text search. SQLite supports per-table strict typing, built-in JSON functions in current releases, and FTS5 virtual tables for search. SQLite STRICT Tables SQLite JSON Functions and Operators SQLite FTS5 Extension

The live .sqlite file should not be the canonical Git artifact. It is a binary, machine-managed file, so ordinary Git diffs and merges are not useful. Git’s own documentation treats machine-managed database-like files as binary content unless a text conversion is supplied. Git Attributes The durable repository record should therefore be sanitized NDJSON snapshots under each project. The central SQLite file can be rebuilt from those snapshots and the existing Markdown documents.

For Adaptation Explorer, Typeform should become an ingestion source rather than the system queried for every read. A trusted scheduled job uses TYPEFORM_ACCESS_TOKEN, upserts responses into the knowledge store, and writes a sanitized snapshot. All later CLI, documentation, and MCP reads use persisted data and do not require the Typeform credential. Typeform’s Responses API supplies historical submissions as JSON and supports incremental retrieval with since, until, before, and after, while webhooks can deliver each new submission to an HTTPS endpoint. Typeform Responses API Typeform Retrieve Responses Typeform Webhooks API

Cloudflare D1 is the strongest second-stage option if the deployed MCP must query or update the latest records directly. D1 provides SQLite-compatible SQL through Workers and Pages bindings, supports JSON and FTS5, and adds managed recovery. Cloudflare D1 Overview Cloudflare D1 SQL Statements It is not necessary for the first local and Git-backed implementation.

What “SQLite Format” Means Here

Several independent decisions are hidden inside the phrase “SQLite format”.

Decision	Recommendation	Reason
Database engine	Upstream SQLite 3	Stable, portable and dependency-light local format
Filename	`.cdp-index/knowledge.sqlite`	Clearly identifies the file; `.db` and `.sqlite3` would not change the underlying format
Repository scope	One database for the whole CDP Index	Enables cross-project search, relations and analytics without attaching multiple databases
Project isolation	`project_id` foreign key on project-owned rows	Keeps project queries simple while preserving a repository-wide graph
Schema style	Normalized relational core plus JSON edge payloads	Stable fields remain queryable; provider-specific data is retained without creating a column for every form question
Table mode	`STRICT`	Rejects incompatible values instead of silently accepting them
Search	FTS5 external-content indexes	Supports phrase and full-text search across feedback and documentation
Local journal	WAL during normal local use	Supports concurrent readers and a writer on the same host
Durable Git representation	Sanitized NDJSON per project	Human inspectable, diffable and rebuildable
Attachments	Files on disk plus metadata and checksums in SQLite	Avoids inflating the database with screenshots and documents
Remote evolution	Cloudflare D1	Natural fit if Pages and MCP require live hosted access

The extension does not define a different SQLite variant. knowledge.sqlite, knowledge.db, and knowledge.sqlite3 can all contain the same SQLite 3 file format. .sqlite is recommended here because it communicates the format most clearly.

Repository Context

The current repository already contains the main building blocks of a knowledge system:

Existing surface	Current role	Relevant evidence
Schema-backed Markdown	Durable narrative project memory	Project frontmatter schema
Adaptation Explorer Typeform collector	Fetches, normalizes and optionally exports responses	Feedback collector
Generated feedback Markdown	Human-readable archive of rich feedback and attachments	Typeform feedback report
PostHog collector	Queries live product analytics and builds summaries	Analytics collector
Trigger.dev automation	Schedules Typeform and PostHog collection	Automation definitions
MCP tools	Expose feedback, analytics and repository knowledge	Feedback MCP tools
Kennel generation	Builds a searchable MCP documentation bundle	MCP configuration

The Typeform collector already defines a useful normalized response shape with a response ID, form ID, answers, timestamps, hidden fields, attachment URLs and a Slack-ready message. The problem is persistence and ownership: both the project collector and MCP tool contain fetching and normalization behavior, and normal reads still depend on Typeform credentials.

The generated Markdown report is valuable as a readable presentation, but it is not a good primary data store. A single response can contain multiple answers and attachments, which means filtering, updating one response, deduplicating by response ID, or relating feedback to analytics requires reparsing a large document.

Options Considered

Option 1: One committed SQLite file

This is the simplest conceptually: commit knowledge.sqlite and let every project update it.

It is not recommended. SQLite is a sound application file format, but the file is binary from Git’s perspective. Git can store binary files, but normal line diffs and text merges do not apply without custom conversion. Git Attributes Two branches updating different records can still produce a conflict on the same database file.

This option is acceptable only when there is one writer, branch-based collaboration is unimportant, and the database is treated as a release artifact rather than collaboratively authored source.

Option 2: One ignored SQLite file rebuilt from project snapshots

This is the recommended first stage.

The repository keeps sanitized, normalized records as NDJSON under the project that owns them. The cdp-index CLI imports those records into one ignored SQLite file. Narrative Markdown is indexed into the same database but remains authored Markdown.

Advantages:

Project data is readable and reviewable in Git.
The SQLite file can be deleted and rebuilt.
Queries work without provider credentials after the snapshot is present.
One database supports cross-project search and relationships.
Provider-specific raw fields can be retained locally without automatically publishing them.

The cost is a build or hydration step after cloning or pulling new snapshots. The CLI should make that automatic.

Option 3: Cloudflare D1 as the primary store

D1 is Cloudflare’s managed serverless database with SQLite SQL semantics, Worker and HTTP API access, and point-in-time recovery. Cloudflare D1 Overview Pages Functions can access D1 through an environment binding. Cloudflare Pages D1 Bindings

This is attractive because CDP Index already deploys its documentation and MCP through Cloudflare. It would allow a Typeform webhook or scheduled worker to write feedback and let the MCP read it immediately.

It is not the best first step because it changes the project from a self-contained knowledge repository into a hosted database application. Local querying, backups, access control, data export and offline work become dependent on another managed surface. D1 should be introduced when freshness or remote multi-writer access becomes an actual requirement.

Option 4: libSQL or Turso

libSQL is a production-oriented fork of SQLite that retains the SQLite file format and API while adding features such as replication and vector support. Turso libSQL Documentation Turso’s embedded replicas can maintain a local file synchronized with a remote primary, although the documentation now describes embedded replicas as a legacy feature and recommends newer Turso Sync for new synchronization use cases. Turso Embedded Replicas

This solves a more complex problem than the repository currently has. It becomes useful if several machines must work offline, write locally, and synchronize changes. It is not necessary for a scheduled Typeform importer plus predominantly read-heavy project knowledge.

Recommended Architecture

flowchart LR
  TF["Typeform"] -->|"credentialed ingestion"| ING["cdp-index ingest"]
  PH["PostHog"] -->|"credentialed ingestion"| ING
  MD["Project Markdown"] --> BUILD["cdp-index build"]
  ING --> RAW["Local raw source records"]
  ING --> SNAP["Sanitized project NDJSON"]
  SNAP --> BUILD
  RAW --> DB[".cdp-index/knowledge.sqlite"]
  BUILD --> DB
  DB --> CLI["Project-aware CLI"]
  DB --> DOCS["Generated reports"]
  DB --> EXPORT["Sanitized MCP export"]
  EXPORT --> MCP["Cloudflare MCP"]

Filesystem Layout

cdp-index/
├── .cdp-index/
│   ├── knowledge.sqlite
│   ├── knowledge.sqlite-wal
│   └── knowledge.sqlite-shm
├── knowledge/
│   ├── migrations/
│   │   ├── 001-core.sql
│   │   ├── 002-feedback.sql
│   │   ├── 003-analytics.sql
│   │   └── 004-search.sql
│   └── schemas/
├── src/knowledge/
│   ├── cli.ts
│   ├── database.ts
│   ├── project-context.ts
│   ├── migrations.ts
│   ├── ingest/
│   │   ├── typeform.ts
│   │   └── posthog.ts
│   └── repositories/
│       ├── feedback.ts
│       ├── analytics.ts
│       └── documents.ts
└── projects/
    └── adapt-action-explorer/
        ├── knowledge.json
        ├── data/
        │   ├── feedback/responses.ndjson
        │   └── analytics/observations.ndjson
        └── assets/typeform-feedback/

.cdp-index/ should be ignored by Git. The -wal and -shm files are normal WAL-mode companion files and must never be treated as independent backups. SQLite documents that WAL state can involve the main database, WAL and shared-memory files; WAL is designed for processes on the same host and does not work across a network filesystem. SQLite Database File Format SQLite Write-Ahead Logging

For a portable database snapshot, the tool should create a clean copy with the SQLite backup API or VACUUM INTO, rather than copying a live database file casually. SQLite documents both mechanisms for producing a consistent database copy. SQLite Backup API SQLite VACUUM

Schema Design

Common relational core

CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
) STRICT;
 
CREATE TABLE sources (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id),
  provider TEXT NOT NULL,
  external_id TEXT,
  record_type TEXT NOT NULL,
  visibility TEXT NOT NULL CHECK (
    visibility IN ('public', 'internal', 'private', 'restricted')
  ),
  config_json TEXT NOT NULL DEFAULT '{}'
    CHECK (json_valid(config_json)),
  UNIQUE (project_id, provider, external_id)
) STRICT;
 
CREATE TABLE knowledge_items (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id),
  source_id TEXT REFERENCES sources(id),
  kind TEXT NOT NULL,
  external_id TEXT,
  title TEXT,
  body TEXT,
  occurred_at TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  visibility TEXT NOT NULL,
  redaction_state TEXT NOT NULL DEFAULT 'unreviewed',
  payload_json TEXT NOT NULL DEFAULT '{}'
    CHECK (json_valid(payload_json)),
  content_hash TEXT NOT NULL,
  ingested_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE (source_id, external_id)
) STRICT;

The relational columns hold fields needed for constraints, joins, sorting and filtering. payload_json preserves irregular provider data that is useful for audit or future normalization. SQLite stores JSON as text and provides functions such as json_extract, json_valid, json_each and json_tree for querying it. SQLite JSON Functions and Operators

Do not put everything into one JSON document. Ratings, timestamps, project identifiers, response identifiers, visibility, answer ordering and attachment relationships are stable concepts and deserve typed columns or child tables.

Feedback extension

CREATE TABLE feedback_items (
  item_id TEXT PRIMARY KEY REFERENCES knowledge_items(id) ON DELETE CASCADE,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  feedback_type TEXT,
  area TEXT,
  landed_at TEXT,
  submitted_at TEXT,
  sentiment TEXT,
  severity TEXT,
  triage_status TEXT NOT NULL DEFAULT 'new'
) STRICT;
 
CREATE TABLE feedback_answers (
  id TEXT PRIMARY KEY,
  feedback_item_id TEXT NOT NULL
    REFERENCES feedback_items(item_id) ON DELETE CASCADE,
  field_id TEXT,
  field_ref TEXT,
  field_type TEXT,
  question TEXT NOT NULL,
  answer_type TEXT,
  answer_text TEXT,
  answer_json TEXT CHECK (
    answer_json IS NULL OR json_valid(answer_json)
  ),
  ordinal INTEGER NOT NULL,
  UNIQUE (feedback_item_id, field_id, ordinal)
) STRICT;
 
CREATE TABLE attachments (
  id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL REFERENCES knowledge_items(id) ON DELETE CASCADE,
  source_url TEXT,
  local_path TEXT,
  media_type TEXT,
  byte_size INTEGER,
  sha256 TEXT,
  visibility TEXT NOT NULL,
  redaction_state TEXT NOT NULL DEFAULT 'unreviewed'
) STRICT;

Typeform response tokens or response IDs should be retained as external_id and protected by UNIQUE (source_id, external_id). This makes retries and overlapping incremental fetch windows safe because the same source response is upserted rather than duplicated.

Typeform documents that responses include landing and submission timestamps and can contain answer arrays with different answer types, including text, choices, numbers, booleans and file URLs. Typeform JSON Response Explanation Typeform Webhook Example Payload Separate answer rows preserve that structure without adding a new column every time the form changes.

Analytics extension

CREATE TABLE metric_observations (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id),
  source_id TEXT NOT NULL REFERENCES sources(id),
  metric_name TEXT NOT NULL,
  period_start TEXT NOT NULL,
  period_end TEXT NOT NULL,
  numeric_value REAL,
  text_value TEXT,
  unit TEXT,
  dimensions_json TEXT NOT NULL DEFAULT '{}'
    CHECK (json_valid(dimensions_json)),
  query_hash TEXT,
  observed_at TEXT NOT NULL,
  UNIQUE (
    source_id,
    metric_name,
    period_start,
    period_end,
    dimensions_json
  )
) STRICT;

Analytics and feedback should share project, source, ingestion and visibility infrastructure, but they should not share one overloaded payload table. Analytics values have time windows, dimensions, units and reproducibility requirements that feedback does not.

Search index

CREATE VIRTUAL TABLE knowledge_items_fts USING fts5(
  title,
  body,
  content='knowledge_items',
  content_rowid='rowid',
  tokenize='unicode61'
);

FTS5 is SQLite’s full-text-search virtual table module. SQLite FTS5 Extension An external-content index avoids maintaining a second canonical copy of the item body. Triggers or an explicit rebuild command should keep the FTS table synchronized.

SQLite Connection Settings

Every CLI connection should explicitly apply:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA busy_timeout = 5000;

Foreign-key enforcement must be enabled explicitly per connection because SQLite does not guarantee that it is enabled by default. SQLite Foreign Key Support

WAL is suitable when project scripts and read commands may overlap on one local machine. SQLite notes that WAL can improve read and write concurrency, but all processes must use the same host. SQLite Write-Ahead Logging

synchronous = FULL is the conservative choice for an ingestion database whose latest external records should survive a power failure. SQLite documents that NORMAL in WAL mode protects database consistency but can lose the latest committed transactions after a power failure. SQLite PRAGMA Documentation If all data is rebuildable and ingestion speed later matters, NORMAL can be considered deliberately.

Store timestamps as UTC ISO 8601 text. SQLite has no dedicated datetime storage class and supports date/time values as ISO 8601 text, Julian day numbers or Unix timestamps. SQLite Datatypes ISO text is the most readable and aligns with the Typeform timestamps already used by the collector.

Project-Aware CLI

The CLI should be named cdp-index and discover context from the current working directory.

From projects/adapt-action-explorer/:

cdp-index status
cdp-index build
cdp-index feedback list --since 30d
cdp-index feedback search "CSV export"
cdp-index feedback show 69zt8mx8e3in8wtc297y69zt8428tv5s
cdp-index analytics list --since 7d
cdp-index sql "select area, count(*) from feedback_items group by area"

Those commands should not load Doppler or require Typeform access. They read the persisted store.

Only source synchronization needs credentials:

cdp-index ingest typeform
cdp-index ingest posthog

The CLI should walk upward to find the repository root, then select the nearest projects/*/knowledge.json. --project adapt-action-explorer can override automatic selection for repository-wide commands.

Typeform Ingestion Strategy

The most robust first implementation is incremental polling plus periodic reconciliation:

Store the last successful Typeform cursor and timestamp in ingestion_runs or sync_cursors.
Request responses after the stored cursor, with a small overlap window.
Upsert by (source_id, external_id) inside one transaction.
Download attachments, compute checksums and store files outside SQLite.
Write a sanitized NDJSON snapshot for Git.
Advance the cursor only after the transaction and snapshot succeed.
Periodically reconcile a wider date window to recover missed records.

Typeform’s Responses API supports cursor-based after and before traversal as well as since and until date filtering. The API documentation warns that very recent responses may not appear immediately, which supports using an overlap window or webhook for low-latency ingestion. Typeform Retrieve Responses

A later webhook can send submissions immediately to a D1-backed endpoint. Typeform requires new webhook URLs to use HTTPS, supports signed payloads, and retries certain failed deliveries. Typeform Webhooks API Polling should still remain as a reconciliation path because it is simpler to replay and verify.

Privacy and Publication Boundaries

The repository’s content boundaries exclude personal emails, relationship notes, private threads and sensitive access details. Typeform answers, hidden fields and attachments can contain information that should not be published.

The knowledge schema should therefore make visibility and redaction first-class fields. At minimum:

public      safe for published documentation and public MCP
internal    available to authenticated collaborators
private     retained locally, never exported automatically
restricted  requires explicit handling or removal

The MCP generator must select only permitted records. It should never export payload_json, hidden fields or attachments merely because they exist in SQLite. A specific projection should define which fields are included.

Raw Typeform payloads should remain in the local database or protected hosted store. Sanitized normalized snapshots can enter Git. If a raw response must be deleted for privacy reasons, the deletion workflow must remove the local source record, normalized item, attachment files, snapshots and generated exports.

Why D1 Is a Later Promotion Path

The local schema should stay within the SQLite subset supported by D1 so it can be promoted without redesign. D1 supports most SQLite conventions as well as JSON and FTS5. Cloudflare D1 SQL Statements Cloudflare recommends STRICT tables for avoiding JavaScript and database type mismatches. Cloudflare D1 Workers Binding API

Move the canonical store to D1 when one or more of these become true:

The deployed MCP must query feedback immediately after submission.
Trigger.dev or a webhook must write from outside a persistent local machine.
Several collaborators need shared live writes.
Automated consumers cannot rely on pulling repository snapshots.
Managed recovery and hosted availability are worth the operational dependency.

Until then, local SQLite plus project snapshots keeps the system transparent, portable and easy to rebuild.

Decision

Use this combination:

Upstream SQLite 3 as the database engine.
One repository-wide .cdp-index/knowledge.sqlite file, ignored by Git.
STRICT normalized tables for projects, sources, knowledge items, feedback, answers, attachments, analytics and ingestion runs.
JSON stored as validated text only for irregular source payloads and dimensions.
FTS5 for project-wide search.
WAL with explicit foreign keys and conservative synchronization for local operation.
Sanitized NDJSON snapshots inside each project as the durable Git representation.
A cwd-aware cdp-index CLI that queries locally without provider credentials.
Credentialed ingestion only, using Typeform and PostHog secrets from Doppler.
Generated sanitized exports for the MCP and docs.
Cloudflare D1 later, if live hosted reads and writes become necessary.

This is more than choosing a .sqlite suffix. It defines a stable separation between source systems, durable repository records, the fast local query index, and publishable knowledge.

Implementation Sequence

Phase	Outcome
1. Foundation	Add migrations, database wrapper, root discovery and project context
2. Feedback persistence	Move Typeform normalization into one adapter and upsert responses, answers and attachments
3. Keyless reads	Change local CLI and MCP preparation to query persisted data
4. Durable snapshots	Export sanitized project NDJSON and rebuild SQLite from it
5. Search	Index Markdown and feedback with FTS5
6. Analytics	Persist PostHog metric observations using the shared source and ingestion model
7. Hosted decision	Evaluate D1 only after freshness or multi-writer requirements are demonstrated

Before implementation, the project schema should be updated to permit the existing feedback area and document type, and the repository’s currently broken dependency and verification paths should be repaired so migrations and ingestion behavior can be tested reliably.

Sources

Source	Publisher	Date	Used For
SQLite: Single-file Cross-platform Database	SQLite	Accessed 2026-07-12	File portability, compatibility and application-file suitability
SQLite Database File Format	SQLite	2026	Main database, journal and WAL file behavior
SQLite As An Application File Format	SQLite	Accessed 2026-07-12	Suitability as an application data format
SQLite STRICT Tables	SQLite	Updated 2025-06-12	Strict type enforcement
SQLite JSON Functions and Operators	SQLite	2026	JSON storage and query functions
SQLite FTS5 Extension	SQLite	Accessed 2026-07-12	Full-text-search support
SQLite Write-Ahead Logging	SQLite	2026	WAL behavior, concurrency and same-host constraint
SQLite PRAGMA Documentation	SQLite	2026	Synchronization and connection settings
SQLite Foreign Key Support	SQLite	2026	Explicit foreign-key enforcement
SQLite Datatypes	SQLite	Accessed 2026-07-12	Date and time storage alternatives
SQLite Backup API	SQLite	2025	Consistent snapshots and backup API
SQLite VACUUM	SQLite	2025	`VACUUM INTO` snapshots
Git Attributes	Git project	Accessed 2026-07-12	Database-like binary files in Git
Git gitattributes Documentation	Git project	Accessed 2026-07-12	Binary diff and text conversion behavior
Typeform Responses API	Typeform	Accessed 2026-07-12	On-demand response retrieval
Typeform Retrieve Responses	Typeform	Accessed 2026-07-12	Pagination, cursors, date filtering and response delay
Typeform JSON Response Explanation	Typeform	Accessed 2026-07-12	Response timestamps and answer structure
Typeform Webhooks API	Typeform	Accessed 2026-07-12	Webhook delivery, HTTPS, signing and retry behavior
Typeform Webhook Example Payload	Typeform	Accessed 2026-07-12	Answer and attachment payload types
Cloudflare D1 Overview	Cloudflare	Updated 2026-04-30	Managed SQLite semantics and recovery
Cloudflare D1 SQL Statements	Cloudflare	Updated 2026-04-21	SQLite, JSON and FTS5 compatibility
Cloudflare D1 Workers Binding API	Cloudflare	Updated 2026-04-21	Typed Worker access and strict-table recommendation
Cloudflare Pages D1 Bindings	Cloudflare	2026	Pages Function access to D1
Turso libSQL Documentation	Turso	Accessed 2026-07-12	libSQL relationship to SQLite
Turso Embedded Replicas	Turso	Accessed 2026-07-12	Local replica and remote synchronization model
Project frontmatter schema	CDP Index repository	2026-07-12	Current project document schema
Feedback collector	CDP Index repository	2026-07-12	Existing response normalization and export behavior
Typeform feedback report	CDP Index repository	2026-07-02	Existing generated feedback archive
Analytics collector	CDP Index repository	2026-07-12	Current PostHog query and report behavior
Automation definitions	CDP Index repository	2026-07-12	Existing scheduled ingestion surfaces
Feedback MCP tools	CDP Index repository	2026-07-12	Current live feedback query surface
MCP configuration	CDP Index repository	2026-07-12	Current generated MCP and Cloudflare configuration
Content boundaries	CDP Index repository	2026-07-12	Privacy and publication rules

CDP Index

Explorer

Choosing the SQLite Format for the CDP Knowledge Brain

Choosing the SQLite Format for the CDP Knowledge Brain

Executive Summary

What “SQLite Format” Means Here

Repository Context

Options Considered

Option 1: One committed SQLite file

Option 2: One ignored SQLite file rebuilt from project snapshots

Option 3: Cloudflare D1 as the primary store

Option 4: libSQL or Turso

Recommended Architecture

Filesystem Layout

Schema Design

Common relational core

Feedback extension

Analytics extension

Search index

SQLite Connection Settings

Project-Aware CLI

Typeform Ingestion Strategy

Privacy and Publication Boundaries

Why D1 Is a Later Promotion Path

Decision

Implementation Sequence

Sources

Graph View

Table of Contents