Data stores

Three SQL Server databases, one per service that has state. No service reads another's tables. The library set API additionally uses Azure Key Vault (the Key Vault Emulator locally) for one thing: an organization's client certificates. Nothing else in the product uses it.

Context	Connection string	Migrations assembly
AccountContext	AccountsContext	TagShape.Api.Account.Migrations
LibrarySetContext	LibrarySetsContext	TagShape.Api.LibrarySet.Migrations
AuditContext	AuditContext	TagShape.Api.Audit.Migrations

The identity API has no database. It reads what it needs from the account API and signs.

Accounts

Entity
Organization	Name, active flag
OrganizationIdentityProvider	Provider, client ID, client secret, tenant ID
OrganizationBranding	Logo, content type, custom CSS
OrganizationAccountPattern	Email patterns permitting automatic account creation
User	Name, email, organization
UserIdentity	Provider and the provider's identifier for the user
UserProfileImage	Normalised image and its hash
UserApiKey	Key hash, hint, description, created and expiry dates
Group	Name, description
GroupPermission	One permission on a group
UserGroup	Membership

A user's permissions are the union of the permissions of their groups; there is no user-level permission table.

An API key row holds a SHA of the key, never the key. Uniqueness is enforced across every key in the system, not per organization — a key is presented on its own and resolved by hash, so two accounts sharing one would leave that lookup with two answers and no way to choose.

Library sets

Entity
TagGroup	Name, description, version, authorship
Tag	Name, tag group
VariableSet	Name, description, version, authorship
Variable	Name, variable set
VariableValue	Value, sensitive, hint, hash, and its tags
OrganizationCertificate	Slot (primary/replacement), source (generated/uploaded), thumbprint, validity window, Key Vault certificate name

IDs are int and assigned per organization rather than globally, which is why the code takes the next ID explicitly rather than relying on an identity column. OrganizationCertificate is the exception - it keys on a Guid, the same as every other service's certificate-shaped rows, since a slot swap needs both of an organization's rows to update in one SaveChangesAsync, which a per-organization identity column would get in the way of.

Every record carries OrganizationId, and every query filters on it. There is no Organization row here to hang a certificate off, unlike the account API this moved from - see Client certificates.

An OrganizationCertificate row names a certificate that actually lives in Key Vault (KeyVaultCertificateName) rather than holding it. The library set API's Key Vault role grants only certificate operations, never secrets/get — the operation that would be needed to read a certificate's private key back out — so there is no code path in the application capable of retrieving one, not merely no endpoint that happens to return it.

Cascades

• A VariableSet cascades to its Variables and their VariableValues.
• A TagGroup cascades to its Tags.
• Value-to-tag join rows cascade from either side.

This is what lets a replacing import empty a library with two delete statements rather than loading every secret the organization holds only to count it and throw it away.

Audit

One LogEntry table.

Column
Id	Per-organization, and shared across the versions of one entity's history
OrganizationId	Guid.Empty for global entries
LoggedAt, LoggedBy
Version	Which version of the entity this entry describes
EntryType, Action	e.g. Variable, Update
EntityName, EntityId
BeforeState, AfterState	JSON strings

Entries are only ever added. DELETE /{id} removes every version of one entry and is the only removal path.

Migrations

EF Core, with each service's migrations in their own assembly.

# Check whether the model has drifted from the migrations
dotnet ef migrations --project .\src\TagShape.Api.Audit.Migrations\ has-pending-model-changes

# Add one
dotnet ef migrations add <Name> `
  --project .\src\TagShape.Api.Audit.Migrations\ `
  --startup-project .\src\TagShape.Api.Audit.Web\

In Development, each service runs Database.MigrateAsync() at startup, so a local run brings its own schema up to date. Outside development, migrations are applied out of band — efbundle.exe at the repository root is a bundled migration runner for that.

Each DbContext is registered with a scoped context and a singleton options instance. The account and library set contexts enable sensitive data logging, which is worth being aware of when reading logs from an environment holding real secrets.