CSharpDB Roadmap

This document outlines the planned direction for CSharpDB, organized by timeframe and priority. Items are roughly ordered by expected impact within each tier; current SQL behavior is documented independently in the SQL Reference.

---

Near-Term

Recently completed improvements to query performance, storage/runtime behavior, maintenance workflows, and developer ergonomics.

Feature	Description	Status
DISTINCT keyword	Deduplicate rows in SELECT output	Done
Composite indexes	Multi-column indexes for covering more query patterns	Done
Index range scans	Use eligible single-column INTEGER and single-column ordered TEXT indexes for <, >, <=, >=, and BETWEEN predicates; REAL indexes remain equality-only	Done
Prepared statement cache	Cache parsed ASTs and query plans to avoid re-parsing identical SQL	Done
Cached max rowid	Avoid repeated O(n) scans when generating row IDs on insert (in-memory + persisted high-water mark)	Done
B+tree delete rebalancing	Merge underflowed pages on delete to reclaim space	Done
In-memory database mode	Open a database fully in memory, load a disk database into memory, and save a committed snapshot back to disk	Done
Shared in-memory ADO.NET mode	Support Data Source=:memory: and named shared in-memory databases with explicit save/load	Done
Collection field indexes	Equality-based secondary indexes for Collection<T> via EnsureIndexAsync / FindByIndexAsync	Done
Reader session reuse	Reuse snapshot pager and query planner inside ReaderSession for burst concurrent reads	Done
Architecture enforcement	CSharpDB.Client is now the main caller-facing interaction layer across local and remote scenarios; ADO.NET now routes ordinary direct and daemon-backed access through that layer, with only named shared in-memory provider state still retaining an internal engine dependency	Done
Database administration	Maintenance report, reindex (database/table/index/collection), VACUUM/compact, fragmentation analysis, database size report	Done
Dedicated gRPC daemon	CSharpDB.Daemon host plus CSharpDB.Client gRPC coverage for SQL, schema, procedures, collections, and maintenance	Done
Storage tuning presets	UseLookupOptimizedPreset() and UseWriteOptimizedPreset() for file-backed workloads	Done
Memory-mapped main-file reads	Opt-in mapped clean-page reads plus copy-on-write materialization for mutable access on file-backed databases	Done
Background WAL checkpointing	Incremental/sliced auto-checkpointing to move work off the triggering commit	Done
SQL executor/read-path fast paths	Compact scan and indexed-range projections, broader join lookup/covered paths, grouped/composite index aggregates, correlated subquery filter fast paths, and lower row materialization overhead	Done
Table/index statistics	ANALYZE command with persisted row counts, column NDV/min/max, stale tracking, and initial stats-guided index selection in the query planner	Done
Collection binary payloads	Binary direct-payload format with faster hydration, direct field/path extraction, and richer path-based indexing	Done
Collection path indexes	Nested scalar, array-element, nested array-object, Guid, temporal, and ordered text path indexes with FindByPathAsync / FindByPathRangeAsync	Done
Hybrid storage mode	Lazy-resident durable storage with gRPC tunable file-cache configuration; Admin direct local hosting keeps a warm in-process database instance and uses hybrid incremental-durable options by default	Done
Client backup/restore	BackupAsync / RestoreAsync as first-class ICSharpDbClient operations across direct, HTTP, gRPC, CLI, and Admin	Done
Native table archives and external tables	Native .csdbtable table snapshots with fast Admin Import / Export, download or server-path destinations, CREATE EXTERNAL TABLE / DROP EXTERNAL TABLE, sys.external_tables, read-only external table scans/joins, and embedded primary-key archive lookup indexes for eligible point reads	Done
Older DB foreign-key retrofit migration	Validate/apply maintenance workflow that rewrites existing child tables with persisted FK metadata across direct, HTTP, gRPC, CLI, and Admin	Done

---

Mid-Term

SQL feature parity, provider/tooling compatibility, and ecosystem expansion.

