Rebuild multi-database portability on migration-safe, normalized schemas

[seonghobae]

Buyer-visible outcome

Ship MariaDB/MySQL/PostgreSQL/SQLite support without destructive schema drift, while preserving the useful test-coverage work currently stranded on PR #1.

Why PR #1 cannot be merged as-is

Current exact-head security gates fail on fixable dependency vulnerabilities (brace-expansion, postcss, undici, plus the frontend react-router scan finding). More importantly, the branch changes the production db:migrate:deploy path from prisma migrate deploy to prisma db push --skip-generate, and the PostgreSQL-specific deploy script also uses prisma db push even though the branch adds an explicit PostgreSQL migration. Production deployment must consume reviewed migrations rather than infer drift at runtime.

The added Prisma schemas also preserve physical single-word table names such as users, files, and folders. CWL's database contract requires descriptive two-or-more-word snake_case object names, and the relational model must be reviewed to 3NF rather than replicating JSON/multi-valued attributes across four providers without a normalization decision.

Canonical rebuild plan

Preserve the valuable branch evidence; do not delete PR #1's branch until the replacement work has absorbed the relevant tests and provider deltas.

1. Start from current protected develop.
2. Port the route/unit/integration tests from Add MariaDB/PostgreSQL support and 100% test coverage #1 first and prove RED/GREEN behavior without committing generated coverage artifacts.
3. Raise vulnerable dependencies to patched versions and regenerate lockfiles from the package manager; verify OSV + Trivy on the exact merge ref.
4. Define one provider-neutral logical Prisma model, with generated/provider-specific physical schemas only where Prisma requires it; prevent four manually drifting schema copies.
5. Rename owned physical database objects to descriptive multiword snake_case names through explicit, reversible migrations (for example user_accounts, stored_files, storage_folders or domain-equivalent names), including compatibility/rollback notes.
6. Review multi-valued JSON fields (for example scopes, account-id priority lists, and similar relationship data) for 1NF/3NF violations. Normalize relationships that need relational constraints/queryability; document any justified JSON document fields as atomic values.
7. Use prisma migrate deploy (or provider-equivalent reviewed migration application) for production. prisma db push may be limited to disposable development/test environments and must never be the production deploy path.
8. Test fresh install and upgrade from the currently released schema for MySQL/MariaDB/PostgreSQL/SQLite, including rollback/recovery evidence.
9. Keep exact 100% owned production statement/branch/function/line coverage where tooling exposes it, with beginner-readable docstrings for owned public APIs.
10. Update ARCHITECTURE.md, data model/ERD, migration runbook, CHANGELOG.md, and doctoring references before release.

Required acceptance evidence

• All current-head CI, OSV, Trivy, Semgrep/SAST, dependency-review and repository-required checks pass.
• No production deployment script executes prisma db push.
• Database migration tests prove existing installations can upgrade without data loss.
• Owned database object names satisfy the multiword snake_case rule.
• Schema review demonstrates 3NF for relational entities/relationships, with explicit justification for any atomic JSON document value.
• MySQL, MariaDB, PostgreSQL and SQLite integration suites exercise the same behavioral contract.
• The useful Add MariaDB/PostgreSQL support and 100% test coverage #1 tests are either ported or intentionally retired with a written reason; no coverage claim is accepted merely from line counts.
• Release artifacts include migration/rollback documentation and updated CHANGELOG.

Standards / primary references (APA 7 style for doctoring)

• PostgreSQL Global Development Group. (2026). PostgreSQL documentation: Data definition and constraints. PostgreSQL Global Development Group.
• Prisma Data, Inc. (2026). Prisma Migrate: Deploying database changes with prisma migrate deploy. Prisma documentation.

Use current provider documentation when implementation begins; record exact versions/URLs and APA 7 references in the repository doctoring/traceability material.