adastra_api/docs/decisions/0009-application-model-migration-to-mysql.md

Migrate Application model from MongoDB to MySQL

• Status: accepted
• Date: 2026-04-26

Context and Problem Statement

The Application model (API key management) was stored in MongoDB while user identity (User model, JWT auth) was stored in MySQL. This split the authentication layer across two databases: verifying who a request comes from required MongoDB for API key auth and MySQL for JWT auth. The two-database architecture is otherwise intentional (see ADR 0002) but the auth layer is a special case where consistency matters for security auditability and operational simplicity.

Decision Drivers

• Auth-related models should be co-located to allow transactional integrity (e.g. cascading deletes when a user is removed).
• Security audits are easier when all auth data lives in one queryable store.
• Application.authorId references a MySQL User.id — a foreign key that MongoDB cannot enforce.
• The skydive and Hero Wars domains intentionally remain on MongoDB; Application is not part of those domains.

Considered Options

• Keep Application on MongoDB
• Migrate Application to MySQL

Decision Outcome

Chosen option: "Migrate Application to MySQL", because the auth layer (JWT + API key) is now consolidated on a single database with enforced referential integrity. The skydive and Hero Wars MongoDB collections are unaffected.

Migration implemented via Sequelize migration 20260426000000-add-application.js. The Mongoose model was deleted and replaced with a Sequelize model in src/database/models/mysql/Application.js. The User hasMany Application association is declared in src/database/relationships/index.js.

Positive Consequences

• Auth layer fully on MySQL — a single connection pool and query interface for all auth operations.
• authorId foreign key enforced at the database level (ON DELETE SET NULL).
• Consistent UUID v4 primary keys across all auth models.
• API key hashing algorithm change (ADR 0008) applied cleanly on the new model.

Negative Consequences

• Existing API key hashes stored in MongoDB are invalidated by the migration (different hashing algorithm). Applications must be recreated via the app to generate new keys.
• Requires a Sequelize migration to create the application table before deploying.

Pros and Cons of the Options

Keep Application on MongoDB

• Good, because no migration effort.
• Bad, because auth data is split across two databases — harder to audit and no referential integrity with User.
• Bad, because authorId is an unenforceable soft reference to a MySQL row.

Migrate Application to MySQL

• Good, because auth layer is consolidated — simpler to audit and operate.
• Good, because foreign key to User can be enforced.
• Good, because consistent primary key format (UUID v4) across all MySQL models.
• Bad, because existing MongoDB API key records are invalidated and must be recreated.

Links

• Related to ADR 0002 — general migration strategy (MongoDB → MySQL for CMS/ecommerce domains).
• Related to ADR 0008 — API key hashing algorithm applied on this model.