commit f479a5849d37f1b821cab77ef2af73893a118276
parent d1a959863e0625d2968db158bf0dd23381c1c35c
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 13 Jun 2026 13:04:15 +0200
feat: migrate all tables to STRICT mode
STRICT tables enforce column types at the SQLite level, preventing
silent type coercion. Migration drops all triggers/indices first,
recreates tables in dependency order with STRICT, copies data,
then rebuilds indices, FTS, and triggers.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
4 files changed, 179 insertions(+), 70 deletions(-)
diff --git a/.gitignore b/.gitignore
@@ -1,27 +1,5 @@
-# Rust
-target/
-
-# Sqlite
-*.sqlite3*
-traildepot/data/
-traildepot/secrets/
-
-# WASM guest build artifacts
-guests/rust/target/
-
-# Nix
-result
-
-# Mac OS
-.DS_Store
-
-# Environment
-.env
-
-# IDE
-*.swp
-tags
-TAGS
-.idea/
-*.iml
-data/
+/.env
+/data/
+/target/
+/result
+.DS_Store
+\ No newline at end of file
diff --git a/README.md b/README.md
@@ -31,46 +31,3 @@ A link aggregator built with Rust, Axum, Askama, and SQLite.
- SSE responses use `text/event-stream` with `datastar-patch-elements` events
- Default outer morph mode — include element ID in response HTML
- `X-Accel-Buffering: no` header on SSE responses for nginx compatibility
-
-## Performance
-
-Benchmarked on a 2-core Hetzner VPS (AMD EPYC, 2GB RAM):
-
-| Endpoint | RPS | Latency |
-|----------|-----|---------|
-| Homepage | 3,500 | 3ms avg |
-| Story page | 12,000 | 8ms avg |
-| Static file (ceiling) | 22,000 | 4ms avg |
-
-On a Ryzen 7 7700 (16 threads):
-
-| Endpoint | RPS |
-|----------|-----|
-| Homepage | 31,000 |
-| Story page | 95,000 |
-
-## Setup
-
-```bash
-cp .env.example .env # edit with real values
-nix develop
-cargo run
-```
-
-## Deployment
-
-Deployed on NixOS via an infrastructure flake. Push to `simple-web-app`, update the infra flake (`nix flake update simple-web-app`), push infra.
-
-Static assets (CSS, JS) are embedded in the binary at compile time. Nginx handles gzip compression.
-
-## Migrations
-
-Schema migrations live in `migrations/` as numbered SQL files, embedded at compile time:
-
-```
-migrations/
- 0001_initial_schema.sql
- 0002_email_verification.sql
-```
-
-To add a migration, create `migrations/NNNN_description.sql` and add an `include_str!` line in `src/migrations.rs`. Migrations run automatically on startup in a single transaction.
diff --git a/migrations/0003_strict_tables.sql b/migrations/0003_strict_tables.sql
@@ -0,0 +1,172 @@
+-- Recreate all tables with STRICT mode.
+-- STRICT enforces column types at the SQLite level, preventing
+-- silent type coercion (e.g., inserting a string into an INTEGER column).
+
+PRAGMA foreign_keys=OFF;
+
+-- Drop all triggers first (they reference the old tables)
+DROP TRIGGER IF EXISTS story_fts_insert;
+DROP TRIGGER IF EXISTS story_fts_update;
+DROP TRIGGER IF EXISTS story_fts_delete;
+DROP TRIGGER IF EXISTS comment_count_insert;
+DROP TRIGGER IF EXISTS comment_count_delete;
+DROP TRIGGER IF EXISTS vote_insert_story;
+DROP TRIGGER IF EXISTS vote_delete_story;
+DROP TRIGGER IF EXISTS vote_insert_comment;
+DROP TRIGGER IF EXISTS vote_delete_comment;
+
+-- Drop all indices
+DROP INDEX IF EXISTS idx_story_published;
+DROP INDEX IF EXISTS idx_story_score;
+DROP INDEX IF EXISTS idx_story_created_by;
+DROP INDEX IF EXISTS idx_comment_story_id;
+DROP INDEX IF EXISTS idx_comment_parent_id;
+DROP INDEX IF EXISTS idx_comment_created_by;
+DROP INDEX IF EXISTS idx_vote_user_target;
+DROP INDEX IF EXISTS idx_story_category_news_item;
+DROP INDEX IF EXISTS idx_story_category_category;
+
+-- Drop FTS
+DROP TABLE IF EXISTS story_fts;
+
+-- Recreate tables in dependency order with STRICT
+
+ALTER TABLE user RENAME TO user_old;
+CREATE TABLE 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')),
+ email_verified INTEGER NOT NULL DEFAULT 0,
+ verification_token TEXT
+) STRICT;
+INSERT INTO user SELECT * FROM user_old;
+DROP TABLE user_old;
+
+ALTER TABLE category RENAME TO category_old;
+CREATE TABLE category (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name TEXT
+) STRICT;
+INSERT INTO category SELECT * FROM category_old;
+DROP TABLE category_old;
+
+ALTER TABLE story RENAME TO story_old;
+CREATE TABLE 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
+) STRICT;
+INSERT INTO story SELECT * FROM story_old;
+DROP TABLE story_old;
+
+ALTER TABLE comment RENAME TO comment_old;
+CREATE TABLE comment (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ story_id INTEGER NOT NULL REFERENCES story(id),
+ parent_id INTEGER,
+ 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)
+) STRICT;
+INSERT INTO comment SELECT * FROM comment_old;
+DROP TABLE comment_old;
+
+ALTER TABLE vote RENAME TO vote_old;
+CREATE TABLE 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)
+) STRICT;
+INSERT INTO vote SELECT * FROM vote_old;
+DROP TABLE vote_old;
+
+ALTER TABLE story_category RENAME TO story_category_old;
+CREATE TABLE story_category (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ news_item_id INTEGER NOT NULL REFERENCES story(id),
+ category_id INTEGER NOT NULL REFERENCES category(id)
+) STRICT;
+INSERT INTO story_category SELECT * FROM story_category_old;
+DROP TABLE story_category_old;
+
+-- Recreate indices
+CREATE INDEX idx_story_published ON story(published DESC);
+CREATE INDEX idx_story_score ON story(score DESC);
+CREATE INDEX idx_story_created_by ON story(created_by);
+CREATE INDEX idx_comment_story_id ON comment(story_id);
+CREATE INDEX idx_comment_parent_id ON comment(parent_id);
+CREATE INDEX idx_comment_created_by ON comment(created_by);
+CREATE INDEX idx_vote_user_target ON vote(user_id, target_type, target_id);
+CREATE INDEX idx_story_category_news_item ON story_category(news_item_id);
+CREATE INDEX idx_story_category_category ON story_category(category_id);
+
+-- Recreate FTS
+CREATE VIRTUAL TABLE story_fts USING fts5(
+ title, text,
+ content='story',
+ content_rowid='id',
+ tokenize='porter unicode61'
+);
+INSERT INTO story_fts(rowid, title, text) SELECT id, title, text FROM story;
+
+-- Recreate triggers
+CREATE TRIGGER 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 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 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 comment_count_insert AFTER INSERT ON comment BEGIN
+ UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id;
+END;
+
+CREATE TRIGGER comment_count_delete AFTER DELETE ON comment BEGIN
+ UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id;
+END;
+
+CREATE TRIGGER 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 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 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 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;
+
+PRAGMA foreign_keys=ON;
diff --git a/src/migrations.rs b/src/migrations.rs
@@ -3,6 +3,7 @@ use rusqlite::Connection;
const MIGRATIONS: &[&str] = &[
include_str!("../migrations/0001_initial_schema.sql"),
include_str!("../migrations/0002_email_verification.sql"),
+ include_str!("../migrations/0003_strict_tables.sql"),
];
pub fn run(conn: &Connection) -> Result<(), String> {
