Storage and Persistence

Storage and Persistence

UncannyPrompt currently uses SQL Server as its system of record. The application is designed so that additional infrastructure, such as OpenSearch for search, can be introduced behind application interfaces without changing controllers or domain entities.

SQL Server

SQL Server stores:

• users and external identities;
• tenants, workspaces, projects, folders;
• prompts, prompt versions, notes, tags, favorites;
• variables and protected variable values;
• share grants and public links;
• API keys;
• audit events.

EF Core access lives in src/UncannyPrompt.Infrastructure/Persistence/UncannyPromptDbContext.cs.

Domain entities

Located under src/UncannyPrompt.Domain/Entities:

Area	Entities
Identity	User, AuthIdentity, UserApiKey
Tenancy	Tenant, TenantMembership, Workspace
Organization	Project, Folder
Prompt authoring	Prompt, PromptVersion, PromptNote, VersionLabel
Metadata	Tag, PromptTag, Favorite
Variables	VariableDefinition, VariableValue
Sharing	ShareGrant, PublicShareLink
Audit	AuditEvent

All entities derive from Entity, which provides Id, CreatedAt, and UpdatedAt.

Repositories and unit of work

Application services depend on repository abstractions, not directly on DbContext.

Infrastructure provides:

• EfRepository<T>;
• EfUnitOfWork;
• UncannyPromptDbContext.

This keeps transaction boundaries explicit while still allowing application services to compose LINQ queries that EF translates to SQL.

Migrations

EF Core migrations live in src/UncannyPrompt.Infrastructure/Migrations. Two ways to apply them:

Explicit (recommended for production):

dotnet ef database update \
  --project src/UncannyPrompt.Infrastructure \
  --startup-project src/UncannyPrompt.WebApp

Automatic on startup (default in Development, useful for dev/ephemeral environments):

"Database": {
  "ApplyMigrationsOnStartup": true
}

When Database:ApplyMigrationsOnStartup is omitted, InitializeUncannyPromptDatabaseAndSeedAsync enables it only for Development. Production deployments should set it to false and run migrations explicitly.

InitializeUncannyPromptDatabaseAndSeedAsync in the WebApp runs at bootstrap; it also seeds configured platform owners from SeedOptions:AdminUsers.

Create a new migration:

dotnet ef migrations add <MigrationName> \
  --project src/UncannyPrompt.Infrastructure \
  --startup-project src/UncannyPrompt.WebApp \
  --output-dir Migrations

Review both the generated migration and the model snapshot before committing.

Indexing choices

The schema is tuned for the main read paths:

• prompt listing by project, folder, status, author, and update time;
• version lookup by prompt and version number;
• share grant lookup by target and user;
• public link lookup by indexed TokenLookupHash;
• audit filtering by actor, event type, and time.

Access-control filters should be applied before materialization so SQL Server does the filtering work.

Soft delete

Entities that support soft delete implement ISoftDeletable. Services must filter out deleted rows unless the use case explicitly needs to inspect deleted state.

Soft delete matters especially for sharing and public links: deleted grants and links must not authorize access.

Future search storage

SearchService is intentionally distinct from PromptService, even though the current implementation uses SQL-backed listing. This keeps the door open for a future OpenSearch implementation with ranking, highlights, facets, and larger-scale search behavior.