reference lifeos pg sql ascii copy json escape bug 20260715T123709
name: lifeos Postgres is SQL_ASCII: psql \copy corrupts JSON unicode escapes description: The lifeos Postgres DB is encoded SQL_ASCII; bulk-loading JSON via psql \copy corrupts/rejects unicode escapes. Base64-encode around it instead. type: reference
The lifeos Postgres database (backing CreatorTrack) is configured with SQL_ASCII encoding, not UTF8. This is distinct from the CT dev-base UTF8 rebuild issue (see reference_creatortrack_dev_base_utf8_rebuild.md), which is about the dev clone image, not the lifeos server itself.
Discovered while restoring a bulk JSON export (CreatorTrack social/scripts transcript corpus, forge/data/creatortrack/social-transcripts-ws85.jsonl): the first restore attempt used psql \copy to bulk-load the JSONL rows back into a table. This corrupted JSON escape sequences on rows containing unicode, and the SQL_ASCII encoding on the server rejected the unicode escapes outright, silently or loudly depending on the row.
Why: SQL_ASCII means Postgres does zero encoding validation/conversion, it passes bytes through as-is. \copy's text-format parsing of JSON escape sequences doesn't survive that combination cleanly, especially for non-ASCII characters embedded in JSON string fields (e.g. transcript text with smart quotes, accented names, emoji).
How to apply: Never use psql \copy to bulk-restore JSON/JSONL data containing non-ASCII text into the lifeos DB. Instead, base64-encode the payload around the copy boundary (decode after INSERT) to sidestep escape-parsing entirely. The working pattern is committed at scripts/forge_creatortrack_transcripts_restore.py (commit 2c32089): base64-encodes around every escaping hazard, only fills missing values (safe to re-run), and was verified with a blank-two-rows-and-refill test under rollback before being trusted. Any future bulk JSON import/restore into lifeos should reuse this base64 pattern rather than re-deriving it, or re-hitting the same corruption.
[auto-memory session 60d132de-4659-485c-8044-427af0ccce7f, confidence 0.72, mode staged]