Database Design
Overview
This project uses an embedded SQLite database for local development.
The canonical schema lives in db/schema.sql;
incremental changes are tracked in db/migrations/.
Database Engine
| Aspect |
Value |
| Engine |
SQLite (development) / PostgreSQL (production) |
| Location |
var/app.sqlite3 |
| Driver |
Python stdlib sqlite3 (no third-party dependency) |
Schema Diagram
┌─────────────┐ ┌─────────────┐
│ users │ │ posts │
├─────────────┤ ├─────────────┤
│ id (PK) │──┐ │ id (PK) │
│ name │ │ │ user_id (FK)│──┐
│ email │ └───>│ title │ │
│ created_at │ │ content │ │
└─────────────┘ │ created_at │ │
└─────────────┘ │
│
┌─────────────┐ │
│ comments │ │
├─────────────┤ │
│ id (PK) │ │
│ post_id (FK)│<─┘
│ body │
└─────────────┘
Tables
users
| Column |
Type |
Constraints |
Description |
id |
INTEGER |
PK, AUTOINCREMENT |
Unique identifier |
name |
TEXT |
NOT NULL |
Display name |
email |
TEXT |
UNIQUE, NOT NULL |
Email address |
created_at |
TIMESTAMP |
DEFAULT NOW |
Creation timestamp |
posts
| Column |
Type |
Constraints |
Description |
id |
INTEGER |
PK, AUTOINCREMENT |
Unique identifier |
user_id |
INTEGER |
FK → users.id, NOT NULL |
Author reference |
title |
TEXT |
NOT NULL |
Post title |
content |
TEXT |
NOT NULL |
Post body |
created_at |
TIMESTAMP |
DEFAULT NOW |
Creation timestamp |
| Column |
Type |
Constraints |
Description |
id |
INTEGER |
PK, AUTOINCREMENT |
Unique identifier |
post_id |
INTEGER |
FK → posts.id, NOT NULL |
Parent post |
body |
TEXT |
NOT NULL |
Comment text |
Indexes
| Index |
Table |
Columns |
Purpose |
idx_users_email |
users |
email |
Fast email lookups |
idx_posts_user |
posts |
user_id |
Posts-by-user queries |
Migrations Strategy
- Schema changes are tracked in
db/migrations/
- Use sequential prefixes (
001_, 002_) for solo/small teams
- Use timestamps (
YYYYMMDDHHMMSS_) for larger teams
- See db/migrations/README.md for conventions