Feature	Description	Status
SQL feature coverage	The bounded SQL implementation and its automated functional qualification gate are complete across the public reference, regression suite, versioned migration capability catalog, replayable EF SQL corpus, historical-database reopen coverage, fault-injected recovery, and a canonical typed parity workload plus feature-specific direct/ADO.NET/HTTP/gRPC coverage where applicable. Status remains In Progress until the release commit records two clean Windows, Linux, and macOS passes, two passing GitHub-hosted comparisons of the 18 stable master-table rows, and two passing sequential local durable-write comparisons on one idle fixed-SSD Windows machine using the same hash-verified harness and valid, stable raw evidence. The local gate must publish its success status on that exact release commit. Supplemental performance suites remain report-only or manual diagnostics. Advanced SQL work is tracked in separate rows rather than keeping completed bounded implementation slices partially complete.	In Progress
User-defined functions and commands	Done for the trusted in-process model: host-registered C# scalar functions, common SQL/Admin built-ins, trusted commands, Admin Forms/Reports/pipeline hooks, declarative Admin Forms action sequences, and local Admin Forms C# code modules are implemented across the supported surfaces. Untrusted sandboxed UDF execution is intentionally out of scope	Done
Writable external tables	Planned opt-in writable external table registrations over mutable .csdbx files, backed by CSharpDB B+tree storage and limited to DML (INSERT, UPDATE, DELETE) in v1 while .csdbtable archives remain read-only	Planned
Subqueries	Scalar subqueries, IN (SELECT ...), EXISTS (SELECT ...), including correlated evaluation in WHERE, non-aggregate projection, and UPDATE/DELETE expressions	Done
UNION / INTERSECT / EXCEPT	UNION, duplicate-preserving UNION ALL, INTERSECT, and EXCEPT across SELECT results, including top-level queries, views, and CTE bodies. INTERSECT ALL and EXCEPT ALL remain unsupported.	Done
Window functions	Done as a bounded in-memory slice: ROW_NUMBER(), RANK(), DENSE_RANK(), common aggregate windows with partitioning, LAG/LEAD/FIRST_VALUE/LAST_VALUE, explicit ROWS frames, case-insensitive named windows, compatible shared ordering across different frames, deterministic peer/NULL behavior, prepared execution, cancellation, and configurable limits with explicit ResourceLimitExceeded failures. Disk spill is deferred and is not required for completion because memory growth is bounded; incompatible partition/order specifications and later SQL window forms remain explicitly unsupported.	Done
EF Core migration and provider validation	Done for the bounded provider contract: a frozen, independently replayable three-version Up/Down SQL corpus covers empty and populated migrations, upgrade/downgrade paths, defaults/checks, composite keys, named relationships and immediate referential actions, primary-key changes, supported column rewrites, table/column/index rename chains, rollback, reopen, runtime CRUD, and ADO.NET schema inspection. Unsupported migration operations covered by this contract fail during SQL generation with stable CDBEF2001 diagnostics. EF migration execution remains embedded/direct; ordinary SQL and metadata transport parity is qualified separately.	Done
Physical EXPLAIN and profile	Done for the bounded physical-plan contract: structural EXPLAIN and executing EXPLAIN ANALYZE rowsets distinguish estimates from actual rows, loops, and elapsed time; expose operator, access-path, index/join, predicate, and relative-cost metadata across direct, ADO.NET, HTTP, and gRPC; preserve normal DML transaction semantics; redact literal and prepared-parameter values from predicate metadata; and attach bounded partial diagnostics on cancellation or errors where safe.	Done
DEFAULT column values	Done for the bounded literal slice: persisted typed-literal and NULL defaults, omitted-value application, explicit DEFAULT markers, INSERT ... DEFAULT VALUES, EF generation, and metadata transport are qualified.	Done
CHECK constraints	Done for the bounded deterministic row-local slice: named/unnamed column and table checks have stable identities and support SQL three-valued semantics, persistence/reopen, rename preservation, catalogs, EF generation, and metadata transport.	Done
ALTER COLUMN rewrites	Done for the bounded ALTER COLUMN slice: exact INTEGER/REAL conversion with eligible index rebuilding, strict dependency-free UTF-8 TEXT/BLOB conversion, and indexed TEXT collation changes use transactional shadow-root rewrites; literal-default and nullability changes use validated transactional catalog updates. The rewrite paths preserve row ids, revalidate CHECK expressions and rebuilt uniqueness, roll back atomically after injected WAL failures, persist across reopen, and are emitted with the catalog operations in EF Core Up/Down migrations.	Done
Primary and unique keys	Done for the bounded key slice: named/unnamed single and composite INTEGER/TEXT primary/unique keys support persistence, enforcement, ordered catalogs, and EF migrations. Standalone primary-key add/drop covers validated logical-key changes with engine-owned backing-index creation/removal; adding a key to a populated table additionally supports bounded single-INTEGER physical rekeying with atomic eligible relational-index and complete ready full-text-owned-store rebuilding.	Done
Foreign key constraints	Done for immediate MATCH SIMPLE: column/table scalar and composite constraints have stable identities, candidate-key/type/collation validation, and the full RESTRICT/NO ACTION/CASCADE/SET NULL/SET DEFAULT matrix for deletes and referenced-key updates. Nested mutations preserve normal row semantics and transactional rollback across persistence, metadata, transports, archives, and tooling. EF-generated DDL covers the bounded relationships; SET DEFAULT and mutating ON UPDATE actions require explicit migration operations rather than relationship-model scaffolding.	Done
Advanced default and check expressions	Statement-time and computed default expressions plus broader safe check-expression forms beyond the completed deterministic row-local contract.	Planned
Advanced rewrites and physical rekeying	Indexed TEXT/BLOB conversion, broader dependency-aware rewrites, additional key shapes and index families, and ordered/range REAL access beyond the completed bounded rewrite and single-INTEGER rekey paths.	Planned
Deferred referential semantics	Deferrable constraints and MATCH FULL/PARTIAL, separate from the completed immediate MATCH SIMPLE action matrix.	Planned
Broader ORM and metadata compatibility	Additional third-party ORM suites and further normalized metadata surfaces beyond the qualified EF Core and ADO.NET contracts.	Planned
SQL release qualification	The automated GitHub gate blocks publishing on two clean full-suite passes for each supported operating system covering durable, in-memory, reopen/recovery configurations, historical-database upgrades, transport parity, the EF corpus, executable documentation, bounded property tests, and stable diagnostics for intentionally unsupported SQL. It also runs two balanced paired previous-release comparisons for the 18 persistent-read and in-memory master-table rows that are stable on hosted Windows runners. Disk-sensitive performance qualification is a required pre-tag local gate: two sequential balanced paired passes qualify each of the remaining ten durable SQL/collection single and batch write rows independently on one idle fixed-SSD Windows machine. Every exact row uses adjacent previous/candidate measurements, pass two reverses the starting order, and every individual measurement must retain at least 30 measured seconds and 10,000 latency samples within the bounded measurement cap. Each logical side has one predeclared attempt; evidence is not discarded, replaced, or silently retried. Both revisions use symmetrically conditioned artifacts with the same hash-recorded candidate benchmark harness; raw runs, recomputed aggregates, artifact closures, and pass reports are hash bound. After post-build quiescence, a required one-second Windows monitor covers every declared measurement. Five consecutive samples above 8% observable external process CPU, 0.5 CPU-core equivalent, or 4,194,304 observable external process I/O bytes per second contaminate the pass; named external build, test, installer, or update processes contaminate immediately. Missing monitor evidence, unavailable allowed runner-tree CPU, a coverage gap above five seconds, invalid benchmark evidence, unstable, order-sensitive, or regressed blocking assessments, and missing, unverifiable, or changed Windows Installer, Application event-log, or pending-file evidence fail closed; unavailable external-process counters remain explicit diagnostics. The expected local runtime is 3.5–4.5 hours. Stable comparisons enforce a 15% throughput limit and fail P95 only when regression exceeds both 25% and 0.05 ms; P99 remains diagnostic. Only the canonical durable-v3 local policy can publish a policy-bound status on the exact candidate commit. Its description is exactly policy=durable-v3; baseline=<40 lowercase hex>; design=<8 uppercase hex>; reports=<8 uppercase>/<8 uppercase>; older policy descriptions are rejected, and the release workflow requires the status from the configured attestor before publishing. Broader performance coverage remains in the existing scheduled report-only guardrails and manual supplemental suite diagnostics. This item closes when all automated functional and hosted-stable jobs and both local durable-write passes succeed for the release commit.	In Progress
Remote host consolidation	CSharpDB.Daemon now hosts the existing REST/HTTP /api surface and gRPC from one long-running process backed by the same warm daemon-hosted client; standalone CSharpDB.Api remains supported for REST-only hosting	Done
Remote API-key protection	Opt-in API-key mode protects REST /api/* and daemon gRPC calls with constant-time key comparison while keeping default no-auth behavior for compatibility	Done
Remote host security hardening	Add authorization, protected admin endpoint scopes, JWT/RBAC options, and TLS/mTLS deployment helpers for remote HTTP and gRPC access	Planned
Daemon service packaging	Package the existing CSharpDB.Daemon host as a persistent background service across systemd, Windows Service, and launchd	Done
Cross-platform deployment	Self-contained daemon archives and install scripts ship for Windows, Linux, and macOS; dotnet tool, Docker, Homebrew, and winget distribution remain future work	In Progress
NuGet package	Publish and maintain CSharpDB.Engine, CSharpDB.Data, CSharpDB.Client, and CSharpDB.Primitives as the primary NuGet packages	Done
Connection pooling	Multiplex logical CSharpDbConnection sessions over one warm embedded engine to amortize physical open/close cost	Done
Admin dashboard improvements	Richer SQL editor UX, query history, deeper diagnostics, and integrated Forms/Reports tooling beyond the core schema/procedure/storage surface	Done
Admin Forms Access parity	Close the highest-impact Access-style form gaps: runtime responsive layouts, full inferred validation enforcement, richer record-source/filter/sort models, Layout View, form modes, broader action/event coverage, and broader control coverage; trusted command-backed form lifecycle events, command buttons, and selected control events are now started	Partial
Admin Reports Access parity	Close the highest-impact Access-style report gaps: bounded saved-query previews, full report rendering/export, parameter/filter prompts, richer grouping/totals options, Layout View, conditional formatting, subreports, and broader report controls; trusted command-backed report preview lifecycle events are now started	Partial
Visual query designer	Classic Admin query builder with source canvas, join editing, design grid, SQL preview, and saved designer layouts	Done
ETL pipelines	Built-in package-driven pipeline runtime with validation, dry-run, execute/resume flows, API/CLI/client coverage, run history, and Admin visual designer support	Done
VS Code extension	Schema explorer, SQL editor with IntelliSense, data browser, table designer, storage diagnostics	Done
ADO.NET GetSchema collections	DbConnection.GetSchema() exposes standard metadata collections (MetaDataCollections, Tables, Columns, Indexes, Views, ForeignKeys) for ORMs and tooling that discover schema through ADO.NET	Done
Multilingual text support	BINARY, NOCASE, NOCASE_AI, and built-in ICU:<locale> collation work across SQL schema/query semantics, metadata surfaces, ordered SQL text indexes, and collection path indexes	Done

---

Long-Term

Advanced features and fundamental architecture enhancements.

Feature	Description	Status
Full-text search	Inverted index with tokenization, stemming, and relevance ranking	Done
JSON path querying	Query into JSON document fields in the Collection API (e.g., $.address.city) via FindByPathAsync / FindByPathRangeAsync	Done
Advanced collection storage path	Binary direct-payload format with direct binary hydration, path-based field extraction, and richer expression/path indexes	Done
SQL batched row transport	Internal row-batch transport serves as the batch-first SQL execution foundation across batch-capable result boundaries, scans, joins, and generic aggregates	Done
External table index coverage	Follow writable .csdbx storage with broader external-table indexes, planner costing, and multi-column lookup/range support beyond the current archive primary-key point-lookup path	Planned
Source-generated collection fast path	Done for the current phase: opt-in generated collection models now provide GetGeneratedCollectionAsync<T>(...), generated field descriptors/index bindings, analyzer-packaged collection model/codecs, generated binary direct-payload encode/decode for supported document graphs, source-generated JSON fallback for unsupported shapes, trim/NativeAOT smoke coverage, and a dedicated sample	Done
Generated collection package ergonomics	Streamline NuGet/analyzer packaging, templates, onboarding docs, and generated-collection setup so consumers can adopt the opt-in path with less project wiring	Planned
Broader generated collection model coverage	Expand generator support beyond the current scalar, scalar collection, nested scalar, and nested collection-scalar shapes; unsupported shapes currently warn and fall back to source-generated JSON instead of binary direct payloads	Planned
Page-level compression	Deep engine/page compression remains planned; application-level payload compression is available as a sample/SDK pattern without changing the storage format	Planned
At-rest encryption	Encrypt database and WAL files with passphrase-based key management and explicit plaintext/encrypted migration/export paths; implementation must meet the database-encryption plan entry criteria before shipping	Research
Advanced cost-based query optimizer	Done for the current phase: ANALYZE-driven stats-guided costing now uses internal equi-depth histograms, heavy hitters, composite-index prefix distinct-count summaries, skew-aware lookup/filter estimates, correlation-aware composite equality filters/joins, non-unique lookup costing, hash build-side choice, and bounded DP reordering for small inner-join chains	Done
Adaptive query re-optimization	Done for the current phase: opt-in adaptive join execution can switch eligible index nested-loop joins to hash joins and can flip inner hash build sides at safe pre-emission boundaries when observed rows diverge materially from estimates. Physical EXPLAIN ANALYZE now reports selected adaptive topology and runtime rows, loops, and elapsed time; adaptive stats persistence and arbitrary mid-plan reordering remain future work	Done
Public planner histogram inspection	Stable SQL-first diagnostics now expose sys.planner_histograms, sys.planner_heavy_hitters, sys.planner_index_prefix_stats, and EXPLAIN ESTIMATE FOR <query> while keeping raw histogram/prefix storage payloads internal	Done
Async I/O batching	Done for the current phase: WAL frame-chunk writes, chunked checkpoint page copies, shared snapshot/export batching, reusable B-tree copy utilities, and the close-out audit now cover the main storage and maintenance write paths; future work is limited to specialized diagnostics or maintenance-path tuning	Done
Low-latency durable writes	Advisory planner-stat persistence can stay deferred without weakening committed-row durability, and sys.table_stats.row_count_is_exact now makes exact versus estimated row-count semantics explicit to planner and COUNT(*) fast paths	Done
Group commit / deferred WAL flush	Opt-in UseDurableCommitBatchWindow(...) batches durable WAL flushes across contending in-process transactions and remains an expert measure-first knob rather than default behavior	Done
Initial multi-writer support	Explicit WriteTransaction conflict-detected retry flow, shared auto-commit non-insert isolation, and opt-in ConcurrentWriteTransactions for shared implicit inserts	Done
Broader multi-writer insert optimization	Opt-in ConcurrentWriteTransactions now reserves shared row-id ranges and rebases hot right-edge insert pages against pending WAL images, improving concurrent one-row auto-ID and explicit-ID insert fan-in while keeping serialized inserts as the default	Done
API-level sharding	Route-aware client, REST, gRPC, daemon, and ADO.NET surfaces target single-shard operations across multiple warm CSharpDB database files using explicit keyspace/shard-key context and stable virtual-bucket ownership	Research
Replication / change feed	Retained commit-log change feeds and reactive query subscriptions for read replicas, live Admin views, and event-driven applications	Research

---

Current Limitations

These are known simplifications in the current implementation:

Area	Limitation
Functions and automation	CSharpDB's UDF/command model is trusted and in-process by design. Current supported surfaces include host-registered scalar functions, common built-ins, trusted commands, form/report/pipeline hooks, declarative action sequences, and local Admin Forms C# modules; untrusted sandboxed execution is intentionally out of scope
Query	Scalar/IN/EXISTS subqueries are supported, including correlated cases in WHERE, non-aggregate projection, and UPDATE/DELETE expressions; correlated subqueries are not yet supported in JOIN ON, GROUP BY, HAVING, ORDER BY, or aggregate projections
Query	UNION, UNION ALL, INTERSECT, and EXCEPT are implemented, with canonical direct, ADO.NET, REST, and gRPC parity coverage; INTERSECT ALL/EXCEPT ALL remain intentionally unsupported with stable diagnostics
Query	Window functions are complete for the bounded in-memory slice, including ranking, aggregate, navigation/value functions, explicit ROWS frames, named windows, compatible shared ordering, peer/default-frame and NULL ordering semantics, prepared execution, cancellation, controlled resource-limit failures, and physical window explain/profile rows. RANGE/GROUPS/EXCLUDE, DISTINCT windows, NULL-treatment syntax, incompatible partition/order specifications, mixed grouped/subquery window queries, and disk spill remain deferred
Schema	Literal SQL DEFAULT values, deterministic row-local CHECK constraints, stable table, column, CHECK, key, and foreign-key identities, logical composite INTEGER/TEXT primary/unique keys, standalone PRIMARY KEY add/drop with engine-owned backing-index maintenance plus bounded populated single-INTEGER rekeying on add across ready relational and complete ready full-text index families, table-level/composite INTEGER/TEXT foreign keys with MATCH SIMPLE and the full immediate RESTRICT/NO ACTION/CASCADE/SET NULL/SET DEFAULT delete and update action matrix, additive transport/archive and ADO.NET constraint metadata, SET/DROP DEFAULT, validated SET/DROP NOT NULL and named constraint changes, plus transactional shadow-root rewrites for exact indexed INTEGER/REAL changes, strict dependency-free UTF-8 TEXT/BLOB changes, and TEXT collation changes with inherited ordinary/unique SQL-index rebuilding are implemented and covered by deterministic WAL-failure recovery tests. Statement-time/function defaults, arbitrary functions/subqueries in checks, indexed TEXT/BLOB and broader dependency rewrites, broader physical rekey shapes/index kinds, deferred constraints, and MATCH FULL/PARTIAL are separate advanced work
Indexes	Equality lookups support INTEGER, TEXT, and hashed REAL SQL indexes. INTEGER-tag values on REAL-indexed columns must be exactly representable within ±253; ordered/range REAL access remains unsupported, while ordered range-scan pushdown supports eligible single-column INTEGER and single-column ordered TEXT index paths
RowId	Legacy table schemas without persisted high-water metadata may pay a one-time key scan on first insert
Collections	FindByIndexAsync supports declared field-equality lookups; FindByPathAsync and FindByPathRangeAsync support path-based queries on indexed paths; FindAsync remains a full scan for unindexed predicates. Generated collections require registered descriptors for existing collection indexes; unsupported generated model shapes warn and use the source-generated JSON fallback instead of binary direct payloads
External Tables	Native .csdbtable archives can be registered and queried as read-only external tables. Writable external tables are planned as an opt-in .csdbx format; current archives remain read-only, and broader external indexes, range seeks, and deeper planner costing remain planned
Networking	CSharpDB.Daemon now hosts both REST and gRPC from one process; named pipes remain reserved but are not implemented end to end today
Security	Remote REST and daemon gRPC support opt-in API-key authentication, defaulting to None for backward compatibility. JWT, RBAC, mTLS helpers, TLS-specific configuration, and at-rest encryption are not implemented
Admin Forms	The Forms designer/runtime supports the core generated-form and data-entry path plus trusted command-backed automation, including lifecycle events, command buttons, selected-control events, conditional UI rules, domain formula helpers, and declarative action sequences for current record, form navigation, filtering, SQL/procedure, and control-property workflows. It still needs Access-parity work for responsive runtime rendering, complete inferred validation, richer form modes, additional events, advanced filtering/sorting, report/query/import/export actions, macro loops/on-error/temp vars, and broader controls
Admin Reports	The Reports designer/runtime supports the core banded preview path plus trusted command-backed preview lifecycle events, but still needs Access-parity work for bounded saved-query previews, full report output/export, parameters, richer grouping and totals semantics, conditional formatting, subreports, and broader controls
Text / Multilingual	Text is stored as UTF-8 and supports all Unicode languages; default semantics remain ordinal, while opt-in BINARY, NOCASE, NOCASE_AI, and ICU:<locale> collation work across SQL schema/query semantics, metadata, ordered SQL text indexes, and collection path indexes
Concurrency	The physical WAL commit path is still serialized at the storage boundary. Initial multi-writer support is shipped, but observed gains still depend on conflict shape and whether shared auto-commit INSERT is left on the default serialized path
Storage	No page-level compression; the compression SDK sample stores compressed payloads as ordinary application-managed BLOB values
Storage	No at-rest encryption for database/WAL files; on-disk storage is plaintext only
Storage	Memory-mapped reads are opt-in and currently apply only to clean main-file pages; WAL-backed reads still rely on the WAL/cache path
Storage	By default, durable auto-commit single-row writes still pay a physical WAL flush per commit; opt-in UseDurableCommitBatchWindow(...) can trade some commit latency for higher throughput across contending in-process writers, but default behavior remains per-commit durable
Query	Phase-2 cost-based planning is in place: ANALYZE, sys.table_stats, sys.column_stats, public planner-stat diagnostics, histogram/heavy-hitter/prefix estimates, and bounded small-chain join reordering now feed join/access-path costing. Stable physical EXPLAIN and executing EXPLAIN ANALYZE rowsets expose selected operators, relative row-work costs, nullable row estimates, and separate runtime rows, loops, and elapsed time. Their 256 KiB limit is an inline content budget rather than an exact serialized transport size. Profile mode executes its target under normal transaction semantics; cancellation and execution errors remain failures, with bounded redacted partial-profile diagnostics attached where safe. Opt-in adaptive join re-optimization can react to stale-stat or parameter-sensitive join cardinality misses, while adaptive stats persistence and arbitrary mid-plan reordering remain future work
Query	Internal row-batch transport is now the default scan-heavy execution foundation across batch-capable scans, joins, aggregates, and result boundaries; remaining work is broader kernel specialization and optional SIMD-style tuning rather than missing core batch coverage

---

Completed Milestones

Major features already implemented:

• Single-file database with 4 KB page-oriented storage
• B+tree-backed tables and secondary indexes
• Write-Ahead Log with crash recovery and auto-checkpoint
• Concurrent snapshot-isolated readers via WAL-based MVCC
• SQL pipeline for the supported CSharpDB SQL subset: tokenizer, parser, query planner, operator tree
• JOINs (INNER, LEFT, RIGHT, CROSS), aggregates, GROUP BY, HAVING, CTEs
• Set operations: UNION, INTERSECT, EXCEPT
• SELECT DISTINCT and DISTINCT aggregates
• Scalar subqueries, IN (SELECT ...), and EXISTS (SELECT ...), including correlated evaluation in filters, non-aggregate projections, and UPDATE/DELETE expressions
• Scalar TEXT(expr) for filter-friendly text coercion
• Composite (multi-column) indexes
• Ordered single-column INTEGER and TEXT index range scans (<, <=, >, >=, BETWEEN) in eligible fast lookup paths
• ANALYZE, persisted sys.table_stats / sys.column_stats, and stale-aware column-stat refresh
• Phase-2 cost-based query planning: statistics-guided access-path selection, join method choice, hash build-side choice, histogram/heavy-hitter/cardinality estimation, composite-prefix correlation modeling, and bounded small-chain inner-join reordering
• Public planner diagnostics through EXPLAIN ESTIMATE FOR SELECT, WITH, compound queries, and sys.planner_* virtual catalogs
• Opt-in adaptive join re-optimization behind DatabaseOptions.AdaptiveQueryReoptimization
• SQL statement and SELECT plan caching
• First-class IDENTITY / AUTOINCREMENT support for INTEGER PRIMARY KEY columns
• Standalone PRIMARY KEY add/drop with engine-owned backing-index creation/removal, plus bounded populated single-INTEGER rekeying on add with atomic ready relational-index and complete ready full-text-owned-store rebuilding, including EF migrations, rollback, reopen, and dependency guards
• Initial exact dependency-free INTEGER/REAL and indexed TEXT-collation ALTER COLUMN rewrites with atomic table/index roots, row-ID, check/uniqueness, rollback, reopen, and EF migration coverage
• Strict UTF-8 dependency-free TEXT/BLOB ALTER COLUMN rewrites, hashed equality REAL SQL indexes, and atomic ready-SQL-index rebuilding for exact INTEGER/REAL changes, including EF Up/Down coverage
• Persisted table NextRowId high-water mark with compatibility fallback for legacy metadata
• Views and triggers (BEFORE/AFTER on INSERT/UPDATE/DELETE)
• Foreign key constraints: stable identities, scalar/composite column/table syntax, MATCH SIMPLE, the full immediate RESTRICT/NO ACTION/CASCADE/SET NULL/SET DEFAULT delete and update action matrix, ordered catalogs, and transport/archive preservation
• Older-database foreign-key retrofit migration across direct, HTTP, gRPC, CLI, and Admin
• ADO.NET provider (DbConnection, DbCommand, DbDataReader, DbTransaction)
• ADO.NET GetSchema() metadata collections for core objects, restrictions, defaults, checks, ordered key columns, and ordered scalar/composite foreign-key pairs
• ADO.NET connection pooling with ClearPool / ClearAllPools
• In-memory database mode with explicit load-from-disk and save-to-disk APIs
• Shared/private in-memory ADO.NET connections with named shared-memory hosts
• Document Collection API (NoSQL) with typed Put/Get/Delete/Scan/Find
• Collection UTF-8 payload fast path with compatibility for legacy backing rows
• Collection secondary field indexes via EnsureIndexAsync / FindByIndexAsync
• Maintenance report, REINDEX, and VACUUM flows across client, CLI, API, and Admin UI
• Dedicated CSharpDB.Daemon gRPC host for remote CSharpDB.Client access
• Remote host consolidation in CSharpDB.Daemon, with REST /api and gRPC sharing the same warm hosted database client
• Opt-in API-key protection for REST /api/* and daemon gRPC access
• Storage tuning presets, bounded WAL read caching, memory-mapped main-file reads, and sliced background WAL auto-checkpointing
• SQL executor/read-path fast paths for compact projections, broader join/index coverage, grouped aggregates, and correlated subquery filters
• Batch-first SQL row-batch execution foundation with batch-aware scan/index/join roots, shared predicate/projection kernels, and batch-native generic aggregate paths
• Interactive CLI with meta-commands and file execution
• REST API with 34 endpoints and OpenAPI/Scalar documentation
• Blazor Server admin dashboard
• Integrated Admin Forms and Reports designers with runtime preview/entry, database-backed metadata persistence, and print-ready report output
• Trusted C# callbacks, commands, Admin automation hooks, and local Admin Forms C# code modules
• B+tree delete rebalancing with underflow handling (borrow/merge + interior collapse path)
• Reusable snapshot reader sessions for higher concurrent-read throughput
• Comprehensive benchmark suite (micro, macro, stress, scaling, in-memory, shared-memory)
• Binary direct-payload collection storage with direct hydration and field/path extraction
• Collection path indexes: nested scalar, array-element, nested array-object, Guid, temporal, ordered text
• Collection path query APIs: FindByPathAsync and FindByPathRangeAsync
• Source-generated typed collection fast path: generated collection models/codecs/field descriptors, generated binary direct payloads for supported shapes, trim-safe GetGeneratedCollectionAsync<T>(...), generator diagnostics, NativeAOT trim-smoke validation, and a dedicated sample
• Full-text search with tokenization, stemming, and relevance ranking
• Hybrid storage mode with lazy-resident durable storage and gRPC tunable file-cache
• Client-wide BackupAsync / RestoreAsync across direct, HTTP, gRPC, CLI, and Admin
• Native .csdbtable table archives with Admin Import / Export, read-only external table registration, sys.external_tables, external table scans/joins, and embedded archive primary-key lookup indexes
• ReplaceAsync for index stores
• Package-driven ETL pipelines with validation, dry-run, execute/resume, persisted run history, and Admin visual designer support

---

See Also

• Architecture Guide — How the engine is structured
• Internals & Contributing — How to extend the engine
• Deployment & Installation Plan — Cross-platform distribution via dotnet tool, Docker, Homebrew, winget, and install scripts
• Multi-Writer Follow-Up Plan — Post-initial multi-writer roadmap, insert-path gaps, and release criteria for broader completion
• API-Level Sharding V1 — explicit keyspace/shard-key routing across multiple database files for tenant-style write-throughput scaling
• Query And Durable Write Performance Plan — Combined optimizer phase-2 plus durable-write completion plan, shipped state, and remaining benchmark/future-work boundaries
• Multilingual Text Support Plan — Build on existing Unicode text storage with case-insensitive matching, locale-aware sorting, and COLLATE clause support for queries and index definitions
• Database Encryption Plan — Encrypted storage format, key management, migration, and managed-surface rollout
• Storage Engine Guide — CSharpDB.Storage API reference: device, pager, B+tree, WAL, indexing, serialization, and catalog
• Native FFI Tutorials — Python and Node.js examples using the NativeAOT shared library
• User-Defined Functions Plan — Trusted C# functions, commands, form/report/pipeline hooks, and code modules
• Writable External Tables Plan — Opt-in writable external tables backed by mutable B+tree external files
• Pub/Sub Change Events Plan — Engine-level change events with channel-based delivery for real-time data subscriptions
• Benchmark Suite — Performance data informing optimization priorities
• Native Table Archives Blog — v3.8 table archive, external table, and Admin Import / Export overview