PostgresDdlGenerator lowercases CHECK constraint identifiers but not column names

[MelbourneDeveloper]

Bug

PostgresDdlGenerator.GenerateColumnDef creates columns with their original case (e.g. "Gender", "Status"), but passes CHECK constraint expressions through LowercaseQuotedIdentifiers which converts all double-quoted identifiers to lowercase (e.g. "gender", "status").

In PostgreSQL, quoted identifiers are case-sensitive. "Gender" and "gender" are different columns. This causes CREATE TABLE to fail with:

42703: column "gender" does not exist
42703: column "status" does not exist

Root Cause

PostgresDdlGenerator.cs:210-213 — the LowercaseQuotedIdentifiers call lowercases the column reference inside the CHECK expression, while the column definition on line 174 preserves original case.

// Line 174: column name keeps original case
sb.Append($"\"{column.Name}\" ");

// Line 212: but CHECK constraint identifiers get lowercased
var constraint = LowercaseQuotedIdentifiers(column.CheckConstraint);

Generated DDL:

CREATE TABLE "public"."fhir_patient" (
  "Gender" TEXT NOT NULL CHECK ("gender" IN ('male', 'female', 'other', 'unknown'))
  --  ^^^^^^                      ^^^^^^
  --  original case               lowercased — MISMATCH!
);

Impact

• Clinical.Api crashes on startup: column "gender" does not exist
• Scheduling.Api crashes on startup: column "status" does not exist
• Any table with CHECK constraints referencing quoted column names will fail on Postgres

Broader Issue

This is a symptom of an inconsistent casing strategy in DDL generation. The generator lowercases:

• Table names (line 91: table.Name.ToLowerInvariant())
• CHECK constraint identifiers (line 212: LowercaseQuotedIdentifiers)

But does NOT lowercase:

• Column names in definitions (line 174)
• Primary key column references (line 109)
• Foreign key column references (line 119)

The platform needs a consistent strategy: either preserve original case everywhere, or lowercase everything. A mismatch between any of these causes PostgreSQL failures since all identifiers are quoted.

Immediate Fix

1. Removed LowercaseQuotedIdentifiers from CHECK constraint generation
2. Fixed scheduling-schema.yaml check constraints that had manually-lowercased identifiers

Labels

bug, migration, postgres