Data Model

NeuralRepo uses Cloudflare D1 (SQLite) as its primary database. The schema is designed for single-user data isolation, efficient full-text search, and flexible idea relations.

Note

Every created_at and updated_at in D1 is datetime('now') — the SQLite format YYYY-MM-DD HH:MM:SS, in UTC, with no T, no Z, and no offset. It is not ISO-8601, and parsing it as such fails in some runtimes. See Timestamps.

Entity Relationship Diagram

magic_links and schema_migrations stand outside this graph: magic links are keyed by email address rather than by user (the account may not exist yet), and migrations track the schema itself.

Core Tables

users

The central user record. All data queries are scoped by user_id.

Column	Type	Description
id	TEXT	Primary key — 32 lowercase hex characters from randomblob(16), not a dashed UUID
email	TEXT	Unique email address
display_name	TEXT	Display name
avatar_url	TEXT	Profile avatar URL
plan	TEXT	free or pro
stripe_customer_id	TEXT	Stripe customer reference
stripe_subscription_id	TEXT	Stripe subscription reference
anthropic_api_key_encrypted	TEXT	Encrypted Anthropic API key (BYOK)
openai_api_key_encrypted	TEXT	Encrypted OpenAI API key (BYOK)
openrouter_api_key_encrypted	TEXT	Encrypted OpenRouter API key (BYOK)
settings_json	TEXT	User settings (JSON string, default {})
trial_ends_at	TEXT	When the Pro trial lapses; the daily cron downgrades past this
trial_used	INTEGER	1 once a trial has been taken, so it cannot be taken twice
created_at	TEXT	YYYY-MM-DD HH:MM:SS UTC
updated_at	TEXT	YYYY-MM-DD HH:MM:SS UTC

auth_accounts

Links external sign-in providers to a user.

Column	Type	Description
id	TEXT	Primary key
user_id	TEXT	FK to users
provider	TEXT	github, google, apple, or email
provider_account_id	TEXT	External account ID; unique together with provider
provider_email	TEXT	Email from the provider
created_at	TEXT	Creation timestamp

sessions

Active user sessions. Tokens are SHA-256 hashed before storage.

Column	Type	Description
id	TEXT	Primary key
user_id	TEXT	FK to users
token_hash	TEXT	SHA-256 hash of the session token (unique)
expires_at	TEXT	Rolling 30-day expiry, pushed forward on every authenticated request
created_at	TEXT	Creation timestamp

api_keys

API keys for programmatic access. Keys are hashed with SHA-256.

Column	Type	Description
id	TEXT	Primary key
user_id	TEXT	FK to users
key_hash	TEXT	SHA-256 hash of the nrp_ prefixed key (unique)
label	TEXT	User-assigned label, default default
scopes	TEXT	Space-separated scope list. NULL — full access — on every key you create; scopes only appear on MCP OAuth tokens.
source	TEXT	Key source, default manual
last_used_at	TEXT	Timestamp of most recent use
created_at	TEXT	Creation timestamp

magic_links

One-time email sign-in tokens, keyed by email rather than user.

Column	Type	Description
id	TEXT	Primary key
email	TEXT	Recipient address
token_hash	TEXT	SHA-256 hash of the emailed token (unique)
expires_at	TEXT	15 minutes after issue
used_at	TEXT	Set on redemption — the row is marked, not deleted
created_at	TEXT	Creation timestamp

ideas

The core content table.

Column	Type	Description
id	INTEGER	Auto-increment primary key — globally unique, and the identifier every API, CLI, and MCP call takes
user_id	TEXT	FK to users
number	INTEGER	Per-user display number, unique with user_id. This is the #42 the UI and CLI print. Nothing resolves a number back to an idea.
title	TEXT	Idea title (max 200 chars)
body	TEXT	Markdown body (max 50,000 chars)
status	TEXT	captured, exploring, building, shipped, shelved
source	TEXT	Origin: web, cli, claude-mcp, siri, email, api, shortcut, ios
source_url	TEXT	Optional URL reference
source_summary	TEXT	Summary from source
parent_id	INTEGER	Self-referencing FK; ON DELETE SET NULL
vectorize_id	TEXT	Cloudflare Vectorize embedding ID (idea_<id>). Null until the queue has processed the idea.
is_archived	INTEGER	Soft delete flag (0 = active, 1 = archived)
created_at	TEXT	Creation timestamp
updated_at	TEXT	Last update timestamp

Danger

id and number are different numbers and only coincide for the first account ever created. number is MAX(number) + 1 within one user; id is the global autoincrement. Every listing prints number; every lookup takes id. See Idea identifiers.

tags

Unique tag definitions scoped by user.

Column	Type	Description
id	INTEGER	Primary key
user_id	TEXT	FK to users; unique together with name
name	TEXT	Tag name (lowercase, unique per user)
color	TEXT	Optional hex color
vectorize_id	TEXT	Vector ID for the tag’s own embedding (tag-<user_id>-<tag_id>)
created_at	TEXT	Creation timestamp

idea_tags

Many-to-many join between ideas and tags. The composite primary key is (idea_id, tag_id), so the same tag cannot be attached twice.

