Skip to content
SeedTrust Docs

PostgreSQL Compatibility

This repo now has a dedicated PostgreSQL compatibility loop for the wrapped legacy launch.

Goals

  • catch PostgreSQL blockers in seedtrust_flask/ and seedtrustapi/ before release
  • preserve legacy behavior with public-behavior regression tests
  • stop new MySQL-only code from landing while the legacy stack remains live

Commands

Run the static audit only:

Terminal window
uv run python scripts/postgres_compat_audit.py

Run the audit in fail-on-new-blockers mode:

Terminal window
uv run python scripts/postgres_compat_audit.py --check

Run the combined local workflow:

Terminal window
uv run python scripts/run_postgres_compat_checks.py

Run only the Postgres regression tests:

Terminal window
uv run python scripts/run_postgres_compat_checks.py --tests-only

Legacy Migration Workflow

For the shared legacy schema, treat the working Postgres database as the baseline.

  • Keep Alembic as the migration owner for legacy tables used by seedtrust_flask and seedtrustapi.
  • Keep Goose in new-st limited to experimental/shell concepts only; new-st is not the strategic product destination.
  • Do not replay the full MySQL-era Alembic history to create a fresh Postgres database.

uv run flask db check is now configured to ignore known pgloader representation drift so that the remaining diff output is focused on semantic mismatches:

  • TIMESTAMP WITH TIME ZONE vs legacy DateTime() model declarations
  • native Postgres enums vs equivalent portable SQLAlchemy enums
  • numbered pgloader index names vs model-owned indexes/unique constraints

Fresh Legacy Postgres Bootstrap

These steps change the target database and must be run manually.

  1. Restore a schema-only snapshot from a known-good Postgres legacy database.
  2. Stamp the restored database to the current Alembic head.
  3. Run uv run flask db check and inspect any remaining diffs before creating new migrations.

Example manual commands:

Terminal window
pg_dump --schema-only --no-owner --no-privileges "$SOURCE_DATABASE_URL" > legacy_schema.sql
psql "$TARGET_DATABASE_URL" -f legacy_schema.sql
cd seedtrust_flask && uv run flask db stamp head
cd seedtrust_flask && uv run flask db check

Legacy Schema Change Loop

  1. Update the shared schema/model metadata.
  2. Run cd seedtrust_flask && uv run flask db check.
  3. Audit the remaining diff for semantic changes such as real enum enforcement, uniqueness, or missing columns.
  4. Create the Alembic migration only after the diff is intentional.
  5. Run uv run python scripts/run_postgres_compat_checks.py.

What The Audit Checks

Strict blockers fail the --check command unless they are already tracked in scripts/postgres_compat_allowlist.json.

  • MySQL upserts in runtime Flask code
  • MySQL timestamp math in runtime Flask code
  • MySQL date arithmetic in runtime Flask code
  • FastAPI trigger helpers that still reject PostgreSQL
  • FastAPI trigger SQL that still uses MySQL-only syntax

Advisory categories are reported but do not fail the audit yet.

  • raw SQL execution
  • request id coercion risks
  • string-based boolean handling
  • naive datetime handling

Regression Test Convention

Every PostgreSQL fix should add or update a public-behavior test marked with postgres_compat.

Current marker commands:

Terminal window
cd seedtrust_flask && uv run pytest -m postgres_compat
cd seedtrustapi && uv run pytest -m postgres_compat

Use the marker for tests that protect:

  • route/form coercion
  • boolean behavior
  • naive/aware datetime behavior
  • config URL generation
  • auth/session interoperability
  • PostgreSQL-specific regressions discovered during staging

Iterative Remediation Loop

  1. Run uv run python scripts/postgres_compat_audit.py
  2. Pick one P0/P1 hotspot
  3. Write one failing public-behavior test with @pytest.mark.postgres_compat
  4. Make the smallest fix that restores behavior on PostgreSQL
  5. Run uv run python scripts/run_postgres_compat_checks.py
  6. If the fix removes a strict blocker entirely, delete its entry from scripts/postgres_compat_allowlist.json
  7. If a new strict blocker is discovered, add it to the allowlist with a note and follow-up test target

Current Strict Hotspots

The strict allowlist is currently empty. New strict hits should be treated as regressions.

The current high-risk PostgreSQL areas are still visible in the advisory audit output and should be burned down iteratively.

Staging Lane

Before launch, run the compatibility suite against the real clone database.

Terminal window
uv run st db config set testing-pg --no-interactive
uv run python scripts/run_postgres_compat_checks.py

Then do manual wrapped-shell smoke checks on PostgreSQL:

  1. FastAPI login + 2FA
  2. Flask admin login + dashboard
  3. /api/agency/get
  4. unread message count
  5. one disbursement/ledger flow
  6. one banking/reporting flow

Release Gate

Do not ship the wrapped legacy launch until all are true.

  • scripts/run_postgres_compat_checks.py passes
  • no unowned P0/P1 PostgreSQL blockers remain
  • launch-critical wrapped flows have a postgres_compat regression test
  • staging smoke checks pass against the PostgreSQL clone
  • any deferred issues are documented and outside launch-critical paths