Ark.Rapid.Database 0.0.23

Ark.Rapid.Database

1. Install

dotnet add package Ark.Rapid.Database

That's it — no further setup. AI-SPEC.md, ai-spec.json, IMPLEMENTATION.md, and VALIDATION.md are copied automatically into your own project's build output on the next build — e.g. bin/Debug/net9.0/Ark.Rapid.Database/ if your agent is scoped to this project's folder, or <YourProject>/bin/Debug/net9.0/Ark.Rapid.Database/ if it's scoped to a multi-project solution root instead — so an AI coding agent working in your repo can read them locally without any network access. The prompts below assume that's already happened — build once after installing, then hand one of these to your agent.

2. Prompts for your AI coding agent

Paste one of these into Claude, Codex, Copilot, or any other coding agent working in your repo. Each one tells the agent exactly which bundled file to read first, so it grounds the generated code in this version's real behavior instead of guessing.

If your agent reports the file doesn't exist: bin/ and obj/ are almost always git-ignored, and many agents' code-search tools skip ignored paths by default — so a search can come back empty even right after a successful build. The **/bin/**/... glob below is deliberate: it resolves whether the agent is scoped to this project's own folder or to a multi-project solution root. If it still can't find the file, tell it to search the filesystem directly (e.g. a plain find/glob/ls call) instead of its indexed or .gitignore-aware search, or just give it the resolved path once you've located it yourself.

Wire up configuration + dependency injection

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md sections 1-2 in this project. Add an
ArkDatabaseOptions/ArkDatabaseConnectionOptions config model, an AddArkRapidDatabase(IConfiguration)
DI extension, and an "ArkDatabase:Databases" section in appsettings.json for a database named
"default" using <SQLite|MariaDB|PostgreSQL>. Wire it into Program.cs.

Migrate an existing database to Ark.Rapid.Database

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md sections 1-6 and 8, and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 8.1, in this project. I'm moving <existing
data-access code, e.g. "EF Core against SQL Server" or "raw ADO.NET against MySQL"> to
Ark.Rapid.Database targeting <SQLite|MariaDB|PostgreSQL>. For each existing table, map its
columns to the DataType/Constraint conventions in AI-SPEC.md section 8.1 and generate an
equivalent static schema class with an EnsureCreatedAsync method, then rewrite the existing
CRUD code to go through ArkDbManager using the config/DI pattern from sections 1-2 and the
provider-portable insert/read pattern from section 6. Do not port any hand-built SQL string
concatenation as-is — rebuild it using InsertTableAsync/UpdateTableAsync's dictionaries or
GetSqlValueAsync, per the security checklist in section 8.

Define a table schema and keep it idempotent

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 3-4 in this project. Generate a static
schema class for a table named "<table>" with columns: <column: type, column: type, ...>, plus an
EnsureCreatedAsync method, following the ColumnProp/Constraint conventions in
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 8.

Add a column safely, later

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 5.1 in this project. Add a migration step
that adds a "<column>" column of type <type> to the "<table>" table, appended to the existing
migration list so it's safe to run on every startup.

Change a column's type or default, via ModifyColumnAsync or expand/contract

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 5.3 in this project. Decide whether a
single ModifyColumnAsync call is enough to change "<table>.<column>" from <old type>/<old default>
to <new type>/<new default>, or whether the expand/contract pattern (backfill statement plus a
follow-up drop step for the retired column, spread across deploys) is the better fit — a large
PostgreSQL table or a need for a safe rollback window are the two reasons to prefer expand/contract
over ModifyColumnAsync's single, immediate, full-column-rewrite call. Write whichever migration.

Generate a repository class

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 9 and **/bin/**/Ark.Rapid.Database/AI-SPEC.md section 7.1 in this
project. Generate a repository class for "<Entity>" backed by ArkDbManager, with Create/Update/Get
methods that stay correct across SQLite, MariaDB, and PostgreSQL (do not cast InsertTableAsync's
return value directly).

Add vector search

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 7 and **/bin/**/Ark.Rapid.Database/AI-SPEC.md section 9 in this
project. Add an "embedding" BLOB column to "<table>" and a similarity-search method, gated so it
only runs against SqliteManager.

Write a portable raw SQL query (aggregates, dates, JSON, paging) that runs unmodified on SQLite, MariaDB, and PostgreSQL

