7 files changed, 87 insertions, 0 deletions
diff --git a/crates/server/migrations/0001_users_sessions.sql b/crates/server/migrations/0001_users_sessions.sql
new file mode 100644
index 0000000..1fe3cee
--- /dev/null
+++ b/crates/server/migrations/0001_users_sessions.sql
@@ -0,0 +1,14 @@
+CREATE TABLE users (
+    id TEXT PRIMARY KEY,
+    username TEXT NOT NULL UNIQUE,
+    password_hash TEXT NOT NULL,
+    created_at TEXT NOT NULL
+);
+
+CREATE TABLE sessions (
+    id TEXT PRIMARY KEY,
+    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    expires_at TEXT NOT NULL
+);
+
+CREATE INDEX idx_sessions_user_id ON sessions(user_id);
diff --git a/crates/server/migrations/0002_test_results.sql b/crates/server/migrations/0002_test_results.sql
new file mode 100644
index 0000000..aa876b5
--- /dev/null
+++ b/crates/server/migrations/0002_test_results.sql
@@ -0,0 +1,13 @@
+CREATE TABLE test_results (
+    id TEXT PRIMARY KEY,
+    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    mode_key TEXT NOT NULL,
+    wpm REAL NOT NULL,
+    raw_wpm REAL NOT NULL,
+    accuracy REAL NOT NULL,
+    time_seconds REAL NOT NULL,
+    created_at TEXT NOT NULL
+);
+
+CREATE INDEX idx_test_results_user_id ON test_results(user_id);
+CREATE INDEX idx_test_results_mode_key ON test_results(mode_key);
diff --git a/crates/server/migrations/0003_friendships.sql b/crates/server/migrations/0003_friendships.sql
new file mode 100644
index 0000000..c44e62a
--- /dev/null
+++ b/crates/server/migrations/0003_friendships.sql
@@ -0,0 +1,11 @@
+CREATE TABLE friendships (
+    id TEXT PRIMARY KEY,
+    requester_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    addressee_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    status TEXT NOT NULL CHECK (status IN ('pending', 'accepted')),
+    created_at TEXT NOT NULL,
+    UNIQUE (requester_id, addressee_id)
+);
+
+CREATE INDEX idx_friendships_requester ON friendships(requester_id);
+CREATE INDEX idx_friendships_addressee ON friendships(addressee_id);
diff --git a/crates/server/migrations/0004_spotify_tokens.sql b/crates/server/migrations/0004_spotify_tokens.sql
new file mode 100644
index 0000000..0129660
--- /dev/null
+++ b/crates/server/migrations/0004_spotify_tokens.sql
@@ -0,0 +1,6 @@
+CREATE TABLE spotify_tokens (
+    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
+    access_token TEXT NOT NULL,
+    refresh_token TEXT NOT NULL,
+    expires_at TEXT NOT NULL
+);
diff --git a/crates/server/migrations/0005_fairness.sql b/crates/server/migrations/0005_fairness.sql
new file mode 100644
index 0000000..28152d6
--- /dev/null
+++ b/crates/server/migrations/0005_fairness.sql
@@ -0,0 +1,2 @@
+ALTER TABLE test_results ADD COLUMN device_type TEXT NOT NULL DEFAULT 'desktop';
+ALTER TABLE test_results ADD COLUMN flagged INTEGER NOT NULL DEFAULT 0;
diff --git a/crates/server/migrations/0006_cosmetics.sql b/crates/server/migrations/0006_cosmetics.sql
new file mode 100644
index 0000000..a795e0b
--- /dev/null
+++ b/crates/server/migrations/0006_cosmetics.sql
@@ -0,0 +1,33 @@
+- Scoped to 'caret' and 'flair' only, not 'theme' - the app already has 7
+- free built-in themes (see web/src/theme.js), and selling some themes
+- while others stay free in the same picker would be a confusing mix of
+- paid and unpaid options in one list. Caret color and a small username
+- flair badge are both genuinely new, non-overlapping cosmetic slots.
+CREATE TABLE cosmetics (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    category TEXT NOT NULL CHECK (category IN ('caret', 'flair')),
+    price_cents INTEGER NOT NULL,
+    - Caret: a CSS color. Flair: a short glyph/emoji shown next to a
+    - username on the leaderboard and public profile.
+    value TEXT NOT NULL
+);
+
+CREATE TABLE user_cosmetics (
+    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    cosmetic_id TEXT NOT NULL REFERENCES cosmetics(id) ON DELETE CASCADE,
+    acquired_at TEXT NOT NULL,
+    PRIMARY KEY (user_id, cosmetic_id)
+);
+
+ALTER TABLE users ADD COLUMN equipped_caret TEXT;
+ALTER TABLE users ADD COLUMN equipped_flair TEXT;
+
+- A small seed catalog so the store isn't empty on a fresh install.
+INSERT INTO cosmetics (id, name, category, price_cents, value) VALUES
+    ('caret-magenta', 'Magenta Caret', 'caret', 199, '#ff2fb0'),
+    ('caret-amber', 'Amber Caret', 'caret', 199, '#ffb703'),
+    ('caret-cyan', 'Cyan Caret', 'caret', 199, '#00e5ff'),
+    ('flair-star', 'Star', 'flair', 149, 'star'),
+    ('flair-bolt', 'Bolt', 'flair', 149, 'bolt'),
+    ('flair-skull', 'Skull', 'flair', 149, 'skull');
diff --git a/crates/server/migrations/0007_api_tokens.sql b/crates/server/migrations/0007_api_tokens.sql
new file mode 100644
index 0000000..61b6697
--- /dev/null
+++ b/crates/server/migrations/0007_api_tokens.sql
@@ -0,0 +1,8 @@
+- Long-lived personal-access tokens for CLI-style clients (the TUI) that
+- have no cookie jar. Separate from the browser's session cookie so
+- revoking one doesn't touch the other.
+CREATE TABLE api_tokens (
+    token TEXT PRIMARY KEY,
+    user_id TEXT NOT NULL REFERENCES users(id),
+    created_at TEXT NOT NULL
+);