Column	Type	Description
idea_id	INTEGER	FK to ideas, cascade delete
tag_id	INTEGER	FK to tags, cascade delete

idea_links

URLs attached to ideas.

Column	Type	Description
id	INTEGER	Primary key
idea_id	INTEGER	FK to ideas
url	TEXT	Link URL
title	TEXT	Optional display title
link_type	TEXT	url, claude-chat, github-repo, github-issue, attachment
created_at	TEXT	Creation timestamp

idea_relations

Typed directional links between ideas — the mind map’s edges.

Column	Type	Description
id	INTEGER	Primary key
user_id	TEXT	FK to users
source_idea_id	INTEGER	FK to ideas
target_idea_id	INTEGER	FK to ideas
relation_type	TEXT	related, parent, blocks, inspires, supersedes — plus duplicate, which the constraint permits but nothing writes
score	REAL	Similarity score (null for manual relations)
note	TEXT	Optional description
created_by	TEXT	user or system
created_at	TEXT	Creation timestamp

Uniqueness is on (source_idea_id, target_idea_id, created_by). That third column is load-bearing: it lets a manual link and an auto-detected one coexist between the same pair, and it is why a second manual link between two ideas fails while the system can still add its own.

duplicate_detections

Records when the system detects similar ideas.

Column	Type	Description
id	INTEGER	Primary key
user_id	TEXT	FK to users
idea_id	INTEGER	The newer/suspected duplicate
duplicate_of_id	INTEGER	The existing original idea
similarity_score	REAL	Cosine similarity (0.0 to 1.0)
status	TEXT	pending, merged, dismissed
created_at	TEXT	Creation timestamp

A unique index on (idea_id, duplicate_of_id) means re-running detection cannot create the same detection twice.

attachments

File metadata, with the object itself in R2 under r2_key.

Column	Type	Description
id	TEXT	Primary key
idea_id	INTEGER	FK to ideas
user_id	TEXT	FK to users
filename	TEXT	Original filename
content_type	TEXT	MIME type
size_bytes	INTEGER	File size
r2_key	TEXT	Object key in the R2 bucket
created_at	TEXT	Creation timestamp

Caution

Nothing writes to this table. There is no upload endpoint anywhere in the product, and the matching attachment link type has no producer either. The table and the R2 binding exist ahead of the feature — treat file attachments as unimplemented rather than undocumented.

billing_events

An append-only log of Stripe webhook events: stripe_event_id (unique, which is what makes webhook delivery idempotent), event_type, amount_cents, currency, the Stripe customer, subscription, and invoice ids, and a metadata_json blob.

OAuth Tables

oauth_authorization_codes and oauth_refresh_tokens support the MCP OAuth flow. Both store SHA-256 hashes rather than the credential. Authorization codes are short-lived (10 minutes) and single-use via used_at; refresh tokens expire after 90 days and carry a revoked_at column. Both default their scopes column to ideas:read ideas:write.

schema_migrations

One row per applied migration file (version, applied_at), so migrations are not re-run.

Full-Text Search

The ideas_fts table is an FTS5 virtual table that mirrors the title and body columns of the ideas table:

CREATE VIRTUAL TABLE ideas_fts USING fts5(
  title, body, content='ideas', content_rowid='id'
);

Three triggers keep it in sync — ideas_ai after insert, ideas_ad after delete, and ideas_au after update (a delete row followed by a fresh insert).

Note

Archiving is an UPDATE, not a DELETE, so an archived idea stays in the FTS index — the archived-out filter happens in the query, not in the index. Its vector is deleted, though, which is why archived ideas vanish from semantic results immediately and from keyword results only because the query says so.

Indexes

Key indexes for query performance:

Table	Index	Columns
ideas	idx_ideas_user	user_id
ideas	idx_ideas_user_status	user_id, status
ideas	idx_ideas_user_created	user_id, created_at DESC
tags	idx_tags_user	user_id
idea_tags	idx_idea_tags_tag	tag_id
idea_relations	idx_relations_source	source_idea_id
idea_relations	idx_relations_target	target_idea_id
idea_links	idx_idea_links_idea	idea_id
duplicate_detections	idx_dupes_user	user_id, status
duplicate_detections	idx_dupes_pair (unique)	idea_id, duplicate_of_id
sessions	idx_sessions_token	token_hash
api_keys	idx_api_keys_hash	key_hash
auth_accounts	idx_auth_accounts_user	user_id
attachments	idx_attachments_idea	idea_id

Soft Delete Pattern

Ideas use a soft delete pattern. The is_archived column is 0 for active ideas and 1 for archived ideas, and every listing query filters is_archived = 0. This preserves relation integrity: an archived idea’s rows in idea_relations and idea_tags survive rather than cascading away.

Danger

Soft delete is not undo. is_archived is absent from the update schema, no endpoint or UI clears it, and archiving deletes the idea’s vector. The row survives; the idea does not, and archived ideas are excluded from every export. Treat archiving as permanent.

Next Steps

Tech Stack The Cloudflare services this schema sits on.

Semantic Search How rows here become vectors, and what the metadata carries.

API Overview Idea identifiers, timestamps, and the field limits enforced above.

Security & Auth What the sessions, keys, and OAuth tables protect.