commit bf3b224f88c40039b3b6c70acc2851b2cc02e71d
parent 613fd402b13f2bc33c6597f04818120ed1efa503
Author: Silas Brack <silasbrack@gmail.com>
Date: Fri, 12 Jun 2026 21:26:49 +0200
refactor: move migrations to SQL files, update README
Migrations now live as separate .sql files in migrations/ and are
embedded at compile time via include_str!. Readable SQL, testable
with sqlite3, compile-time guarantees.
Updated README to reflect current architecture (SQLite, no
TrailBase) with environment variables and migration docs.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
4 files changed, 180 insertions(+), 172 deletions(-)
diff --git a/README.md b/README.md
@@ -1,38 +1,56 @@
# Simple Web App
-A link aggregator built with Rust, Axum, Askama, and Trailbase.
+A link aggregator built with Rust, Axum, Askama, and SQLite.
-## Principles
+## Architecture
-1. **Explicit over clever** — Code reads top-to-bottom. A new reader should understand what a function does without chasing through layers of indirection.
-2. **Pure functions** — Isolate decision logic from I/O.
-3. **Linear flow** — Handlers read as sequential steps: authenticate, fetch data, build view, respond.
-4. **Minimize shared state** — Pass values explicitly.
-5. **Minimize indirection** — No traits or abstractions "in case we need to swap later."
+- **Web framework**: Axum (async Rust)
+- **Templates**: Askama (compiled HTML templates)
+- **Database**: SQLite via rusqlite (embedded, no separate service)
+- **Frontend**: Datastar v1.0.2 for interactive updates (voting, search, real-time comments)
+- **Auth**: argon2id password hashing, JWT tokens, email verification via SMTP
## Development
```bash
nix develop
-cargo run # start server
-cargo watch -x run # auto-reload
-trail run # trailbase (separate terminal)
+cargo run # start server on :8000
+cargo watch -x run # auto-reload on changes
```
-## Scripts
+### Environment variables
-```bash
-./scripts/generate-types.sh # regenerate Rust types from trailbase schema
-TOKEN=$(trail user mint-token <email>) ./scripts/seed.sh # seed sample data
+| Variable | Default | Description |
+|----------|---------|-------------|
+| `PORT` | `8000` | Server port |
+| `DB_PATH` | `data/app.db` | SQLite database path |
+| `JWT_SECRET` | random | Secret for JWT tokens (random per restart if unset) |
+| `SMTP_HOST` | `localhost` | SMTP server for verification emails |
+| `SMTP_PORT` | `25` | SMTP port |
+| `SMTP_FROM` | `noreply@silasbrack.com` | Sender email address |
+| `SITE_URL` | `http://localhost:8000` | Base URL for email links |
+| `DEBUG` | `true` | Debug mode |
+
+## Migrations
+
+Schema migrations live in `migrations/` as numbered SQL files and are embedded at compile time:
+
+```
+migrations/
+ 0001_initial_schema.sql
+ 0002_email_verification.sql
```
+To add a new migration, create `migrations/NNNN_description.sql` and add an `include_str!` entry in `src/migrations.rs`. Migrations run automatically on startup in a single transaction.
+
## Building
```bash
-nix build # app binary
-nix build .#trailbase # trailbase binary
-
-# WASM search guest (must be built separately)
-cd guests/rust && cargo build --target wasm32-wasip2 --release
-cp target/wasm32-wasip2/release/search_guest.wasm ../../traildepot/wasm/
+nix build # produces result/bin/simple-web-app
```
+
+## Deployment
+
+Deployed on NixOS via the infrastructure flake. The app runs as a systemd service with `DynamicUser=true`, database on a Hetzner volume at `/mnt/volume-hel1-1/simple-web-app/data/app.db`.
+
+Static assets (CSS, JS) are embedded in the binary at compile time.
diff --git a/migrations/0001_initial_schema.sql b/migrations/0001_initial_schema.sql
@@ -0,0 +1,117 @@
+CREATE TABLE IF NOT EXISTS user (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ email TEXT UNIQUE NOT NULL,
+ password_hash TEXT NOT NULL,
+ username TEXT UNIQUE NOT NULL,
+ about TEXT,
+ karma INTEGER DEFAULT 0,
+ created_at TEXT DEFAULT (datetime('now'))
+);
+
+CREATE TABLE IF NOT EXISTS story (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ url TEXT,
+ title TEXT NOT NULL,
+ text TEXT NOT NULL DEFAULT '',
+ published TEXT NOT NULL DEFAULT (datetime('now')),
+ author TEXT,
+ language TEXT NOT NULL DEFAULT 'en',
+ created_by INTEGER REFERENCES user(id),
+ score INTEGER NOT NULL DEFAULT 0,
+ comment_count INTEGER NOT NULL DEFAULT 0,
+ domain TEXT
+);
+
+CREATE TABLE IF NOT EXISTS comment (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ story_id INTEGER NOT NULL REFERENCES story(id),
+ parent_id INTEGER REFERENCES comment(id),
+ path TEXT NOT NULL DEFAULT '',
+ depth INTEGER NOT NULL DEFAULT 0,
+ text TEXT NOT NULL DEFAULT '',
+ score INTEGER NOT NULL DEFAULT 0,
+ created_at TEXT NOT NULL DEFAULT (datetime('now')),
+ created_by INTEGER NOT NULL REFERENCES user(id)
+);
+
+CREATE TABLE IF NOT EXISTS vote (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user_id INTEGER NOT NULL REFERENCES user(id),
+ target_type INTEGER NOT NULL,
+ target_id INTEGER NOT NULL,
+ created_at TEXT NOT NULL DEFAULT (datetime('now')),
+ UNIQUE(user_id, target_type, target_id)
+);
+
+CREATE TABLE IF NOT EXISTS category (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name TEXT
+);
+
+CREATE TABLE IF NOT EXISTS story_category (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ news_item_id INTEGER NOT NULL REFERENCES story(id),
+ category_id INTEGER NOT NULL REFERENCES category(id)
+);
+
+CREATE VIRTUAL TABLE IF NOT EXISTS story_fts USING fts5(
+ title, text,
+ content='story',
+ content_rowid='id',
+ tokenize='porter unicode61'
+);
+
+-- Indices
+CREATE INDEX IF NOT EXISTS idx_story_published ON story(published DESC);
+CREATE INDEX IF NOT EXISTS idx_story_score ON story(score DESC);
+CREATE INDEX IF NOT EXISTS idx_story_created_by ON story(created_by);
+CREATE INDEX IF NOT EXISTS idx_comment_story_id ON comment(story_id);
+CREATE INDEX IF NOT EXISTS idx_comment_parent_id ON comment(parent_id);
+CREATE INDEX IF NOT EXISTS idx_comment_created_by ON comment(created_by);
+CREATE INDEX IF NOT EXISTS idx_vote_user_target ON vote(user_id, target_type, target_id);
+CREATE INDEX IF NOT EXISTS idx_story_category_news_item ON story_category(news_item_id);
+CREATE INDEX IF NOT EXISTS idx_story_category_category ON story_category(category_id);
+
+-- FTS triggers
+CREATE TRIGGER IF NOT EXISTS story_fts_insert AFTER INSERT ON story BEGIN
+ INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text);
+END;
+
+CREATE TRIGGER IF NOT EXISTS story_fts_update AFTER UPDATE ON story BEGIN
+ INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text);
+ INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text);
+END;
+
+CREATE TRIGGER IF NOT EXISTS story_fts_delete AFTER DELETE ON story BEGIN
+ INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text);
+END;
+
+-- Comment count triggers
+CREATE TRIGGER IF NOT EXISTS comment_count_insert AFTER INSERT ON comment BEGIN
+ UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id;
+END;
+
+CREATE TRIGGER IF NOT EXISTS comment_count_delete AFTER DELETE ON comment BEGIN
+ UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id;
+END;
+
+-- Vote score/karma triggers
+CREATE TRIGGER IF NOT EXISTS vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN
+ UPDATE story SET score = score + 1 WHERE id = new.target_id;
+ UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id);
+END;
+
+CREATE TRIGGER IF NOT EXISTS vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN
+ UPDATE story SET score = score - 1 WHERE id = old.target_id;
+ UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id);
+END;
+
+CREATE TRIGGER IF NOT EXISTS vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN
+ UPDATE comment SET score = score + 1 WHERE id = new.target_id;
+ UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id);
+END;
+
+CREATE TRIGGER IF NOT EXISTS vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN
+ UPDATE comment SET score = score - 1 WHERE id = old.target_id;
+ UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id);
+END;
diff --git a/migrations/0002_email_verification.sql b/migrations/0002_email_verification.sql
@@ -0,0 +1,2 @@
+ALTER TABLE user ADD COLUMN email_verified INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE user ADD COLUMN verification_token TEXT;
diff --git a/src/migrations.rs b/src/migrations.rs
@@ -1,140 +1,16 @@
use rusqlite::Connection;
-/// Each migration is a SQL string. Migration 0 is the initial schema.
-/// Migrations are applied in order, and the current version is tracked
-/// in the `migration_version` table.
const MIGRATIONS: &[&str] = &[
- // Migration 0: Initial schema
- r#"
- CREATE TABLE IF NOT EXISTS user (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- email TEXT UNIQUE NOT NULL,
- password_hash TEXT NOT NULL,
- username TEXT UNIQUE NOT NULL,
- about TEXT,
- karma INTEGER DEFAULT 0,
- created_at TEXT DEFAULT (datetime('now'))
- );
-
- CREATE TABLE IF NOT EXISTS story (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- url TEXT,
- title TEXT NOT NULL,
- text TEXT NOT NULL DEFAULT '',
- published TEXT NOT NULL DEFAULT (datetime('now')),
- author TEXT,
- language TEXT NOT NULL DEFAULT 'en',
- created_by INTEGER REFERENCES user(id),
- score INTEGER NOT NULL DEFAULT 0,
- comment_count INTEGER NOT NULL DEFAULT 0,
- domain TEXT
- );
-
- CREATE TABLE IF NOT EXISTS comment (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- story_id INTEGER NOT NULL REFERENCES story(id),
- parent_id INTEGER REFERENCES comment(id),
- path TEXT NOT NULL DEFAULT '',
- depth INTEGER NOT NULL DEFAULT 0,
- text TEXT NOT NULL DEFAULT '',
- score INTEGER NOT NULL DEFAULT 0,
- created_at TEXT NOT NULL DEFAULT (datetime('now')),
- created_by INTEGER NOT NULL REFERENCES user(id)
- );
-
- CREATE TABLE IF NOT EXISTS vote (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- user_id INTEGER NOT NULL REFERENCES user(id),
- target_type INTEGER NOT NULL,
- target_id INTEGER NOT NULL,
- created_at TEXT NOT NULL DEFAULT (datetime('now')),
- UNIQUE(user_id, target_type, target_id)
- );
-
- CREATE TABLE IF NOT EXISTS category (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- name TEXT
- );
-
- CREATE TABLE IF NOT EXISTS story_category (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- news_item_id INTEGER NOT NULL REFERENCES story(id),
- category_id INTEGER NOT NULL REFERENCES category(id)
- );
-
- CREATE VIRTUAL TABLE IF NOT EXISTS story_fts USING fts5(
- title, text,
- content='story',
- content_rowid='id',
- tokenize='porter unicode61'
- );
-
- CREATE INDEX IF NOT EXISTS idx_story_published ON story(published DESC);
- CREATE INDEX IF NOT EXISTS idx_story_score ON story(score DESC);
- CREATE INDEX IF NOT EXISTS idx_story_created_by ON story(created_by);
- CREATE INDEX IF NOT EXISTS idx_comment_story_id ON comment(story_id);
- CREATE INDEX IF NOT EXISTS idx_comment_parent_id ON comment(parent_id);
- CREATE INDEX IF NOT EXISTS idx_comment_created_by ON comment(created_by);
- CREATE INDEX IF NOT EXISTS idx_vote_user_target ON vote(user_id, target_type, target_id);
- CREATE INDEX IF NOT EXISTS idx_story_category_news_item ON story_category(news_item_id);
- CREATE INDEX IF NOT EXISTS idx_story_category_category ON story_category(category_id);
-
- CREATE TRIGGER IF NOT EXISTS story_fts_insert AFTER INSERT ON story BEGIN
- INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text);
- END;
-
- CREATE TRIGGER IF NOT EXISTS story_fts_update AFTER UPDATE ON story BEGIN
- INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text);
- INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text);
- END;
-
- CREATE TRIGGER IF NOT EXISTS story_fts_delete AFTER DELETE ON story BEGIN
- INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text);
- END;
-
- CREATE TRIGGER IF NOT EXISTS comment_count_insert AFTER INSERT ON comment BEGIN
- UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id;
- END;
-
- CREATE TRIGGER IF NOT EXISTS comment_count_delete AFTER DELETE ON comment BEGIN
- UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id;
- END;
-
- CREATE TRIGGER IF NOT EXISTS vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN
- UPDATE story SET score = score + 1 WHERE id = new.target_id;
- UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id);
- END;
-
- CREATE TRIGGER IF NOT EXISTS vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN
- UPDATE story SET score = score - 1 WHERE id = old.target_id;
- UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id);
- END;
-
- CREATE TRIGGER IF NOT EXISTS vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN
- UPDATE comment SET score = score + 1 WHERE id = new.target_id;
- UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id);
- END;
-
- CREATE TRIGGER IF NOT EXISTS vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN
- UPDATE comment SET score = score - 1 WHERE id = old.target_id;
- UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id);
- END;
- "#,
-
- // Migration 1: Add email verification columns
- r#"
- ALTER TABLE user ADD COLUMN email_verified INTEGER NOT NULL DEFAULT 0;
- ALTER TABLE user ADD COLUMN verification_token TEXT;
- "#,
+ include_str!("../migrations/0001_initial_schema.sql"),
+ include_str!("../migrations/0002_email_verification.sql"),
];
pub fn run(conn: &Connection) -> Result<(), String> {
- // Create migration_version table if it doesn't exist
conn.execute_batch(
- "CREATE TABLE IF NOT EXISTS migration_version (version INTEGER NOT NULL);"
- ).map_err(|e| format!("Failed to create migration_version table: {}", e))?;
+ "CREATE TABLE IF NOT EXISTS migration_version (version INTEGER NOT NULL);",
+ )
+ .map_err(|e| format!("Failed to create migration_version table: {e}"))?;
- // Get current version
let current_version: i64 = conn
.query_row(
"SELECT COALESCE(MAX(version), 0) FROM migration_version",
@@ -146,7 +22,6 @@ pub fn run(conn: &Connection) -> Result<(), String> {
let total = MIGRATIONS.len() as i64;
if current_version >= total {
- eprintln!("Database is up to date (version {})", current_version);
return Ok(());
}
@@ -157,40 +32,36 @@ pub fn run(conn: &Connection) -> Result<(), String> {
current_version
);
- // Run all pending migrations in a single transaction
conn.execute_batch("BEGIN TRANSACTION;")
- .map_err(|e| format!("Failed to begin transaction: {}", e))?;
+ .map_err(|e| format!("Failed to begin transaction: {e}"))?;
for i in current_version..total {
eprintln!(" Applying migration {}...", i);
- conn.execute_batch(MIGRATIONS[i as usize])
- .map_err(|e| {
- let _ = conn.execute_batch("ROLLBACK;");
- format!("Migration {} failed: {}", i, e)
- })?;
+ conn.execute_batch(MIGRATIONS[i as usize]).map_err(|e| {
+ let _ = conn.execute_batch("ROLLBACK;");
+ format!("Migration {} failed: {}", i, e)
+ })?;
}
- // Update version
- if current_version == 0 {
- // Check if there are any rows
- let count: i64 = conn
- .query_row("SELECT COUNT(*) FROM migration_version", [], |row| row.get(0))
- .map_err(|e| format!("Failed to count migration_version rows: {}", e))?;
+ let count: i64 = conn
+ .query_row("SELECT COUNT(*) FROM migration_version", [], |row| {
+ row.get(0)
+ })
+ .map_err(|e| format!("Failed to count migration_version rows: {e}"))?;
- if count == 0 {
- conn.execute("INSERT INTO migration_version (version) VALUES (?1)", [total])
- .map_err(|e| format!("Failed to insert migration version: {}", e))?;
- } else {
- conn.execute("UPDATE migration_version SET version = ?1", [total])
- .map_err(|e| format!("Failed to update migration version: {}", e))?;
- }
+ if count == 0 {
+ conn.execute(
+ "INSERT INTO migration_version (version) VALUES (?1)",
+ [total],
+ )
+ .map_err(|e| format!("Failed to insert migration version: {e}"))?;
} else {
conn.execute("UPDATE migration_version SET version = ?1", [total])
- .map_err(|e| format!("Failed to update migration version: {}", e))?;
+ .map_err(|e| format!("Failed to update migration version: {e}"))?;
}
conn.execute_batch("COMMIT;")
- .map_err(|e| format!("Failed to commit transaction: {}", e))?;
+ .map_err(|e| format!("Failed to commit transaction: {e}"))?;
eprintln!("Migrations complete. Database is now at version {}", total);
Ok(())
