refactor(data): migrate single-word database objects with zero-downtime compatibility

[seonghobae]

Problem

The current SQLite schema contains single-word database object names that violate the repository's two-or-more-word naming contract and make the future managed-PostgreSQL adapter harder to govern consistently.

Affected tables include:

Current object	Proposed canonical object
users	user_accounts
orgs	organization_records
memberships	organization_memberships
projects	project_records
invites	invitation_records
webhooks	webhook_endpoints
baselines	project_baselines
comments	project_comments
sprints	project_sprints
attachments	project_attachments

Existing compliant objects such as webhook_deliveries, audit_log, api_tokens, project_revisions, share_tokens, and project_seen remain unchanged unless the migration audit finds a semantic collision.

Required migration design

This must be an explicit expand/verify/contract migration, not a search-and-replace PR.

1. Create a versioned migration ledger with a two-word name such as schema_migrations.
2. Back up the SQLite file and verify PRAGMA integrity_check before mutation.
3. Run all renames in one transaction with PRAGMA foreign_keys = ON and modern legacy_alter_table = OFF behavior.
4. Recreate or verify every foreign key, index, trigger, view, prepared statement, query, test fixture, export/import path, and audit reference.
5. Provide temporary compatibility views only when an independently versioned consumer requires them; make those views read-only where SQLite cannot safely emulate writes.
6. Make the data-access boundary select the schema version and fail closed on a partially migrated database.
7. Provide a PostgreSQL migration using the same canonical names and an adapter contract that keeps application modules independent from SQLite-specific SQL.
8. Add downgrade documentation and a restore rehearsal; destructive reverse renames are not a substitute for backup restoration.
9. Update all operational, architecture, database, and integration documentation, plus CHANGELOG.md.
10. Do not combine this migration with feature work or dependency updates.

Realistic verification contract

• Start from a pre-migration database populated with multiple organizations, users, memberships, projects, revisions, comments, sprints, attachments, API tokens, webhooks, deliveries, audit events, invitations, baselines, shares, and seen-state rows.
• Include cascades, nullable ON DELETE SET NULL references, duplicate-prevention constraints, archived projects, and at least one row for every table.
• Snapshot row counts, foreign-key relationships, selected hashes, and API-visible responses before migration.
• Migrate, restart the process, and prove the same customer-visible data and permissions are reproduced.
• Run PRAGMA foreign_key_check and PRAGMA integrity_check after migration.
• Exercise signup/login, organization creation, project CRUD, optimistic version conflicts, revisions, comments, sprints, attachments, webhook delivery records, audit export, share links, and logout-all.
• Run the migration twice and prove idempotence.
• Kill the migration process at deterministic checkpoints and prove restart recovery or fail-closed behavior without mixed-schema service.
• Run the PostgreSQL adapter contract against an ephemeral PostgreSQL instance.
• Require 100% statement/branch/function/line coverage and complete JSDoc for every new migration and adapter module.
• Reject any remaining production table, view, index, trigger, or migration-ledger name that has fewer than two lexical words.

Sequencing

Begin implementation only after the current Hono/attachment-status security train and strict session-revocation train have merged. Keeping this as a dedicated migration avoids destabilizing the active security PRs and gives downstream modules one auditable schema cutover.

Standards and authoritative references

ISO/IEC 9075-1:2023 defines the current SQL framework and terminology used by the SQL standard family. SQLite's current ALTER TABLE ... RENAME TO ... behavior propagates table renames into indexes, triggers, and views when legacy behavior is disabled, but the migration must still verify every object and application query rather than assuming portability.

References (APA 7th)

International Organization for Standardization. (2023). Information technology—Database languages SQL—Part 1: Framework (SQL/Framework) (ISO/IEC Standard No. 9075-1:2023). https://www.iso.org/standard/76583.html

SQLite Consortium. (2026). ALTER TABLE. https://sqlite.org/lang_altertable.html