fix: serialise mirror Date values in MariaDB-compatible format

TheLastCicada · TheLastCicada · commit 44bcc2afc336 · 2026-04-17T09:41:19.000-07:00
Sequelize's default datetime format "YYYY-MM-DD HH:mm:ss.SSS +00:00"
includes a trailing "+00:00" offset that recent MariaDB strict mode
rejects with:

  Incorrect datetime value: '2026-04-16 22:41:27.490 +00:00'
    for column `cadt_mirror_test`.`audit`.`createdAt` at row 1

Every mirror write touching a DATE/DATETIME column was silently dropped
by the fire-and-forget safeMirrorDbHandler[V2]. This affected both V1
and V2 mirrors in production - the v2 live-api CI run exposed 59 such
errors but still passed because V2 test assertions only check
project/methodology-level records, not audit. V1 live-api verification
added in this PR surfaced the bug directly on the verified records
(projects.projectStatusDate, labels.creditingPeriodStartDate, etc.).

The fix is to set `timezone: '+00:00'` at the Sequelize constructor
level for both `mirror` and `v2Mirror` configs. At the constructor
level (unlike Model.init, where the option is silently ignored),
Sequelize converts Date values to UTC and emits the MariaDB-safe
"YYYY-MM-DD HH:MM:SS[.SSS]" format.

Also updates prepareMysqlMirrorV2's JSDoc to reflect that it does
throw (intentionally) when V1/V2 share a MIRROR_DB name - the earlier
"Never throws" claim was left over from before validateMirrorDbNames
was moved to fail-loud. Both callers (prepareV2Db and
startV2ReconnectBackfill) are wrapped in try/catch at the right level.
diff --git a/src/config/config.js b/src/config/config.js
@@ -61,6 +61,18 @@ export default {
     host: getConfig().MIRROR_DB.DB_HOST || '',
     dialect: 'mysql',
     logging: mirrorLogging,
+    // Tell Sequelize to serialise Date values as UTC without a trailing
+    // "+00:00" offset. Recent MariaDB strict mode rejects the
+    // "YYYY-MM-DD HH:MM:SS.SSS +00:00" format Sequelize emits by default,
+    // with errors like:
+    //   Incorrect datetime value: '2026-04-16 22:41:27.490 +00:00'
+    //     for column `cadt_mirror_test`.`audit`.`createdAt` at row 1
+    // Setting timezone at the Sequelize constructor level (as opposed to
+    // Model.init, where it is silently ignored) produces the MariaDB-safe
+    // "YYYY-MM-DD HH:MM:SS" format. Without this, every mirror write that
+    // touches a DATE/DATETIME column is silently dropped by the
+    // fire-and-forget safeMirrorDbHandler.
+    timezone: '+00:00',
   },
   // V2 Database Configurations
   v2Local: {
@@ -96,5 +108,8 @@ export default {
     host: getConfigV2().MIRROR_DB?.DB_HOST || '',
     dialect: 'mysql',
     logging: mirrorLogging,
+    // See comment on `mirror` above. Same MariaDB strict-mode datetime
+    // issue applies to V2.
+    timezone: '+00:00',
   },
 };
diff --git a/src/database/v2/index.js b/src/database/v2/index.js
@@ -581,8 +581,17 @@ const sweepMirrorOrphansV2 = async (source, mirror, name) => {
  * Ensure the MySQL mirror database exists and has up-to-date migrations.
  * Idempotent: safe to call from startup and again on every reconnect.
  *
- * Returns true on success, false on failure. Never throws so it won't take
- * down the main database path.
+ * Returns true on success, false on transient I/O failure (e.g. MySQL not
+ * reachable, CREATE DATABASE fails, migrations fail). I/O failures are
+ * caught and logged so they don't take down the main database path.
+ *
+ * THROWS (intentionally) when V1 and V2 are misconfigured to share the
+ * same MIRROR_DB.DB_NAME - see validateMirrorDbNames. That is a
+ * programmer/ops error, not a retryable failure, and surfacing it loudly
+ * at CADT startup prevents silent data corruption where V1 and V2 would
+ * clobber each other's rows in the mirror. Callers on both the startup
+ * path (prepareV2Db) and the reconnect path (startV2ReconnectBackfill)
+ * are already wrapped in try/catch at the appropriate level.
  */
 export const prepareMysqlMirrorV2 = async () => {
   if (v2MirrorSetupPromise) {
