Fix migration script: cast SQLite integer booleans to Postgres booleans #81

New issue

Closed

opened 2026-03-06 19:19:41 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-03-06 19:19:41 +00:00

Contributor

Plan

plan-2026-02-26-tf-modularize-postgres -- Phase 3, Part D (data migration script)

Repo

forgejo_admin/pal-e-docs

User Story

As a platform operator
I want the SQLite-to-Postgres migration script to handle boolean type differences
So that data migrates cleanly without manual intervention

Context

SQLite stores booleans as integers (1/0). Postgres expects actual booleans (true/false). The migration script (scripts/migrate_sqlite_to_postgres.py) does raw INSERT via text() SQL without type conversion, causing DatatypeMismatch on boolean columns.

Error: column "is_public" is of type boolean but expression is of type integer

Three boolean columns in the schema:

notes.is_public
projects.is_public
users.is_approved

File Targets

Files the agent should modify:

scripts/migrate_sqlite_to_postgres.py -- add boolean conversion for affected columns

Files the agent should NOT touch:

Alembic migrations -- schema is correct, this is a data migration issue

Acceptance Criteria

Migration script converts integer 1/0 to Python True/False for boolean columns before inserting into Postgres
Script handles edge cases: None values in boolean columns should stay None (if nullable)
Migration completes successfully against a clean Postgres database

Test Expectations

Run against Postgres with port-forward: kubectl port-forward -n postgres svc/pal-e-postgres-rw 5432:5432 &
Full command: PALDOCS_DATABASE_PATH=/tmp/pal-e-docs-backup.db PALDOCS_DATABASE_URL=postgresql://paledocs:PfyJMdX4mPJF8eyYS38l@127.0.0.1:5432/paledocs .venv/bin/python scripts/migrate_sqlite_to_postgres.py
Before testing, reset Postgres: PALDOCS_DATABASE_URL=... alembic downgrade base && alembic upgrade head (clean schema)

Constraints

Keep the fix minimal — add a boolean conversion step to row_dicts before insertion
Use bool() conversion or explicit {1: True, 0: False} mapping
The script should auto-detect boolean columns from the Postgres schema, OR hardcode the known list — either approach is fine for this scope

Checklist

PR opened
Tests pass
No unrelated changes

pal-e-docs #79 / PR #80 — Alembic migration fix (just merged)
plan-2026-02-26-tf-modularize-postgres Phase 3 — this blocks data migration

### Plan `plan-2026-02-26-tf-modularize-postgres` -- Phase 3, Part D (data migration script) ### Repo `forgejo_admin/pal-e-docs` ### User Story As a platform operator I want the SQLite-to-Postgres migration script to handle boolean type differences So that data migrates cleanly without manual intervention ### Context SQLite stores booleans as integers (`1`/`0`). Postgres expects actual booleans (`true`/`false`). The migration script (`scripts/migrate_sqlite_to_postgres.py`) does raw `INSERT` via `text()` SQL without type conversion, causing `DatatypeMismatch` on boolean columns. Error: `column "is_public" is of type boolean but expression is of type integer` Three boolean columns in the schema: - `notes.is_public` - `projects.is_public` - `users.is_approved` ### File Targets Files the agent should modify: - `scripts/migrate_sqlite_to_postgres.py` -- add boolean conversion for affected columns Files the agent should NOT touch: - Alembic migrations -- schema is correct, this is a data migration issue ### Acceptance Criteria - [ ] Migration script converts integer `1`/`0` to Python `True`/`False` for boolean columns before inserting into Postgres - [ ] Script handles edge cases: `None` values in boolean columns should stay `None` (if nullable) - [ ] Migration completes successfully against a clean Postgres database ### Test Expectations - [ ] Run against Postgres with port-forward: `kubectl port-forward -n postgres svc/pal-e-postgres-rw 5432:5432 &` - [ ] Full command: `PALDOCS_DATABASE_PATH=/tmp/pal-e-docs-backup.db PALDOCS_DATABASE_URL=postgresql://paledocs:PfyJMdX4mPJF8eyYS38l@127.0.0.1:5432/paledocs .venv/bin/python scripts/migrate_sqlite_to_postgres.py` - [ ] Before testing, reset Postgres: `PALDOCS_DATABASE_URL=... alembic downgrade base && alembic upgrade head` (clean schema) ### Constraints - Keep the fix minimal — add a boolean conversion step to `row_dicts` before insertion - Use `bool()` conversion or explicit `{1: True, 0: False}` mapping - The script should auto-detect boolean columns from the Postgres schema, OR hardcode the known list — either approach is fine for this scope ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - pal-e-docs #79 / PR #80 — Alembic migration fix (just merged) - `plan-2026-02-26-tf-modularize-postgres` Phase 3 — this blocks data migration