# 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](../src/UncannyPrompt.Infrastructure/Persistence/UncannyPromptDbContext.cs).

## Domain entities

Located under [src/UncannyPrompt.Domain/Entities](../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](../src/UncannyPrompt.Infrastructure/Migrations). Two ways to apply them:

**Explicit (recommended for production)**:

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

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

```json
"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:

```bash
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.