From 7f40b470a7d42a7674cc74309b210a968291ca23 Mon Sep 17 00:00:00 2001
From: ivar <i@oiee.no>
Date: Sat, 11 Apr 2026 00:11:01 +0200
Subject: feat: add initial schema migration files

---
 internal/db/migrations/000001_init.down.sql |  5 ++++
 internal/db/migrations/000001_init.up.sql   | 41 +++++++++++++++++++++++++++++
 2 files changed, 46 insertions(+)
 create mode 100644 internal/db/migrations/000001_init.down.sql
 create mode 100644 internal/db/migrations/000001_init.up.sql

(limited to 'internal')

diff --git a/internal/db/migrations/000001_init.down.sql b/internal/db/migrations/000001_init.down.sql
new file mode 100644
index 0000000..0a59d39
--- /dev/null
+++ b/internal/db/migrations/000001_init.down.sql
@@ -0,0 +1,5 @@
+DROP TABLE IF EXISTS pages_fts;
+DROP TABLE IF EXISTS settings;
+DROP TABLE IF EXISTS redirects;
+DROP TABLE IF EXISTS posts;
+DROP TABLE IF EXISTS pages;
diff --git a/internal/db/migrations/000001_init.up.sql b/internal/db/migrations/000001_init.up.sql
new file mode 100644
index 0000000..a28d2f8
--- /dev/null
+++ b/internal/db/migrations/000001_init.up.sql
@@ -0,0 +1,41 @@
+CREATE TABLE pages (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    path       TEXT NOT NULL UNIQUE,
+    html_path  TEXT NOT NULL,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+CREATE INDEX idx_pages_path ON pages(path);
+CREATE INDEX idx_pages_date ON pages(date);
+
+CREATE TABLE posts (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    slug       TEXT NOT NULL UNIQUE,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    draft      INTEGER NOT NULL DEFAULT 0,
+    blocks     TEXT NOT NULL DEFAULT '[]',
+    updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
+);
+CREATE INDEX idx_posts_slug ON posts(slug);
+CREATE INDEX idx_posts_date ON posts(date);
+
+CREATE TABLE redirects (
+    from_slug TEXT PRIMARY KEY,
+    to_slug   TEXT NOT NULL
+);
+
+CREATE TABLE settings (
+    key   TEXT PRIMARY KEY,
+    value TEXT NOT NULL DEFAULT ''
+);
+
+CREATE VIRTUAL TABLE pages_fts USING fts5(
+    path      UNINDEXED,
+    title,
+    content,
+    tokenize = 'porter unicode61'
+);
-- 
cgit v1.3