Data model
Dieser Inhalt ist noch nicht in deiner Sprache verfügbar.
Providers
Section titled “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 applied by DatabaseMigrator — on startup where the process holds a DDL credential, and from a one-shot --migrate-only container in production. A DesignTimeDbContextFactory is included for dotnet ef commands.
Multi-tenant isolation
Section titled “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.
The admin catalogs are the deliberate exception: ServerPlugin and ManagedPersona are shared rows keyed on Id alone, and reach a user through a Group* join rather than through ownership. A shared row therefore has no user key to encrypt with, which is why managed personas are plaintext-only — see Managed personas.
Entities
Section titled “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 |
ServerAssignment |
Id (GUID) |
UserId |
A Pia Mesh assignment — input, status, artifact, spend |
ServerAssignmentEvent |
Id (GUID) |
AssignmentId |
Append-only progress log for one assignment |
ManagedPersona |
Id (GUID) |
— | An admin-authored managed persona — shared, so no UserId |
GroupManagedPersona |
(GroupId, ManagedPersonaId) |
both | Which groups may select a managed persona |
ManagedPersonaPlugin |
(ManagedPersonaId, PluginId) |
both | Which KBs/connectors a managed persona may reach |
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
Section titled “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.
Mesh tables
Section titled “Mesh tables”Two tables back the operator runtime. They exist whether or not it is enabled.
assignments keys on Id alone rather than the usual (Id, UserId) composite, because the Temporal workflow id is derived from the assignment id and carries its own unique index — the id has to be known before the insert, so it is minted by the server rather than defaulted by the database. Isolation is still UserId: every query path filters on it, and cross-user reads return 404.
| Index | Serves |
|---|---|
WorkflowId (unique) |
The enqueue idempotency key |
(UserId, CreatedAt) |
The user’s own list route |
(Status, CreatedAt) |
The admin roll-up and the retention sweep |
OperatorPluginId |
Catalog-row lookups |
InputJson and ArtifactJson are jsonb on PostgreSQL and TEXT on SQLite. Both are plaintext — see the Mesh trust model.
TokensSpent counts what the run’s own steps recorded. TokensAbandoned counts spend from a step that was still in flight when the run ended: that work is billed upstream but its projection write is refused, so it is the one field a terminal row accepts. An aborted assignment’s true cost is the sum.
assignment_events is append-only and cascades on delete. Its rows carry no sequence number; ordering is the composite (AssignmentId, CreatedAt, Id), matching audit_log, guardrail_decisions, login_events and sync_events. Event ids come from the workflow rather than from the database, which is what makes an at-least-once activity retry idempotent instead of duplicating a row. DetailJson (jsonb / TEXT) carries the structured half of a progress row — step index, token delta, resolved tools — and is null on the kinds where nothing structured applies.
Mesh pods and operators are not separate tables. They are Plugins rows discriminated by Kind (connector / operator), with their settings in ConfigJson: a connector row’s transport and identity, an operator row’s chosen skill. Both kinds are excluded from client sync at every projection boundary.
Managed persona tables
Section titled “Managed persona tables”A knowledge base is itself a Plugins row, so ManagedPersonaPlugin covers both KBs and connectors with one join — there is no second table. Its PluginId foreign key is Restrict rather than cascade: a plugin bound to a persona cannot be deleted out from under it. GroupManagedPersona cascades conventionally, matching GroupPlugin.
ManagedPersona carries no EncryptedPayload or WrappedDek column at all, so a managed persona cannot become end-to-end encrypted by accident — the pull projection emits plaintext even to E2EE-enabled accounts.
Refresh tokens
Section titled “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.