Database Documentation

Last Updated: 2026-05-08

The backend uses SQLite with Doctrine ORM for persistence.

Overview

• Engine: SQLite (single file)
• ORM: Doctrine ORM with PHP 8 attribute mapping
• Location: backend/var/data.db
• Tables: 8

Why SQLite?

• No separate database server required
• Single file, easy to backup and reset
• Fast for the team's scale
• Zero configuration

---

Schema

Tables (8)

1. category — Thematic lists with digest settings
2. knowledge — Knowledge items with AI highlights
3. digest — Generated digest reports
4. digest_delivery — Per-user delivery tracking
5. subscription — User category subscriptions
6. digest_knowledge — ManyToMany join table (Digest ↔ Knowledge)
7. doctrine_migration_versions — Migration tracking
8. sqlite_sequence — Auto-increment tracking

Entity Relationship Diagram

---

Table Definitions

category

Column	Type	Constraints
id	INTEGER	PK, AUTOINCREMENT
name	VARCHAR(255)	NOT NULL
description	VARCHAR(255)	NULLABLE
icon	VARCHAR(50)	DEFAULT '📁'
target_groups	TEXT (JSON)	NULLABLE
notion_id	VARCHAR(255)	NULLABLE
last_synced_at	DATETIME	NULLABLE
notion_last_edited_at	DATETIME	NULLABLE
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL
is_active	BOOLEAN	DEFAULT 1
sort_order	INTEGER	DEFAULT 0
digest_enabled	BOOLEAN	DEFAULT 0
digest_frequency	VARCHAR(20)	NULLABLE (enum)
last_digest_at	DATETIME	NULLABLE
digest_day	INTEGER	NULLABLE
digest_time	VARCHAR(5)	NULLABLE

knowledge

Column	Type	Constraints
id	INTEGER	PK, AUTOINCREMENT
title	VARCHAR(255)	NOT NULL
content	TEXT	NOT NULL
tags	TEXT (JSON)	NULLABLE
target_groups	TEXT (JSON)	NULLABLE
user_id	VARCHAR(255)	NULLABLE
status	VARCHAR(50)	DEFAULT 'pending'
url	VARCHAR(500)	NULLABLE
url_metadata	TEXT (JSON)	NULLABLE
source_message	TEXT (JSON)	NULLABLE
attachments	TEXT (JSON)	NULLABLE
summaries	TEXT (JSON)	NULLABLE
highlight	TEXT	NULLABLE
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL
notion_id	VARCHAR(255)	NULLABLE
notion_url	VARCHAR(2048)	NULLABLE
last_synced_at	DATETIME	NULLABLE
notion_last_edited_at	DATETIME	NULLABLE
category_id	INTEGER	FK → category(id) ON DELETE SET NULL

Indexes: category_id, notion_id, status, user_id

digest

Column	Type	Constraints
id	INTEGER	PK, AUTOINCREMENT
title	VARCHAR(255)	NOT NULL
content	TEXT	NULLABLE
period	VARCHAR(50)	DEFAULT 'Weekly'
status	VARCHAR(50)	DEFAULT 'Done'
statistics	TEXT (JSON)	NULLABLE
knowledge_highlights	TEXT (JSON)	NULLABLE
generated_by	VARCHAR(255)	NULLABLE
notion_id	VARCHAR(255)	NULLABLE
notion_url	VARCHAR(2048)	NULLABLE
last_synced_at	DATETIME	NULLABLE
created_at	DATETIME	NOT NULL
updated_at	DATETIME	NOT NULL
category_id	INTEGER	FK → category(id) NOT NULL

digest_delivery

Column	Type	Constraints
id	INTEGER	PK, AUTOINCREMENT
user_id	VARCHAR(255)	NOT NULL
delivery_channel	VARCHAR(50)	DEFAULT 'slack'
status	VARCHAR(50)	DEFAULT 'pending'
created_at	DATETIME	NOT NULL
sent_at	DATETIME	NULLABLE
error_message	TEXT	NULLABLE
retry_count	INTEGER	DEFAULT 0
digest_id	INTEGER	FK → digest(id) ON DELETE CASCADE NOT NULL

subscription

Column	Type	Constraints
id	INTEGER	PK, AUTOINCREMENT
user_id	VARCHAR(255)	NOT NULL
slack_user_name	VARCHAR(255)	NULLABLE
is_active	BOOLEAN	DEFAULT 1
subscribed_at	DATETIME	NOT NULL
unsubscribed_at	DATETIME	NULLABLE
notion_id	VARCHAR(255)	NULLABLE
last_synced_at	DATETIME	NULLABLE
category_id	INTEGER	FK → category(id) ON DELETE CASCADE NOT NULL

Unique constraint: uq_user_category_subscription on (user_id, category_id)

Indexes: user_id, category_id, is_active

digest_knowledge (join table)

Column	Type	Constraints
digest_id	INTEGER	FK → digest(id) ON DELETE CASCADE
knowledge_id	INTEGER	FK → knowledge(id) ON DELETE CASCADE

Primary key: (digest_id, knowledge_id)

---

Schema Management

The schema was created using doctrine:schema:create (not migrations). To recreate:

cd backend
php bin/console doctrine:database:drop --force
php bin/console doctrine:database:create
php bin/console doctrine:schema:create

To check schema validity:

php bin/console doctrine:schema:validate

---

Direct SQLite Access

cd backend/var
sqlite3 data.db

# List tables
.tables

# Describe table
.schema knowledge

# Query
SELECT * FROM knowledge LIMIT 10;

# Count records
SELECT 'knowledge' as tbl, COUNT(*) FROM knowledge
UNION ALL SELECT 'category', COUNT(*) FROM category
UNION ALL SELECT 'digest', COUNT(*) FROM digest
UNION ALL SELECT 'subscription', COUNT(*) FROM subscription;

.quit

---

Backup and Restore

# Backup
cp backend/var/data.db backend/var/data.db.backup

# Export to SQL
sqlite3 backend/var/data.db .dump > backup.sql

# Restore
cp backend/var/data.db.backup backend/var/data.db