Read **/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Before hand-writing any
dialect-specific SQL fragment inside a raw query string passed to ExecuteAsync/ExecuteSelectAsync/
SelectAsync/FirstAsync/ExecuteScalarAsync, check whether one of these <db>.Util (DatabaseUtil)
methods already covers it, and use the method instead of the literal syntax:

- Qry_Now() / Qry_UtcNow() — current timestamp, local or UTC
- Qry_Date_Day(days) — a date N days in the past/future
- Qry_To_Date(col) — cast/truncate a column to a date
- Qry_IfNull(col, default) — null-coalesce
- Qry_Sum(col) — aggregate sum
- Qry_Greatest(...values) / Qry_Least(...values) — row-wise (scalar) greatest/least of 2+
  expressions, e.g. clamping a shortfall at zero: Qry_Greatest("0", "required - filled"). This is
  NOT the same as the aggregate MAX()/MIN() over rows. Mind the per-provider NULL-handling
  differences documented on the method.
- Qry_Bool(bool) — a boolean literal safe for a native/conventional boolean column (required on
  PostgreSQL, whose boolean type rejects bare 1/0); do not confuse with GetSqlValueAsync(bool),
  which targets columns this library itself created via CreateTableAsync.
- Qry_Concat(...values) — string concatenation (mind the per-provider NULL-handling differences
  documented on the method)
- Qry_Limit(limit, offset) — LIMIT/OFFSET paging; on the SQL Server branch this requires the query
  to already have an ORDER BY
- Qry_Diff_Hrs(end, start) / Qry_Diff_Mins(end, start) / Qry_Diff_Days(end, start) — datetime
  difference. Qry_Diff_Days is always fractional on every provider (unlike Qry_Diff_Hrs/
  Qry_Diff_Mins, which truncate to a whole HOUR/MINUTE on MariaDB/mssql) — use it for duration/
  average reports (e.g. "average days a request has stayed open") where whole-day truncation would
  lose most of the signal.
- JsonExtract(jsonCol, path) — extract a field from a JSON column

Only fall back to writing the dialect-specific SQL yourself, gated per provider, if none of these
cover what you need — and if so, consider adding a new Qry_* method to DatabaseUtil.cs instead of
inlining it at the call site, so the next query gets it for free.

The prompt above is the general-purpose one — it works for any raw query. For a specific, common scenario, these narrower prompts point the agent straight at the matching worked pattern in IMPLEMENTATION.md section 10, so it doesn't have to compose the fragments itself:

Relative-date filter (e.g. "records from the last N days")

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.1 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that returns rows where "<date_column>" falls within the last <N> days, using
db.Util.Qry_Date_Day(-<N>) for the cutoff and db.Util.Qry_To_Date("<date_column>") if
<date_column> is a datetime column being compared against a date boundary. Run it through
ExecuteSelectAsync<<Entity>> and order by <date_column> descending.

Null-safe default / coalesce

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.2 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that selects "<column>" but substitutes <default_value> when it's NULL, using
db.Util.Qry_IfNull("<column>", "<default_value>"). Quote <default_value> yourself if it's a
string literal (e.g. "'unknown'") — Qry_IfNull interpolates it as-is.

Aggregate sum / totals report

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.3 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that groups by "<group_by_column>" and returns the sum of "<amount_column>" as
"<alias>", using db.Util.Qry_Sum("<amount_column>") wrapped in db.Util.Qry_IfNull(..., "0") so
groups with only NULL values report 0 instead of NULL. Run it through db.SelectAsync since the
result is an ad hoc aggregate shape, not an existing entity type.

Clamp a computed value to a floor or ceiling (row-wise, not an aggregate)

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.4 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that computes "<expression>" (e.g. "<required_column> - <filled_column>") per row and
clamps it to a minimum of <floor> using db.Util.Qry_Greatest("<floor>", "<expression>") — or a
maximum of <ceiling> using Qry_Least instead. If either input column can be NULL, wrap each one
individually in db.Util.Qry_IfNull(..., "0") before passing it to Qry_Greatest/Qry_Least, per the
per-provider NULL-handling table in AI-SPEC.md section 11 — do not skip this if the columns are nullable.

