Why repositories return dictionaries.

Every repository method returns Dictionary<string, object?>, not a typed DTO. It looks like a regression but it is a deliberate choice that keeps schema migrations rare, JSON shapes evolvable, and the frontend in charge of contracts. Here is the why, and what it costs.

DbContext

backend/src/AgenticIT.Data/AgenticITDbContext.cs — ~95 lines

Around 38 DbSet<T> properties, grouped:

• Identity: Tenants, Users, OAuthAccounts, Sessions
• Domain: Projects, ProjectMembers, ProjectInvitations, Chats, ChatMessages, ChatCompressionStates, ChatViews, ProjectMemories, UserMemories, Artifacts, Folders, Pins, Skills, SkillAttachments
• Features: Suggestions, Reminders, SyncConflicts, ScheduledJobs, GitSyncs, SharePointSyncs
• Integrations / tokens: VBoxTokens, VBoxOrganizationsCache, UserAzureConfigs, JiraTokens, JiraOAuthClients, MsgraphTokens, GitHubUserTokens, SharePointTokens, GitCredentials, ClaudeCodeCredentials, OpenCodeCredentials, UserMcpConnections, UserMcpToolToggles
• Config: ConfigTemplates, ConfigAgents, ConfigTools, ConfigSearches, TenantToolAccess
• Audit: AuditLog, UsageRecords, UserPreferences

OnModelCreating applies all IEntityTypeConfiguration<T> from the assembly, registers a JSON-document converter for the in-memory test provider, and adds a unique filtered index on tenants.name WHERE is_deleted = false (soft-delete awareness).

No migrations

EnsureCreatedAsync() on startup creates any missing tables. New columns need a different approach because EnsureCreatedAsync() does not alter existing tables — so Program.cs contains a stretch of inline ALTER TABLE … ADD COLUMN IF NOT EXISTS statements, one per added field, run on every boot.

The dictionary repository pattern

Every repository surface uses Dictionary<string, object?> (or a list thereof) instead of typed DTOs. The pattern is uniform across all 40+ repositories.

// idiomatic shape
public interface IChatRepository
{
    Task<Dictionary<string, object?>?> CreateAsync(Guid tenantId, Guid userId, …);
    Task<List<Dictionary<string, object?>>> ListAsync(Guid tenantId, Guid? projectId, …);
    Task UpdateAsync(Guid id, Dictionary<string, object?> patch);
    Task<List<Dictionary<string, object?>>> LoadHistoryAsync(Guid chatId);
}

Why

Cheap evolution

Adding a JSON field is one entity property + one inline ALTER. No DTO regen, no contract change.

Envelope versioning

JSONB envelope versioning is owned by §06·Envelopes →.

Loose frontend coupling

The TypeScript layer is the type authority for HTTP contracts; backend doesn't double-define the same shapes.

Snake-case alignment

Dictionary keys are explicit strings, so JsonNamingPolicy.SnakeCaseLower doesn't have to translate names. The dictionary keys are the wire format.

What it costs

• No compile-time guarantee that a field name is correct. Typos surface as runtime 400s.
• IDE autocomplete is gone for response shapes — the price of dynamism.
• Refactors are tedious; every key string needs to be searched in plain text.
• Performance: object-boxing slightly slower than a struct DTO. Negligible at our scale.

The platform offsets these by:

• Strict TypeScript interfaces on the frontend that mirror endpoint shapes.
• Integration tests that assert exact JSON keys for every response that the UI consumes.

Append-only history with concurrency safety

ChatRepository.SaveHistoryAsync()

The chat-message table is append-only. The save is wrapped in a transaction that compares the incoming history length to the row count for that chat_id; only the new tail is inserted. Two concurrent saves that have diverged by one message will collide on the unique index — the loser detaches its stale entry and retries the wrap.

This dance is what keeps two browser tabs from corrupting each other's view of a chat — or worse, from doubling messages when the server retries on socket reconnect.

Multi-tenancy isolation

There are no EF Core global query filters. Tenant scoping is explicit in every repository query, e.g.:

where c.ProjectId == projectId && c.TenantId == tenantId

Why? Because global filters silently apply, and a missed filter is invisible. Explicit filtering forces every new query to acknowledge tenancy in code review. Cascade deletes are wired for project-scoped entities; tenant deletion is soft (is_deleted) to prevent orphan cascade.

Storage details by entity family

Token entities

VBoxTokens, GitHubUserTokens, MsgraphTokens, JiraTokens, SharePointTokens — all encrypted at rest via ITokenEncryptor with the shared TOKEN_ENCRYPTION_KEY (also used by the MCP service for decryption).

Artifact files

Metadata in DB, bytes on disk under {_dataDir}/projects/{projectId}/{artifactId}{ext}. The appdata volume is mounted at /data in both API and MCP containers.

Skills attachments

Inline bytea in skill_attachments.content — no external blob storage. The expected payloads are small (markdown helpers, sample inputs).

Usage records

Append-only. Driven by ICreditsService.RecordUsageAsync().

Audit log

Append-only. AuditLogEntity rows for every tool call: tool_name, tool_input (JSON), tool_result (JSON), approval_status.

EF Core 10 quirks we work around