feat(db): update language migration files for PostgreSQL 18 optimizations

[davideme]

Context

The canonical schema database/sql/postgresql/schema.sql has been updated for PostgreSQL 18 (see related PR). Since no code has been deployed yet, each language's existing migration files should be edited directly (not have new migration versions appended on top).

Changes to propagate

Three improvements from the canonical schema:

1. Remove uuid-ossp extension — gen_random_uuid() is built-in since PostgreSQL 13
2. Replace three weak indexes with two optimized partial indexes
   • Remove: idx_lamps_is_on, idx_lamps_created_at, idx_lamps_deleted_at
   • Add: idx_lamps_active_created_at_id ON lamps (created_at ASC, id ASC) WHERE deleted_at IS NULL
   • Add: idx_lamps_active_is_on ON lamps (is_on) WHERE deleted_at IS NULL
3. Normalize trigger function name — UPDATE_UPDATED_AT_COLUMN → update_updated_at_column

Files to edit per language

Go (src/go/api/migrations/)

• 000001_initial_schema.up.sql — remove extension, change UUID_GENERATE_V4() → gen_random_uuid(), replace old indexes with two new partial indexes
• 000001_initial_schema.down.sql — update DROP INDEX names to match new index names
• 000002_add_soft_deletes.up.sql — remove CREATE INDEX idx_lamps_deleted_at line
• 000002_add_soft_deletes.down.sql — remove DROP INDEX idx_lamps_deleted_at line
• 000003_add_updated_at_trigger.up.sql — rename function UPDATE_UPDATED_AT_COLUMN → update_updated_at_column
• 000003_add_updated_at_trigger.down.sql — update DROP FUNCTION to use lowercase name

Java (src/java/src/main/resources/db/migration/)

• V1__Initial_schema.sql — remove extension, change UUID_GENERATE_V4() → gen_random_uuid(), replace old indexes with two new partial indexes
• V2__Add_soft_deletes.sql — remove CREATE INDEX idx_lamps_deleted_at line

(Java has no trigger migration — updated_at is managed by @UpdateTimestamp in JPA)

Kotlin (src/kotlin/src/main/resources/db/migration/)

• V1__Initial_schema.sql — same changes as Java V1
• V2__Add_soft_deletes.sql — remove CREATE INDEX idx_lamps_deleted_at line
• V3__Add_updated_at_trigger.sql — rename function UPDATE_UPDATED_AT_COLUMN → update_updated_at_column

Python (src/python/alembic/versions/001_initial_schema.py)

• Remove op.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
• Change server_default=sa.text("UUID_GENERATE_V4()") → server_default=sa.text("gen_random_uuid()")
• Replace the three op.create_index(...) calls with op.execute() for partial indexes (Alembic doesn't support partial indexes natively):

  op.execute("""CREATE INDEX idx_lamps_active_created_at_id
      ON lamps (created_at ASC, id ASC) WHERE deleted_at IS NULL""")
  op.execute("""CREATE INDEX idx_lamps_active_is_on
      ON lamps (is_on) WHERE deleted_at IS NULL""")

• Rename trigger function UPDATE_UPDATED_AT_COLUMN → update_updated_at_column in the op.execute() SQL
• Update downgrade(): replace op.drop_index(...) calls with op.execute("DROP INDEX IF EXISTS ...") for the new names, remove extension drop

TypeScript (src/typescript/prisma/schema.prisma)

• Replace @@index declarations:
  • Remove: @@index([isOn], name: "idx_lamps_is_on")
  • Remove: @@index([createdAt], name: "idx_lamps_created_at")
  • Remove: @@index([deletedAt], name: "idx_lamps_deleted_at")
  • Add: @@index([createdAt, id], name: "idx_lamps_active_created_at_id")
  • Add: @@index([isOn], name: "idx_lamps_active_is_on")
• Important: Prisma doesn't support partial indexes declaratively. After running prisma migrate dev --create-only, manually edit the generated migration SQL to add WHERE deleted_at IS NULL to both index CREATE INDEX statements.

C# (src/csharp/LampControlApi/Infrastructure/Database/)

LampControlDbContext.cs — replace the three HasIndex + HasDatabaseName calls (lines ~63–71) with:

entity.HasIndex(e => new { e.CreatedAt, e.Id })
    .HasDatabaseName("idx_lamps_active_created_at_id")
    .HasFilter("deleted_at IS NULL");

entity.HasIndex(e => e.IsOn)
    .HasDatabaseName("idx_lamps_active_is_on")
    .HasFilter("deleted_at IS NULL");

Migrations/20260123154109_InitialCreate.cs — in Up(), replace the three CreateIndex calls with:

migrationBuilder.CreateIndex(
    name: "idx_lamps_active_created_at_id",
    table: "lamps",
    columns: new[] { "created_at", "id" },
    filter: "deleted_at IS NULL");

migrationBuilder.CreateIndex(
    name: "idx_lamps_active_is_on",
    table: "lamps",
    column: "is_on",
    filter: "deleted_at IS NULL");

Migrations/20260123154109_InitialCreate.Designer.cs — update HasIndex annotations to reflect new names and add filter annotations.

Migrations/LampControlDbContextModelSnapshot.cs — update the three HasIndex entries to the two new indexes with filter annotations.

Verification

For each language, run the existing integration tests against a fresh PostgreSQL 18 instance after applying the updated migrations.