Filter or write against a hand-modeled boolean column

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.5 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that filters "<bool_column>" to <true|false> using db.Util.Qry_Bool(<true|false>) as
the literal, e.g. `WHERE <bool_column> = {db.Util.Qry_Bool(true)}`. First confirm <bool_column>
is an existing/hand-modeled boolean or 1/0 integer column, not one created by this library's own
CreateTableAsync with ColumnProp.DataType = typeof(bool) — that case needs
await db.GetSqlValueAsync(<true|false>) instead; the two are not interchangeable for the same
column (see AI-SPEC.md section 11).

Build a display or search string from multiple columns

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.6 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that projects "<alias>" as the concatenation of <column1>, a literal separator, and
<column2> (e.g. first/last name), using db.Util.Qry_Concat("<column1>", "' '", "<column2>"). If
this is also used in a WHERE ... LIKE clause against a caller-supplied search term, inline the
term via `await db.GetSqlValueAsync($"%{<searchTerm>}%")` — never interpolate <searchTerm>
directly into the query string (AI-SPEC.md section 7).

Paginated listing

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.7 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a paginated query
against "<table>" for page <page> of size <pageSize>, ordered by "<order_column>", using
db.Util.Qry_Limit(<pageSize>, (<page> - 1) * <pageSize>) appended after the ORDER BY clause. Every
provider requires the ORDER BY to be present for this to be deterministic, so don't omit it even
though only the SQL Server branch enforces it structurally.

Elapsed time / duration report between two datetime columns

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.8 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that returns the <hours|minutes> elapsed between "<start_column>" and "<end_column>" as
"<alias>", using db.Util.Qry_Diff_Hrs("<end_column>", "<start_column>") or
db.Util.Qry_Diff_Mins("<end_column>", "<start_column>") — note the argument order is (end, start).
If aggregating this across rows (e.g. total hours per <group_by_column>), wrap it in
db.Util.Qry_Sum(...) and then db.Util.Qry_IfNull(..., "0") so groups with no rows report 0. If the
unit you actually need is fractional days (e.g. an average duration), use
db.Util.Qry_Diff_Days(...) instead — see the next prompt below.

Convert a hand-written duration/date-diff expression (e.g. PostgreSQL EXTRACT(EPOCH FROM ...)) to a portable, fractional-day average or duration report

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.11 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. I have this existing raw SQL
query that only works on one provider (paste the query). Rewrite it so it runs unmodified on
SQLite, MariaDB, and PostgreSQL:

1. Replace any hand-written datetime-difference expression (e.g. PostgreSQL's
   `EXTRACT(EPOCH FROM (a - b))/<divisor>`, or an equivalent on another provider) with
   db.Util.Qry_Diff_Days("<end_expr>", "<start_expr>") if the result is reported as a fractional
   day count (an average, or any duration where truncating to whole days would lose most of the
   signal — e.g. "average days a request has stayed open"), or db.Util.Qry_Diff_Hrs/Qry_Diff_Mins
   (IMPLEMENTATION.md section 10.8) if whole-unit truncation is fine instead. Note the argument
   order is (end, start) for all three. If "now" is one side of the diff, use db.Util.Qry_UtcNow()
   rather than a provider-specific current-timestamp expression.
2. Replace any other dialect-specific fragment in the same query the same way — e.g. PostgreSQL's
   `GREATEST(a, b)`/`LEAST(a, b)` with db.Util.Qry_Greatest("<a>", "<b>")/Qry_Least(...)
   (IMPLEMENTATION.md section 10.4), and `COALESCE(col, default)` with
   db.Util.Qry_IfNull("<col>", "<default>") (section 10.2) — per the full helper list in
   AI-SPEC.md section 11.
3. Leave ANSI-standard constructs alone (COUNT, SUM, AVG, CASE WHEN, EXISTS/NOT EXISTS, IN, JOIN) —
   no Util wrapper exists or is needed for them.
4. If any comparison value in the query is caller-supplied or computed at runtime (a cutoff date, a
   threshold, a search term) rather than a fixed constant, replace it with
   await db.GetSqlValueAsync(<value>) instead of leaving it as a literal in the SQL text, per the
   security checklist in IMPLEMENTATION.md section 8.

Run the result through db.SelectAsync unless its shape matches an existing entity type, in which
case use db.ExecuteSelectAsync<<Entity>> instead.

Query a field inside a JSON column

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.9 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. Write a query against
"<table>" that filters or projects the "<json_path>" field of the JSON column "<json_column>",
using db.Util.JsonExtract("<json_column>", "<json_path>"). If comparing it against a literal
value, inline that value via `await db.GetSqlValueAsync(<value>)` rather than a bare string.

