Skip to content

Database & migrations

The platform runs on a single MySQL 8.0 instance (cm1) on Google Cloud SQL. Every backend service speaks to it through a Cloud SQL Auth Proxy sidecar. Schema is defined as fully-typed SQLAlchemy 2.0 in core/models/, and migrations are applied by a one-shot cashless-migrate Job that ArgoCD runs as a PreSync hook ahead of every backend rollout.

No Postgres / Drizzle in prod

The global CLAUDE.md stack table lists "Drizzle (PostgreSQL)" — that line is aspirational for new greenfield services. Every production workload on dev2.mycashless.com talks to MySQL 8.0, via SQLAlchemy 2.0 + pymysql. Do not assume Postgres semantics (no JSONB, no RETURNING, MySQL collation rules apply).

Stack

Layer Choice
Engine MySQL 8.0 on Cloud SQL (mycashless-pa-development:us-central1:cm1)
Tier db-g1-small today; tier-bump to db-n1-standard-2 is a pre-launch task
Connectivity Cloud SQL Auth Proxy sidecar (gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.14.0) in every pod
ORM SQLAlchemy 2.0 (typed Mapped[...] style) + pymysql driver
Driver URL mysql+pymysql://user:pass@127.0.0.1:3306/<db>
Migration tool Alembic 1.13+
Backups mysqldump daily → GCS (services/cashless-db-backup)

A k8s-native MySQL cluster (Percona Operator, group replication) is on the roadmap as a post-launch follow-up — see .planning/k8s-mysql-migration-plan.md. Until that lands, Cloud SQL cm1 is the source of truth.

Where the schema lives

The cashless-models v2 package (core/models/) is the single source of truth. Models are grouped by feature, not by type:

Package Domain
identity/ Admin, Customer (a.k.a. PAUser), User, Operator, auth
event/ Event, EventConfig, EventAccount, Area, Location, color
ticketing/ Ticket, Token, AccessCode, ReloadAmount
catalog/ Product, Stock, Recipe, Bundle, Category
transaction/ Transaction, Transfer, Order (legacy Sale retired in 034)
payment/ Stripe, Mercado Pago, Adyen, PayPal billing
refund/ Refund, RefundMethod, ListToRefund
device/ Unified Device, DeviceRegistry, Command
nfc/ UserChip, DChip, FPCheckIn, NfcData
marketplace/ Vendor, Runner, TableReservation, TableStage
notification/ Notification
reference/ Country, Currency, Bank, Organization

Foundations live in base.py (DeclarativeBase + UIDMixin, timestamp mixins) and enums.py (every IntEnum). Session management lives in session.py (framework-agnostic SessionManager, replaces the legacy Flask QueryMixin).

The canonical model shape — typed columns, mixin-provided UID, enum-typed status — looks like this (from cashless_models/event/event.py):

from sqlalchemy import ForeignKey, Integer, SmallInteger, Unicode
from sqlalchemy.orm import Mapped, mapped_column

from ..base import Base, UIDMixin
from ..enums import EventStatus, EventType


class Event(UIDMixin, Base):
    __tablename__ = "event"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(Unicode(50), unique=True, nullable=False)
    organization_id: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("organization.id"), default=None
    )
    status: Mapped[EventStatus | None] = mapped_column(Integer, default=None)
    type: Mapped[EventType] = mapped_column(SmallInteger, default=EventType.CASHLESS)
    capacity: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

Migration tool

Alembic 1.13+, configured at core/models/alembic.ini with the script tree at core/models/migrations/. env.py reads DATABASE_URL from the environment and uses Base.metadata from cashless_models as the autogenerate target.

The optional dependency that pulls Alembic in is cashless-models[migrate] (see pyproject.toml). It is not part of the runtime install — only the migrate image installs it.

