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.

Entity Relationship Diagram

erDiagram
    users ||--o{ auth_accounts : "has"
    users ||--o{ sessions : "has"
    users ||--o{ api_keys : "has"
    users ||--o{ magic_links : "has"
    users ||--o{ ideas : "owns"
    ideas ||--o{ idea_tags : "has"
    ideas ||--o{ idea_links : "has"
    ideas ||--o{ attachments : "has"
    ideas ||--o{ idea_relations : "source"
    ideas ||--o{ idea_relations : "target"
    ideas ||--o{ duplicate_detections : "source"
    ideas ||--o{ duplicate_detections : "target"
    tags ||--o{ idea_tags : "has"
    users ||--o{ oauth_authorization_codes : "has"
    users ||--o{ oauth_refresh_tokens : "has"

Core Tables

users

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

Column	Type	Description
`id`	TEXT (UUID)	Primary key
`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)
`github_sync_repo`	TEXT	GitHub sync repository
`github_sync_token_encrypted`	TEXT	Encrypted GitHub sync token
`settings_json`	TEXT	User settings (JSON string, default `{}`)
`created_at`	TEXT	ISO 8601 timestamp
`updated_at`	TEXT	ISO 8601 timestamp

auth_accounts

Links external OAuth providers to a user.

Column	Type	Description
`id`	TEXT	Primary key
`user_id`	TEXT	FK to users
`provider`	TEXT	`github`, `google`, `apple`, `email`
`provider_account_id`	TEXT	External account ID
`provider_email`	TEXT	Email from the provider
`created_at`	TEXT	ISO 8601 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
`expires_at`	TEXT	30-day expiry timestamp
`created_at`	TEXT	ISO 8601 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
`label`	TEXT	User-assigned label
`key_hash`	TEXT	SHA-256 hash of the `nrp_` prefixed key
`scopes`	TEXT	Space-separated scope list (null for full access)
`source`	TEXT	Key source (default `manual`)
`last_used_at`	TEXT	Timestamp of most recent use
`created_at`	TEXT	ISO 8601 timestamp

ideas

The core content table.

Column	Type	Description
`id`	INTEGER	Auto-increment primary key
`user_id`	TEXT	FK to users
`title`	TEXT	Idea title (max 200 chars)
`body`	TEXT	Markdown body
`status`	TEXT	`captured`, `exploring`, `building`, `shipped`, `shelved`
`source`	TEXT	Origin: `web`, `cli`, `claude-mcp`, `siri`, `email`, `api`, `shortcut`
`source_url`	TEXT	Optional URL reference
`source_summary`	TEXT	Summary from source
`parent_id`	INTEGER	Self-referencing FK for parent idea
`is_archived`	INTEGER	Soft delete flag (0 = active, 1 = archived)
`vectorize_id`	TEXT	Cloudflare Vectorize embedding ID
`created_at`	TEXT	ISO 8601 timestamp
`updated_at`	TEXT	ISO 8601 timestamp

idea_tags

Many-to-many join between ideas and tags.

Column	Type	Description
`idea_id`	INTEGER	FK to ideas
`tag_id`	INTEGER	FK to tags

idea_links

Links 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	ISO 8601 timestamp

idea_relations

Typed directional links between ideas.

Column	Type	Description
`id`	INTEGER	Primary key
`source_idea_id`	INTEGER	FK to ideas
`target_idea_id`	INTEGER	FK to ideas
`relation_type`	TEXT	`related`, `parent`, `blocks`, `inspires`, `duplicate`, `supersedes`
`user_id`	TEXT	FK to users
`score`	REAL	Similarity score (null for manual relations)
`note`	TEXT	Optional description
`created_at`	TEXT	ISO 8601 timestamp

duplicate_detections

Records when the system detects similar ideas.

Column	Type	Description
`id`	INTEGER	Primary key
`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	ISO 8601 timestamp

attachments

Files attached to ideas, stored in R2.

Column	Type	Description
`id`	TEXT	Primary key
`idea_id`	INTEGER	FK to ideas
`user_id`	TEXT	FK to users
`filename`	TEXT	Original filename
`r2_key`	TEXT	R2 object key
`content_type`	TEXT	MIME type
`size_bytes`	INTEGER	File size
`created_at`	TEXT	ISO 8601 timestamp

OAuth Tables

oauth_authorization_codes and oauth_refresh_tokens support the MCP OAuth flow. Authorization codes are short-lived (10 minutes). Refresh tokens expire after 90 days.

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);

Sync triggers keep the FTS index up to date on insert, update, and delete operations. FTS5 is used for keyword search and is combined with vector search for hybrid results.

Indexes

Key indexes for query performance:

Table	Index	Columns
ideas	`idx_ideas_user_status`	`user_id`, `status`
ideas	`idx_ideas_user_created`	`user_id`, `created_at`
ideas	`idx_ideas_parent`	`parent_id`
idea_tags	`idx_idea_tags_idea`	`idea_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`
sessions	`idx_sessions_token`	`token_hash`
api_keys	`idx_api_keys_hash`	`key_hash`

Soft Delete Pattern

Ideas use a soft delete pattern. The is_archived column is 0 for active ideas and 1 for archived ideas.

All queries include a WHERE is_archived = 0 condition by default. This allows recovery of accidentally deleted ideas and preserves relation integrity.