admin/_docs/02_document/components/01_data_layer/description.md

Data Layer

1. High-Level Overview

Purpose: Provides database access, ORM mapping, entity definitions, configuration binding, and in-memory caching for the entire application.

Architectural Pattern: Repository/Factory — DbFactory creates short-lived AzaionDb connections with a read/write separation pattern.

Upstream dependencies: None (leaf component).

Downstream consumers: User Management, Authentication & Security, Resource Management.

2. Internal Interfaces

Interface: IDbFactory

Method	Input	Output	Async	Error Types
Run<T>	Func<AzaionDb, Task<T>>	T	Yes	ArgumentException (empty conn string)
Run	Func<AzaionDb, Task>	void	Yes	ArgumentException
RunAdmin	Func<AzaionDb, Task>	void	Yes	ArgumentException

Interface: ICache

Method	Input	Output	Async	Error Types
GetFromCacheAsync<T>	string key, Func<Task<T>>, TimeSpan?	T	Yes	None
Invalidate	string key	void	No	None

Entities

User:
  Id: Guid (PK)
  Email: string (required)
  PasswordHash: string (required)
  Hardware: string? (optional)
  Role: RoleEnum (required)
  CreatedAt: DateTime (required)
  LastLogin: DateTime? (optional)
  UserConfig: UserConfig? (optional, JSON-serialized)
  IsEnabled: bool (required)

UserConfig:
  QueueOffsets: UserQueueOffsets? (optional)

UserQueueOffsets:
  AnnotationsOffset: ulong
  AnnotationsConfirmOffset: ulong
  AnnotationsCommandsOffset: ulong

RoleEnum: None=0, Operator=10, Validator=20, CompanionPC=30, Admin=40, ResourceUploader=50, ApiAdmin=1000

Configuration POCOs

ConnectionStrings:
  AzaionDb: string — read-only connection string
  AzaionDbAdmin: string — admin (read/write) connection string

JwtConfig:
  Issuer: string
  Audience: string
  Secret: string
  TokenLifetimeHours: double

ResourcesConfig:
  ResourcesFolder: string
  SuiteInstallerFolder: string
  SuiteStageInstallerFolder: string

3. External API Specification

N/A — internal component.

4. Data Access Patterns

Queries

Query	Frequency	Hot Path	Index Needed
SELECT * FROM users WHERE email = ?	High	Yes	Yes (email)
SELECT * FROM users with optional filters	Medium	No	No
UPDATE users SET ... WHERE email = ?	Medium	No	No
INSERT INTO users	Low	No	No
DELETE FROM users WHERE email = ?	Low	No	No

Caching Strategy

Data	Cache Type	TTL	Invalidation
User by email	In-memory (LazyCache)	4 hours	On hardware update, queue offset update, hardware check

Storage Estimates

Table	Est. Row Count (1yr)	Row Size	Total Size	Growth Rate
users	100–1000	~500 bytes	~500 KB	Low

Data Management

Seed data: Default admin user (admin@azaion.com, ApiAdmin role) and uploader user (uploader@azaion.com, ResourceUploader role) — see env/db/02_structure.sql.

Rollback: Standard PostgreSQL transactions; linq2db creates a new connection per Run/RunAdmin call.

5. Implementation Details

State Management: Stateless factory pattern. DbFactory is a singleton holding pre-built DataOptions. Cache state is in-memory per process.

Key Dependencies:

Library	Version	Purpose
linq2db	5.4.1	ORM for PostgreSQL access
Npgsql	10.0.1	PostgreSQL ADO.NET provider
LazyCache	2.4.0	In-memory cache with async support
Newtonsoft.Json	13.0.1	JSON serialization for UserConfig

Error Handling Strategy:

• DbFactory.LoadOptions throws ArgumentException on empty connection strings (fail-fast at startup).
• Database exceptions from linq2db/Npgsql propagate unhandled to callers.

6. Extensions and Helpers

Helper	Purpose	Used By
StringExtensions.ToSnakeCase	PascalCase → snake_case column mapping	AzaionDbSchemaHolder
EnumExtensions.GetDescriptions	Enum → description dictionary	BusinessException
QueryableExtensions.WhereIf	Conditional LINQ filters	UserService

7. Caveats & Edge Cases

Known limitations:

• No connection pooling configuration exposed; relies on Npgsql defaults.
• AzaionDbSchemaHolder mapping schema is static — adding new entities requires code changes.
• Cache TTL (4 hours) is hardcoded, not configurable.

Potential race conditions:

• Cache invalidation after write: there's a small window where stale data could be served between the DB write and cache invalidation.

Performance bottlenecks:

• DbFactory creates a new connection per operation. For high-throughput scenarios, connection reuse or batching would be needed.

8. Dependency Graph

Must be implemented after: None (leaf component).

Can be implemented in parallel with: Security & Cryptography (no dependency).

Blocks: User Management, Authentication, Resource Management, Admin API.

9. Logging Strategy

Log Level	When	Example
INFO	SQL trace	SELECT * FROM users WHERE email = @p1 (via linq2db TraceLevel.Info)

Log format: Plaintext SQL output to console.

Log storage: Console (via Console.WriteLine in DbFactory.LoadOptions trace callback).

Modules Covered

• Common/Configs/ConnectionStrings
• Common/Configs/JwtConfig
• Common/Configs/ResourcesConfig
• Common/Entities/User
• Common/Entities/RoleEnum
• Common/Database/AzaionDb
• Common/Database/AzaionDbSchemaHolder
• Common/Database/DbFactory
• Services/Cache