Schema by declaration, never by migration.

Postgres 16. Around 38 EF Core entities. Schema bootstrapped at startup; new columns added as inline ALTER TABLE … IF NOT EXISTS statements. Tenant isolation is enforced explicitly in every query — there are no global filters and there will never be.

Bootstrap

On startup, Program.cs:

1. Connects to Postgres using DATABASE_URL. ConnectionStringConverter.NormalizePostgres() accepts both postgresql://user:pass@host:port/db and Npgsql Host=…;Port=… formats.
2. Calls EnsureCreatedAsync() — creates any tables that don't exist yet, with the configured indexes and foreign keys.
3. Runs a stretch of inline ALTER TABLE … ADD COLUMN IF NOT EXISTS … statements (~250 lines in Program.cs) to add columns to existing tables since EnsureCreatedAsync won't.
4. Seeds tenants / test users when applicable.

Multi-tenancy enforcement

Most domain entities carry a tenant_id column. There are no global query filters in EF — every repository query explicitly filters on tenant. The reasoning:

• Global filters are silent and easy to mis-bypass.
• Explicit filtering forces every new query to acknowledge tenancy in code review.

Cascade deletes are wired for project-scoped entities — chats, messages, artifacts, pins, and project memory all cascade from a project deletion. Tenant deletion is soft (is_deleted) — preventing an accidental cascade from blowing away an entire tenant's data.

Storage conventions

Snake_case JSON columns

JSONB blobs serialised with JsonNamingPolicy.SnakeCaseLower — same convention as the HTTP layer.

Envelope versioning

Where the JSON schema may grow, we wrap: e.g. chat_messages.content is {"_v": 2, "content": …, "mentions": [...]}. Older rows are unwrapped transparently.

UUID primary keys

Most entities use Guid PKs. A few (oauth_accounts, audit_log, usage_records) use auto-increment bigint for append-only-ness.

Append-only tables

Chat messages, audit log, usage records. Never updated, never deleted (except by cascade).

Soft delete

Only at the tenant level. Other entities are hard-deleted (with cascades).

Indexes worth knowing

• tenants.name — unique filtered index WHERE is_deleted = false. Enables soft-delete + name re-use.
• chat_messages.chat_id — non-unique index for fast history fetch.
• chats (user_id, is_global) — composite for global-chat list lookups.
• artifacts.source_provider — filtered index, used by sync diff.
• skills — partial unique indexes: (tenant_id, slug) WHERE scope='tenant', (owner_user_id, slug) WHERE scope='private'. The same slug can co-exist in private vs tenant scope.
• oauth_accounts (provider, provider_sub) — unique. Prevents duplicate linking.

Concurrency & race conditions

Append-only chat history

Wrapped in a transaction; row-count check decides whether to insert. Concurrent saves race on the unique chat_message PK; loser detaches and retries.

chat_views upsert

Idempotent. Race on insert is caught and translated into an update.

EF Core 10 ghost entries

After a DbUpdateConcurrencyException, the StateManager keeps a ghost. Workaround: _db.ChangeTracker.Clear() before retry. Several repositories do this explicitly.

Scheduled jobs

Status flips active → running → completed. Crash recovery on startup resets stuck running rows to active. No distributed lock — production runs a single API replica for this reason.

Persistence volumes

Where to dive