Combined multi-helper report (the common real-world case)

Read **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md section 10.10 and
**/bin/**/Ark.Rapid.Database/AI-SPEC.md section 11 in this project. I need a report query
against "<table>" grouped by "<group_by_column>" that computes <describe the metric — e.g. "total
hours between planned_start and planned_end, defaulting to 0" or "open positions clamped at
zero">. Compose it from the matching db.Util.Qry_* helpers (Qry_Sum, Qry_IfNull, Qry_Diff_Hrs,
Qry_Greatest, Qry_Date_Day, Qry_To_Date, Qry_Concat, JsonExtract, Qry_Bool, Qry_Limit as needed)
rather than hand-writing any dialect-specific SQL, nesting them in the order the underlying value
flows (e.g. Qry_IfNull(Qry_Sum(Qry_Diff_Hrs(end, start)), "0")). Run it through db.SelectAsync
unless the shape matches an existing entity type, in which case use
db.ExecuteSelectAsync<<Entity>> instead.

Validate a query or repository method against every known cross-provider failure mode

Read **/bin/**/Ark.Rapid.Database/VALIDATION.md in this project — it is a pass/fail checklist
(V1, V2, ...) of every known failure mode in this library, each with a Trigger/Symptom/Root
cause/Fix and a cross-reference to the fuller explanation in AI-SPEC.md/IMPLEMENTATION.md. Check
<the query, repository method, or file — paste it, or name it> against every item whose "Trigger"
matches something in it: any GROUP BY combined with a JOIN (V1), any cast of
InsertTableAsync/ExecuteQueryAsync's return value (V2), any FirstAsync<T> call on PostgreSQL whose
caller treats a default/null result as "no row" (V3), any IsTableExistAsync precheck before
CreateTableAsync (V4), any ColumnProp.Default/ConstraintName usage (V5/V6), any nullable
ColumnProp.DataType (V7), any Qry_Greatest/Qry_Least/Qry_Concat call with a possibly-NULL operand
not wrapped in Qry_IfNull (V8), any Qry_Bool/GetSqlValueAsync(bool) usage against a boolean column
(V9), any hand-written PostgreSQL datetime subtraction (V10), any reliance on CreateSchemaAsync
provisioning errors surfacing (V11), any mixed-case identifier read through
GetSafeTableName/GetSafeColumnName (V12), any GetSchemasAsync(partial_name) call expecting
filtering (V13), any ModifyColumnAsync call (V14), any external/caller-supplied value not
routed through GetSqlValueAsync (V15), and — if V1 applies — whether the joined table's id column
actually has a real PostgreSQL PRIMARY KEY, since a table created with Constraint.Primary_AutoIncrement
before package version 0.0.20 does not, and V1's fix silently does nothing on it (V16). For every item that matches, read its full write-up in
VALIDATION.md (and the AI-SPEC.md/IMPLEMENTATION.md section it references) before deciding whether
it's actually a problem here, then fix it using the pattern shown there. Report which items you
checked, which ones actually applied, and what you changed for each — including ones you confirmed
don't apply and why, so nothing was skipped rather than checked.

Fix a GROUP BY 42803 that persists after applying VALIDATION.md V1's fix (PostgreSQL)

