Database Upsert and Uniqueness Constraints#

The Core Problem: Composite Unique Constraints on Nullable Columns#

PostgreSQL treats every NULL value as distinct, meaning a composite unique index on (projectId, modelName, startDate, unit) does not prevent duplicates when startDate or unit are NULL. Two rows with identical projectId + modelName but NULL startDate satisfy the constraint independently and can coexist.

The models table has exactly this constraint 1:

@@unique([projectId, modelName, startDate, unit])

All four columns are nullable (projectId for system-wide defaults, startDate and unit for models that don't need versioning or unit discrimination).

Sentinel Value Strategy#

To make Prisma's upsert work against this constraint, the upsert mutation in models.ts writes fixed sentinel values for startDate and unit on every new project-scoped model 2:

startDate: new Date("2010-01-01"), // sentinel for uniqueness constraint
unit: ModelUsageUnit.Tokens, // sentinel for uniqueness constraint

This makes both columns non-null and predictable, so ON CONFLICT (projectId, modelName, startDate, unit) fires reliably. Both lines carry inline TODO comments referencing LFE-3229, a planned cleanup to drop startDate/unit from the constraint once the models table is rationalized.

Transactional findFirst + upsert Pattern#

The sentinel value alone is insufficient: two concurrent requests with the same modelName and different generated UUIDs could both pass the constraint check before either commits. Langfuse handles this with an explicit uniqueness check inside a $transaction block before the upsert 3:

1. findUnique by id — verifies the caller owns the model being updated (prevents cross-project mutation).
2. findFirst by { projectId, modelName } — enforces uniqueness on just those two fields, since the DB constraint is not reliable when startDate/unit could be null in legacy rows.
3. upsert by { id, projectId } — performs the actual insert-or-update.

The comment at line 287–290 documents the rationale explicitly:

"The database has a uniqueness constraint on (projectId, modelName, startDate, unit), but this constraint is not enforced when startDate or unit are NULL. We do an explicit check here to ensure uniqueness on just (projectId, modelName)." 4

If the findFirst returns a record with a different id than the one being upserted, a BAD_REQUEST TRPC error is thrown.

Pricing Tier Replacement Inside the Same Transaction#

Tier updates are full replacements, not incremental patches. Within the same transaction that upserts the model record 5:

1. deleteMany all PricingTier rows for the model.
2. create each new tier.
3. create each Price under the new tier.

This avoids partial-update anomalies but means every tier write deletes and re-creates child Price rows. Both PricingTier and Price cascade-delete from Model .

Cache Invalidation#

After every successful upsert or delete, clearModelCacheForProject(projectId) is called outside the transaction to flush the Redis and in-process caches used by findModel() 8. Skipping this step would leave stale pricing data visible until TTL expiry.

Other Nullable-Column Patterns in the Schema#

The same NULL-equality issue appears elsewhere in the schema:

• DefaultView — uses PostgreSQL partial indexes (created in a migration) rather than Prisma @@unique to enforce distinct uniqueness for user-scoped vs. project-scoped defaults: UNIQUE(project_id, user_id, view_name) WHERE user_id IS NOT NULL and UNIQUE(project_id, view_name) WHERE user_id IS NULL 9. This is a cleaner DB-native alternative when Prisma constraints fall short.
• Model.projectId is nullable to represent system-wide defaults (projectId = null = Langfuse-managed model) 10.

Key Files#