Data model

Providers

Provider	When	Notes
SQLite	Dev default, tests	Single-file, zero ops
PostgreSQL	Production	UUIDs, JSONB columns, indexed for sync workloads

The provider is chosen from Database:Provider in configuration. Migrations are auto-applied on startup via db.Database.MigrateAsync(). A DesignTimeDbContextFactory is included for dotnet ef commands.

After any change to entity models, DbSet properties, or Fluent API configuration in PiaDbContext, you must generate a new migration:

dotnet ef migrations add <MigrationName> --project src/Pia.Server/Pia.Server.csproj

Skipping this crashes the server on startup with PendingModelChangesWarning.

Multi-tenant isolation

Most user-owned entities use a composite primary key (Id, UserId). All read and write paths must filter by UserId — never query by Id alone, because a colliding Id from another user would cross tenants.

This is enforced in the entity configuration and audited through code review; there is no global query filter automatically applied. Treat any new entity as UserId-scoped unless you have a specific reason not to.

Entities

Entity	PK	FK	Purpose
`PiaUser`	`Id` (GUID)	—	Auth principal
`RefreshToken`	`Id` (GUID)	`UserId`	Token rotation, max 10 per user
`UserSettings`	`UserId`	`UserId`	JSON settings blob
`ServerTemplate`	`(Id, UserId)`	`UserId`	Custom optimization templates
`ServerProvider`	`(Id, UserId)`	`UserId`	AI provider configurations
`ServerSession`	`(Id, UserId)`	`UserId`	Optimization history
`ServerMemory`	`(Id, UserId)`	`UserId`	Context / memory store
`ServerTodo`	`(Id, UserId)`	`UserId`	Todo items
`SyncCursor`	`Id` (GUID)	`UserId`	Per-device sync cursor
`SyncEvent`	`Id` (GUID)	`UserId`	Sync audit trail
`ServerDevice`	`DeviceId` (string)	`UserId`	E2EE device registration (keys, status, fingerprint)
`ServerWrappedUmk`	`(UserId, DeviceId)`	`UserId`	UMK wrapped for a specific device via ECDH

The DTOs that travel over the wire — SyncTemplate, SyncProvider, SyncSession, SyncMemory, SyncTodo — live in Pia.Shared and have explicit, hand-written mappers to/from the entities. There is no AutoMapper.

E2EE fields on `PiaUser`

When a user enables E2EE, the following columns on PiaUser are populated:

IsE2EEEnabled — boolean.
UmkVersion — bumps on UMK rotation.
RecoveryWrappedUmkCiphertext — UMK wrapped with the recovery-derived KEK.
RecoveryKdfSalt, RecoveryKdfMemory, RecoveryKdfTime, RecoveryKdfParallelism — Argon2id parameters.
RecoveryWrapVersion — bumps when the recovery code is rotated.

See E2EE architecture for how these are used.

Refresh tokens

RefreshToken rows store a hashed token plus issue / expiry timestamps. Rotation is performed on every /auth/refresh call: the old row is deleted, a new one inserted. A per-user cap of 10 tokens is enforced — when exceeded, the oldest is pruned. A background RefreshTokenCleanupService removes expired rows.