Read **/bin/**/Ark.Rapid.Database/VALIDATION.md sections V1 and V16, and
**/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md sections 10.12-10.13, in this project. I'm getting
Npgsql.PostgresException 42803 ("column ... must appear in the GROUP BY clause or be used in an
aggregate function") on this query even though I've already added the joined table's own id
column to GROUP BY (paste the query). This is expected if the id column was created with
Constraint.Primary_AutoIncrement by an Ark.Rapid.Database build before 0.0.20 — that version never
gave such a column a real PostgreSQL PRIMARY KEY, so grouping by it doesn't satisfy the
functional-dependency rule V1's fix depends on (VALIDATION.md V16). Rewrite the query to list
every selected non-aggregate column explicitly in GROUP BY (the IMPLEMENTATION.md §10.13
workaround) so it works regardless of whether that constraint exists. Then check every other raw
GROUP BY query in this project for the same pattern (a driving-table GROUP BY plus a JOINed
table's other columns selected ungrouped) and apply the same fix to each one you find — do not
stop at the query I pasted. Report the Ark.Rapid.Database package version this project references
and note, separately, that upgrading to 0.0.20+ only gives newly created tables a real primary
key — existing tables need an explicit `ALTER TABLE <table> ADD PRIMARY KEY (<id_column>);`
migration per table if the shorter V1 form is wanted back; do not run that migration yourself
without being asked to, since it changes a live schema.

Sweep the whole project: migrate every existing raw SQL query to Ark.Rapid.Database's portable helpers

Read **/bin/**/Ark.Rapid.Database/AI-SPEC.md sections 7 and 11 and
**/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md sections 6 and 10 in this project. Find every raw
SQL string this project already executes — through ArkDbManager's ExecuteAsync/ExecuteSelectAsync/
SelectAsync/FirstAsync/ExecuteScalarAsync, and any leftover ADO.NET/Dapper/EF Core raw-SQL calls
not yet routed through ArkDbManager at all — and convert each one so it runs unmodified on SQLite,
MariaDB, and PostgreSQL. For every query found:

1. Identify each dialect-specific fragment (current date/time, relative-date filters, date
   truncation, NULL-coalescing, SUM/aggregate totals, row-wise clamping, boolean literals, string
   concatenation, LIMIT/OFFSET paging, datetime differences, JSON field access) and replace it
   with the matching db.Util.Qry_* method — Qry_Now/Qry_UtcNow, Qry_Date_Day, Qry_To_Date,
   Qry_IfNull, Qry_Sum, Qry_Greatest/Qry_Least, Qry_Bool, Qry_Concat, Qry_Limit, Qry_Diff_Hrs/
   Qry_Diff_Mins/Qry_Diff_Days, JsonExtract — per the worked patterns in IMPLEMENTATION.md section
   10 (section 10.11 for Qry_Diff_Days specifically — prefer it over Qry_Diff_Hrs/Qry_Diff_Mins
   whenever the result is a fractional-day average/duration). Do not
   leave any hand-written dialect-gated branch in place if a Qry_* helper already covers it; only
   keep a per-provider branch for the rare fragment none of them cover, and flag that spot as a
   candidate to add to DatabaseUtil.cs instead.
2. Replace any query built by concatenating or interpolating external input (user input, search
   terms, filter values) with parameters produced by `await db.GetSqlValueAsync(...)`, per the SQL
   construction/security model in AI-SPEC.md section 7 — treat every such spot you find as a SQL
   injection bug to fix, not just a portability one.
3. Preserve existing behavior exactly while converting: match the per-provider NULL-handling table
   in AI-SPEC.md section 11 (wrap nullable columns in Qry_IfNull before passing them to
   Qry_Greatest/Qry_Least/Qry_Concat/Qry_Diff_Hrs), keep an ORDER BY on every query that gains a
   Qry_Limit call, and don't conflate Qry_Bool(bool) (for pre-existing/hand-modeled boolean or
   1/0 columns) with GetSqlValueAsync(bool) (for columns this library created itself via
   CreateTableAsync with ColumnProp.DataType = typeof(bool)) — they are not interchangeable on the
   same column.
4. Route each converted query's results through ExecuteSelectAsync<TEntity> when the shape
   matches an existing entity/DTO, or SelectAsync for ad hoc/aggregate shapes, following the
   provider-portable insert/read pattern in IMPLEMENTATION.md section 6 — don't introduce a new
   mapping mechanism alongside it.
5. Leave everything else (schema declarations, migrations, DI registration) untouched — this pass
   is scoped to the query layer, not the schema.

When done, list every file and query you converted, the dialect-specific syntax or injection risk
each one had before, and the Qry_* helper(s) or GetSqlValueAsync call that replaced it, plus any
fragment you had to leave gated per-provider because no helper covers it yet.

Upgrade to a newer package version

Read **/bin/**/Ark.Rapid.Database/AI-SPEC.md section 12 (Versioning) in this project and note
the current package version plus every ArkDbManager member this project already calls. Then
run `dotnet add package Ark.Rapid.Database` to pull the latest version, rebuild so the
refreshed **/bin/**/Ark.Rapid.Database/IMPLEMENTATION.md, AI-SPEC.md, and ai-spec.json are
copied in, and re-read all three. List every behavior change, newly-unimplemented member, or
newly-supported capability (e.g. an added provider, or a method that used to throw
NotImplementedException and no longer does) that affects this project's existing usage, then
update the code to match — don't leave a call site relying on behavior that changed
underneath it.