| name | new-relational-storage-provider |
| version | 1.0.0 |
| description | Playbook for adding a new EF Core RELATIONAL storage provider to EverTask
(MySQL, MariaDB, Oracle, CockroachDB, …) by inheriting EverTask.Storage.EfCore.
Use when the user asks to add/scaffold/implement a new database storage provider,
port the storage layer to another relational DB, or create an EverTask.Storage.X
package. It encodes the repeatable scaffold AND — critically — the MANDATORY
per-database verification matrix so the agent never assumes "it just inherits the
base": each provider differs on DateTimeOffset/uuid/ExecuteDelete translation,
schema concept, identifier casing, and the Phase-2 hot-write mechanism. Do NOT use
for the in-memory or a non-EF/NoSQL store (that is the custom-storage path).
|
Add a new EF Core relational storage provider to EverTask
EverTask's storage is an EF Core base (EverTask.Storage.EfCore.EfCoreTaskStorage) that already
carries the optimized, server-side implementation any transactional/relational provider can run.
A new provider is mostly a thin shell that inherits it — but only after you PROVE, per database, that
the base's LINQ actually translates server-side. Postgres was nearly free because it is almost
identical to SQL Server; MySQL/Oracle are NOT — they break specific assumptions. This skill makes you
verify each axis instead of assuming.
Golden rule: inherit the base; override ONLY the methods whose LINQ a given provider cannot translate
(the SQLite pattern). Never push a provider workaround into the base. Never declare "zero overrides"
without the captured SQL + a green EfCoreTaskStorageTestsBase run on a real container.
Reference implementations (read these first)
| Reference | What it teaches |
|---|
src/Storage/EverTask.Storage.Postgres/ | Clean relational provider: inherits base with zero overrides, schema-aware migrations (Option B), writable-CTE Phase-2. Closest template for a well-behaved relational DB. |
src/Storage/EverTask.Storage.SqlServer/ | Stored-procedure Phase-2 + schema-aware migration assembly. Template when the DB has no writable CTEs (e.g. MySQL). |
src/Storage/EverTask.Storage.Sqlite/ | The override pattern for constructs a provider can't translate (DateTimeOffset ordering, Take().ExecuteDelete, date-filtered stats). Template for any axis that fails the matrix. |
src/Storage/EverTask.Storage.EfCore/EfCoreTaskStorage.cs + its CLAUDE.md | The base + the "New EF Core Provider" checklist + which methods are virtual. |
src/EverTask/Storage/AuditPolicy.cs | Single source of truth for audit decisions — Phase-2 SQL MUST match it exactly. |
review/postgres-provider-plan.md, review/postgres-provider-decisions.md | A fully worked file-by-file plan + decision matrix to mirror. |
STEP 0 — Mandatory verification matrix (fill this BEFORE scaffolding)
For the target DB, answer every row with evidence (provider docs, EF provider source, a throwaway probe,
or "verify at impl"). This decides which overrides and which Phase-2 mechanism you need. An unverified
row is a landmine.
| Axis | How Postgres answered | What to determine for the new DB |
|---|
| EF provider package + per-TFM versions | Npgsql.EntityFrameworkCore.PostgreSQL 8/9/10 | Which package (e.g. MySQL → Pomelo.EntityFrameworkCore.MySql, not necessarily Oracle's)? Versions aligned to the repo's EF Core pins (net8/9/10) — check nuget; confirm the Microsoft.EntityFrameworkCore.Relational floor ≤ repo pins. |
DateTimeOffset mapping + ordering translation | → timestamptz, all </>/OrderBy/keyset translate server-side → no override | Does the DB have a timezone-aware type? Do RunUntil >= now, the CreatedAtUtc keyset, and the cleanup cutoffs translate? MySQL has no tz type (Pomelo maps to datetime(6)/text) → likely needs the SQLite override pattern for RetrievePending/Cleanup*/stats. |
Guid keyset Id.CompareTo(x) > 0 | uuid >, translates; ordering matches UUIDv7 | Native uuid type? Does Guid.CompareTo translate? Does the stored ordering match the GUID generator? (MySQL: char(36)/binary(16) — verify.) |
Take(n).ExecuteDeleteAsync (cleanup) | translates to LIMIT | Supported? (Postgres/MySQL: DELETE … LIMIT ✓. If not → override Cleanup* with client-side id-resolution like SQLite.) |
| Schema concept | native schema, default public; lowercase default "evertask" | Does the DB have schemas? MySQL: a "schema" IS a database → SchemaName semantics change (closer to SQLite's ""). Oracle: schema == user. |
| Identifier casing / quoting | folds unquoted to lowercase, EF quotes → default lowercase to avoid permanent case-sensitivity | Casing rules + any server setting (MySQL lower_case_table_names is OS-dependent — a real trap). |
GUID generator (UUIDNext.Database.X) | .PostgreSql (v7) — byte-wise uuid sort | Which UUIDNext value matches this DB's PK ordering? NEVER copy .SqlServer (v8) unless the DB sorts like SQL Server. |
| Phase-2 hot-write mechanism | writable CTE (single statement, atomic) | Does the DB support data-modifying CTEs? MySQL CTEs are READ-ONLY → use stored procedures (SqlServer template), not CTEs. Oracle: PL/SQL. |
| Column type mapping | uuid/timestamptz/text/varchar(n)/boolean/bigint IDENTITY | Confirm the scaffolded types are sane; the model uses only HasMaxLength/HasConversion<string> (portable) — check no HasColumnType surprises. |
| Testcontainers module + Respawn adapter | Testcontainers.PostgreSql + DbAdapter.Postgres (+ SchemasToInclude) | Which Testcontainers module + Respawn.DbAdapter? Schema/db inclusion rules for Respawn. |
If ≥2 axes diverge from Postgres, expect a hybrid: SQLite override pattern for the untranslatable
LINQ + SqlServer stored-proc pattern for Phase-2.
STEP 1 — Phase 1: thin provider + GATE
Mirror EverTask.Storage.Postgres/ (or .Sqlite/ if many axes diverge). Files:
*.csproj, GlobalUsings.cs, XTaskStoreOptions.cs (set SchemaName per the matrix), XTaskStoreContext.cs,
DbContextFactoryAdapter.cs, ServiceCollectionExtensions.cs (AddXStorage, UseX(...), GUID generator,
MigrationsHistoryTable(name, schema) if schemas exist), XTaskStorage.cs (empty if zero overrides; else
override ONLY the methods the matrix flagged), TaskStoreEfDbContextFactory.cs (#if DEBUG), CLAUDE.md,
and (if runtime schema needed) a copied DbSchemaAwareMigrationAssembly.cs + hand-edited Initial.
Then wire: Directory.Packages.props (per-TFM provider version), *.slnx, the test .csproj.
Generate the migration with dotnet ef migrations add Initial (DEBUG factory), inspect the types/recovery
index, hand-edit for schema if Option B, then the GATE — do NOT call Phase 1 done until all pass:
dotnet build *.slnx -c Release → 0 warnings (TreatWarningsAsErrors) on net8/9/10.- Write
XEfCoreTaskStorageTests mirroring SqlServerEfCoreTaskStorageTests (Testcontainers module pinned
to a small image, Respawn adapter, GetGuidForProvider, schema assertion). Run the full inherited
EfCoreTaskStorageTestsBase green on a real container.
- Capture the SQL of
RetrievePending and one Cleanup* (.ToQueryString()/logging). Confirm the
keyset and the delete run server-side (no client-eval). If a construct throws / client-evals → add the
minimal override (SQLite pattern) and re-run. Document every override + why.
- Cross-provider tests added in the base suite still pass on the existing providers (no regressions).
STEP 2 — Phase 2 (optional, perf): hot-write optimization
Only if profiling justifies it; the base is already atomic/correct. Override SetStatus,
UpdateCurrentRun, CompleteRecurringRun using the mechanism from the matrix (writable CTE OR stored
procedure OR PL/SQL). Invariants that MUST hold (verify with tests):
- Audit parity with
AuditPolicy — SetStatus: gate from the INPUT status/exception (C# bool is fine).
UpdateCurrentRun: the ErrorsOnly RunsAudit gate depends on the row's Status/Exception → decide it
server-side (never a single C# bool); the audited values are the pre-update row's (a non-mutating
RETURNING/SELECT is faithful). CompleteRecurringRun: audits CONSTANTS (Completed/null) → gate is
C#-computable from the level (StatusAudit at Full; RunsAudit at Full+Minimal).
- Propagation contracts —
SetStatus swallows; UpdateCurrentRun/CompleteRecurringRun rethrow
(Residual D: never advance the schedule on unpersisted state).
- Counter overflow —
+1 on the integer run counter must raise the DB's out-of-range error and roll
the statement back atomically (Postgres: SQLSTATE 22003). Re-add the overflow-propagation test.
- Atomicity — one statement / one transaction: audit insert + row update commit together; a forced
mid-statement failure persists nothing.
- NextRunUtc assigned unconditionally in the recurring completion (a null makes the series terminal).
STEP 3 — Packaging & docs checklist (do NOT skip — "in every form")
Directory.Packages.props: provider version in EACH per-TFM ItemGroup + Testcontainers.X in the test group.*.slnx: add the project; test .csproj: ProjectReference + Testcontainers PackageReference..github/workflows/release.yml: add the dotnet pack src/Storage/EverTask.Storage.X/... line (else it
never ships to NuGet). build.yml builds the slnx automatically; the new tests run on CI if the image is
small (don't exclude them unless the image is heavy like mssql).README.md: NuGet badge, package list, persistence/storage enumerations, install snippet, roadmap.docs/: new docs/storage/x-storage.md (mirror sql-server-storage.md front-matter + nav_order, bump
siblings), and add the provider to EVERY enumeration: storage/overview.md (comparison table + section +
"when to use" + links), storage.md, index.md, getting-started.md, configuration-cheatsheet.md
(method table + SchemaName), configuration-reference.md (AddXStorage API), and remove the DB from any
"implement custom storage for X" list (storage/custom-storage.md).- Root
CLAUDE.md (Key Features, Solution Structure, module table) + EfCore/CLAUDE.md ("future …") +
the new provider CLAUDE.md (operational gotchas only) + CHANGELOG.md (under [Unreleased]).
- Run
/humanizer on the public .md YOU authored (new page, CHANGELOG, README/docs prose) — surgical:
remove AI tells (promotional language, copula avoidance, em-dash/significance inflation), do NOT inject
first-person "personality" into reference docs (it clashes with the sibling pages' house style).
Invariants that must never break (all providers)
RetrievePending recoverable-status filter is duplicated in EfCoreTaskStorage, SqliteTaskStorage
(override), and MemoryTaskStorage — keep all in sync (covered by EfCoreTaskStorageTestsBase).IGuidGenerator picks a DB-appropriate UUIDNext layout so PK order matches insert order (recovery index).- No silent coverage gaps: if a base test can't run on the DB, say so; if you add an override, document
the reason. Never let "tests pass" hide a skipped axis.
Recommended flow for a hard/divergent target (e.g. MySQL)
Treat it like the Postgres effort: (1) write a file-by-file plan + decision matrix (mirror
review/postgres-provider-*.md), (2) optionally run an adversarial review of the plan (see the
adversarial-review skill + Codex as a second opinion), (3) implement Phase 1 to the GATE, (4) decide
Phase 2, (5) packaging/docs, (6) humanize. Expect for MySQL: SQLite-style overrides for DateTimeOffset,
stored procedures (not CTEs) for Phase 2, and SchemaName semantics closer to "no schema".
