Prisma migration adds updatedAt/createdAt as NOT NULL without defaults - breaks existing data

[borisno2]

Summary

When migrating from Keystone to OpenSaaS Stack, Prisma generates migrations that add updatedAt and make createdAt required without proper default value handling. This causes migration failures on databases with existing data.

Current Behavior

The generated migration adds updatedAt as NOT NULL without any default:

-- This fails on tables with existing data
ALTER TABLE "Account" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL;
ALTER TABLE "Bill" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL;
ALTER TABLE "BillItem" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL;
-- ... and 17 more tables

Similarly for making nullable createdAt required:

-- This fails if any rows have NULL createdAt
ALTER COLUMN "createdAt" SET NOT NULL;

PostgreSQL Error:

ERROR: column "updatedAt" of relation "Account" contains null values

Expected Behavior

When adding NOT NULL timestamp columns to existing tables, the migration should:

1. Add the column with a default value for existing rows
2. Then optionally remove the default if desired

-- Safe approach
ALTER TABLE "Account" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

Proposed Fixes

Option 1: Schema Generation (Recommended)

When OpenSaaS generates Prisma schemas, include @default(now()) for both fields:

createdAt DateTime @default(now())
updatedAt DateTime @default(now()) @updatedAt  // Add @default(now())

This ensures Prisma generates safer migrations with defaults.

Option 2: Migration Helper

Provide a pre-migration script that adds safe defaults before running the generated migration.

Option 3: Documentation

Document that users must manually edit generated migrations to add defaults when migrating existing data.

Affected Tables

From the emily-calder migration (20 tables total):

• Account, Bill, BillItem, EmailSettings, Enrolment, ImportantDate
• Instrument, Lesson, LessonAttendance, LessonCategory, LessonLength
• LessonSchedule, LessonTerm, Message, Production, Student
• Teacher, Term, User, XeroSettings

Workaround

Edit the generated migration file before running:

-- Change this:
ALTER TABLE "Account" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL;

-- To this:
ALTER TABLE "Account" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

Priority

High - This completely blocks migration on non-empty databases, requiring manual intervention for every migration.

[borisno2]

Workaround Implemented ✅

We found a working workaround using extendPrismaSchema:

// opensaas.config.ts
db: {
  extendPrismaSchema: (schema) => {
    // Add @default(now()) to updatedAt fields so migrations don't fail on existing data
    return schema.replace(
      /updatedAt\s+DateTime\s+@updatedAt/g,
      'updatedAt DateTime @default(now()) @updatedAt',
    )
  },
}

Result: All updatedAt fields now generate with @default(now()) @updatedAt:

updatedAt DateTime @default(now()) @updatedAt

This allows migrations to succeed on existing data without manual SQL edits.

Suggestion for OpenSaaS Stack

Consider adding @default(now()) by default when generating the updatedAt field. This would be a simple change in the field generation logic and would prevent migration failures for all users.

Alternatively, document this workaround in the Keystone migration guide.