Rewrites migration 0009 using SQLite's table-recreation pattern (CREATE → INSERT SELECT → DROP → RENAME) instead of ALTER TABLE ADD COLUMN ... DEFAULT (unixepoch()), which SQLite rejects for expression defaults
Adds migration validation framework (scripts/validate-migrations.ts) with SQLite-specific lint rules and upgrade-path testing with seeded data
Adds runtime repair in src/lib/db/index.ts for users who may have a stale migration record from the broken release
Adds CI step that explicitly validates all migrations on every push/PR
Context
v3.13.0 shipped with a Drizzle-kit auto-generated migration that used ALTER TABLE repositories ADD imported_at integer DEFAULT (unixepoch()) NOT NULL. SQLite rejects this because expression defaults are not allowed in ALTER TABLE ADD COLUMN. This caused all upgrades from v3.12.x to fail at startup (#228, #229).
Prevention layers
Layer
What it catches
When
SQLite lint rules
Invalid SQL patterns (expression defaults, CURRENT_TIMESTAMP in ALTER TABLE)
Before SQL executes
From-scratch execution
SQL syntax errors in the full migration chain
Validates on :memory: DB
Upgrade path + fixtures
Data corruption, missing columns, broken backfills
Tests against real seeded data
Fixture requirement
Forces developer to write upgrade tests for each new migration
Fails if fixture is missing
CI explicit step
All of the above
Every push/PR
Runtime repair
Safety net for users with stale migration records
App startup (one-time, non-fatal)
Test plan
bun test:migrations passes (lint + from-scratch + upgrade path)
bun test src/lib/db/migrations.test.ts passes
Lint rules catch both DEFAULT (expr) and DEFAULT CURRENT_TIMESTAMP in ALTER TABLE
Lint rules don't false-positive on safe patterns (CREATE TABLE, RENAME)
CI workflow runs successfully
Verify upgrade from v3.12.x database works (Docker)
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.
## 📋 Pull Request Information
**Original PR:** https://github.com/RayLabsHQ/gitea-mirror/pull/230
**Author:** [@arunavo4](https://github.com/arunavo4)
**Created:** 3/15/2026
**Status:** ✅ Merged
**Merged:** 3/15/2026
**Merged by:** [@arunavo4](https://github.com/arunavo4)
**Base:** `main` ← **Head:** `fix/migration-0009-sqlite-compat`
---
### 📝 Commits (1)
- [`74f0abe`](https://github.com/RayLabsHQ/gitea-mirror/commit/74f0abe1fb2c0912d717c95cc416c047c98bb94b) fix: rewrite migration 0009 for SQLite compatibility and add migration validation
### 📊 Changes
**6 files changed** (+439 additions, -25 deletions)
<details>
<summary>View changed files</summary>
📝 `.github/workflows/astro-build-test.yml` (+6 -0)
📝 `drizzle/0009_nervous_tyger_tiger.sql` (+148 -23)
📝 `package.json` (+1 -0)
➕ `scripts/validate-migrations.ts` (+212 -0)
📝 `src/lib/db/index.ts` (+46 -2)
➕ `src/lib/db/migrations.test.ts` (+26 -0)
</details>
### 📄 Description
## Summary
- **Rewrites migration 0009** using SQLite's table-recreation pattern (CREATE → INSERT SELECT → DROP → RENAME) instead of `ALTER TABLE ADD COLUMN ... DEFAULT (unixepoch())`, which SQLite rejects for expression defaults
- **Adds migration validation framework** (`scripts/validate-migrations.ts`) with SQLite-specific lint rules and upgrade-path testing with seeded data
- **Adds runtime repair** in `src/lib/db/index.ts` for users who may have a stale migration record from the broken release
- **Adds CI step** that explicitly validates all migrations on every push/PR
## Context
v3.13.0 shipped with a Drizzle-kit auto-generated migration that used `ALTER TABLE repositories ADD imported_at integer DEFAULT (unixepoch()) NOT NULL`. SQLite rejects this because expression defaults are not allowed in `ALTER TABLE ADD COLUMN`. This caused all upgrades from v3.12.x to fail at startup (#228, #229).
## Prevention layers
| Layer | What it catches | When |
|-------|----------------|------|
| SQLite lint rules | Invalid SQL patterns (expression defaults, CURRENT_TIMESTAMP in ALTER TABLE) | Before SQL executes |
| From-scratch execution | SQL syntax errors in the full migration chain | Validates on :memory: DB |
| Upgrade path + fixtures | Data corruption, missing columns, broken backfills | Tests against real seeded data |
| Fixture requirement | Forces developer to write upgrade tests for each new migration | Fails if fixture is missing |
| CI explicit step | All of the above | Every push/PR |
| Runtime repair | Safety net for users with stale migration records | App startup (one-time, non-fatal) |
## Test plan
- [x] `bun test:migrations` passes (lint + from-scratch + upgrade path)
- [x] `bun test src/lib/db/migrations.test.ts` passes
- [x] Lint rules catch both `DEFAULT (expr)` and `DEFAULT CURRENT_TIMESTAMP` in ALTER TABLE
- [x] Lint rules don't false-positive on safe patterns (CREATE TABLE, RENAME)
- [ ] CI workflow runs successfully
- [ ] Verify upgrade from v3.12.x database works (Docker)
Fixes #228
Fixes #229
---
<sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
📋 Pull Request Information
Original PR: https://github.com/RayLabsHQ/gitea-mirror/pull/230
Author: @arunavo4
Created: 3/15/2026
Status: ✅ Merged
Merged: 3/15/2026
Merged by: @arunavo4
Base:
main← Head:fix/migration-0009-sqlite-compat📝 Commits (1)
74f0abefix: rewrite migration 0009 for SQLite compatibility and add migration validation📊 Changes
6 files changed (+439 additions, -25 deletions)
View changed files
📝
.github/workflows/astro-build-test.yml(+6 -0)📝
drizzle/0009_nervous_tyger_tiger.sql(+148 -23)📝
package.json(+1 -0)➕
scripts/validate-migrations.ts(+212 -0)📝
src/lib/db/index.ts(+46 -2)➕
src/lib/db/migrations.test.ts(+26 -0)📄 Description
Summary
ALTER TABLE ADD COLUMN ... DEFAULT (unixepoch()), which SQLite rejects for expression defaultsscripts/validate-migrations.ts) with SQLite-specific lint rules and upgrade-path testing with seeded datasrc/lib/db/index.tsfor users who may have a stale migration record from the broken releaseContext
v3.13.0 shipped with a Drizzle-kit auto-generated migration that used
ALTER TABLE repositories ADD imported_at integer DEFAULT (unixepoch()) NOT NULL. SQLite rejects this because expression defaults are not allowed inALTER TABLE ADD COLUMN. This caused all upgrades from v3.12.x to fail at startup (#228, #229).Prevention layers
Test plan
bun test:migrationspasses (lint + from-scratch + upgrade path)bun test src/lib/db/migrations.test.tspassesDEFAULT (expr)andDEFAULT CURRENT_TIMESTAMPin ALTER TABLEFixes #228
Fixes #229
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.