Authoring a migration

  1. Edit / add a model under core/models/cashless_models/<domain>/.
  2. Create a new revision file under core/models/migrations/versions/, numbered sequentially with a snake-case description: NNN_short_description.py. Match the existing zero-padded 3-digit prefix (the project does not use Alembic's default hex revision IDs — the ID and the prefix are both the same NNN string).
  3. Hand-author upgrade() / downgrade(). Idempotent guards (information_schema lookups before ADD COLUMN, etc.) are the house pattern — partially-migrated environments must replay cleanly. Migration 020 is a good template.
  4. Run locally before pushing: 
    cd core/models
uv pip install -e ".[dev]"
DATABASE_URL='mysql+pymysql://user:pw@localhost:3306/cashless' \
  alembic -c alembic.ini upgrade head
  5. Commit directly to cashless-v2 on the models submodule, then bump the parent monorepo's submodule pointer. No PRs during the pre-launch window — see Getting started. Land it and move on.

pytest in core/models/ runs a 41-test suite that exercises the metadata + a subset of constraint behaviours. Run it before pushing.

cashless-migrate Job

Every backend rollout runs Alembic to head before any backend Deployment sees the new schema. The mechanism is a single-shot Kubernetes Job, packaged as a Helm chart, owned by its own ArgoCD Application.

Pieces

Piece Location
Migration image (cashless-migrate) core/models/Dockerfile.migrate — python:3.12-slim + cashless-models[migrate] + pymysql
Helm chart core/models/charts/cashless-migrate/
ArgoCD Application deploy/argocd/cashless-migrate.yaml
Image-tag pin deploy/argocd/values/cashless-migrate.yaml (auto-bumped by Image Updater)

Triggering

The Application carries argocd.argoproj.io/sync-wave: "-1" and the underlying Job adds:

argocd.argoproj.io/hook: PreSync
argocd.argoproj.io/hook-delete-policy: BeforeHookCreation,HookSucceeded
argocd.argoproj.io/sync-wave: "-1"

That combination guarantees:

  • The Job runs and must succeed before any wave-0 Application (cashless-backend-v2, personalaccount-api-v4, cashless-auth) rolls out new pods.
  • The previous Job is deleted before each new sync (BeforeHookCreation), so re-applies never collide with Kubernetes' "Job is immutable" rule.
  • Successful Jobs auto-clean (HookSucceeded); failed Jobs stick around for ttlSecondsAfterFinished: 86400 (24h) for triage.

What it does

The container's entrypoint is literally alembic upgrade head. The Pod runs the Cloud SQL Auth Proxy as a native sidecar initContainer (restartPolicy: Always — required so the Pod can complete when alembic exits), targeting 127.0.0.1:3306 → mycashless-pa-development:us-central1:cm1. DATABASE_URL is materialized into a hook-scoped Secret by External-Secrets-Operator pulling cashless-prod-cm-database-url from GCP Secret Manager.

Workload Identity (cashless-cloudsql@mycashless-pa-development.iam.gserviceaccount.com) gives the proxy roles/cloudsql.client without static credentials.

On failure

  • backoffLimit: 3 — Alembic gets three swings before the Job fails.
  • activeDeadlineSeconds: 600 — 10-minute hard ceiling per attempt.
  • A failed Job blocks the entire sync wave: backend Deployments do not roll out new pods. Existing pods keep serving the previous schema-pinned image. There is no partial-apply state.
  • Triage path: kubectl -n cashless logs job/cashless-migrate -c alembic.

Idempotency

Migrations are written to be replay-safe: every ADD COLUMN and CREATE INDEX is gated by an information_schema lookup so a re-run against a partially-migrated DB is a no-op. This is the house pattern, not a tool guarantee — review new migrations against it.

Rollback

Forward-fix only. The project posture is pre-launch; no version has shipped to production yet, and re-rolling-back through alembic downgrade is not part of the deploy flow.

Each migration ships a downgrade() for local-dev convenience (verified across all 36 revisions), but:

  • The cashless-migrate Job runs upgrade head exclusively.
  • No automation invokes downgrade.
  • Some migrations are explicitly labelled "no-rollback milestones" once a destructive step has landed — migration 035 is the latest example (legacy per-processor columns dropped after the 034 backfill).

If a migration breaks production, the recovery path is:

  1. Revert the offending revision in the models submodule.
  2. Author a forward-fix migration that undoes the change.
  3. Land it on cashless-v2, bump the pointer, let ArgoCD re-sync.

For total-loss recovery, restore from the most recent cashless-db-backup dump (see Backups below).

Recent migration landmarks

The full list lives at core/models/migrations/versions/. Last ~10 revisions:

Rev Date Title
026 2026-05-10 Add user.token_hash for operator-token hash-at-rest (D8 P0.2)
027 2026-05-10 Drop legacy refund.debit_card_number (PCI P0, D11)
028 2026-05-10 pa_user.clabe_account_encrypted — at-rest encryption (GDPR/PCI)
029 2026-05-10 pa_user.id_number_encrypted — at-rest encryption (GDPR)
030 2026-05-10 NFC enum CHECK constraints
031 2026-05-10 pa_transfer Text → typed schema, Phase 1 (additive)
032 2026-05-10 Promote Command(type=8) to CommandType.DCHIP_RESURRECT
033 2026-05-11 event_account.processor_configs JSON column (Phase 5.1)
034a 2026-05-12 Drop legacy sale / sale_item / sale_log / sale_item_log
034b 2026-05-12 event_account.processor_configs backfill (Phase 5.2)
035 2026-05-12 Drop legacy per-processor credential columns on event_account

Two revision 034s

Two parallel revisions both number themselves 034 — 034_drop_legacy_sale_tables.py and 034_event_account_processor_configs_backfill.py. They are merged on the same down_revision=033 parent. Alembic accepts the branch; 035 is the merge point.

Earlier landmark to know about:

  • 020 (2026-04-30) — table_reservation_columns. Restored 25 columns on table (user_id, stage_id, transaction_uid, online_paid_amount, …) and 4 on table_stage (uid, map, terms_service, refund_policy) that the initial v2 model shape had dropped. Unblocked the A.5.3 table-stages port and removed the AttributeError on m.Table.user_id that was tripping legacy Flask reservation handlers.

Cloud SQL hardening (2026-05)

Companion to migration 020-era schema work, a separate .planning/cloud-sql-hardening-2026-05-10.md captures the instance-level hardening pass:

  • --enable-deletion-protection — applies. Zero cost, no restart.
  • --require-ssl (sslMode=ENCRYPTED_ONLY) — applies after the SSL precheck script confirms every client is TLS-capable.
  • --enable-ha (ZONAL → REGIONAL) — skipped. With the k8s-MySQL migration on the roadmap, doubling the Cloud SQL bill for ~6 weeks is not worth it.

Scripts live at scripts/cloud-sql-harden.sh, scripts/cloud-sql-harden-rollback.sh, scripts/cloud-sql-ssl-precheck.sh.

Connections & pooling

Every FastAPI service uses sync SQLAlchemy + QueuePool (no asyncpg, no create_async_engine). The full audit lives at .planning/connection-pool-2026-05.md. Today's picture:

Service pool_size max_overflow pool_recycle Workers/pod Per-pod cap
cm_v2 10 20 1800 s 2 60
pa_v4 5 10 1800 s 2 30
cashless-auth 10 (hard) 20 (hard) 3600 s 1 30
cashless-worker NullPool — — 4 scripts ~4–8 steady

pool_pre_ping=True is set on every FastAPI service to absorb silently-dropped Cloud SQL Auth Proxy connections. Engines are wrapped in @lru_cache(maxsize=1), so each uvicorn worker has its own pool — the per-pod ceiling is (pool_size + max_overflow) × workers, not just pool_size + max_overflow.

When to bump pool sizes: the cm1 instance defaults to max_connections=50 at db-g1-small. The current pool config is already oversubscribed at HPA max — pre-launch we raise the Cloud SQL flag (or bump the tier) before any service raises its pool_size. See the audit's §4 for per-service recommendations.

TBD

The pre-launch cm1 tier bump (db-g1-small → db-n1-standard-2) and the max_connections flag override are tracked but not yet applied at the time of writing. Operators: verify the live values with gcloud sql instances describe cm1 before changing any pool knob.

Backups

The services/cashless-db-backup chart runs a CronJob that takes a logical dump of the whole database every day and uploads it to GCS.

Setting Value
Schedule 0 3 * * * (03:00 UTC daily)
Tool mysqldump --single-transaction --quick --routines --triggers --events
Output gs://<bucket>/cashless/daily/cashless-<UTC-ts>.sql.gz (gzip-9)
Retention 30 days (older objects pruned at the end of each run)
Hard timeout activeDeadlineSeconds: 7200 (2 h)
Retry budget backoffLimit: 2
History last 7 successful + 3 failed CronJob children kept
Authentication Workload Identity → roles/cloudsql.client + bucket objectAdmin

Restore path (operator runbook):

  1. Pick a dump from the bucket. gsutil ls -l gs://<bucket>/cashless/daily/.
  2. Spin up a target MySQL endpoint reachable from your workstation (Cloud SQL Proxy locally, or a fresh restore instance).
  3. gunzip -c cashless-<ts>.sql.gz | mysql -h <host> -u <user> -p <db>.
  4. Run alembic upgrade head against the restored DB to bring schema in line with current code if the dump pre-dates a migration.

TBD

Point-in-time recovery is not wired up today — only the daily logical dump. PITR via binlog shipping arrives with the k8s-MySQL migration (Percona Operator + XtraBackup). Until then, the RPO is 24 h worst-case.

Source pointers