JetDatabaseWriter

Design notes: Index, primary-key, foreign-key, and relationship format

Status: All shipped phases listed in §7; outstanding work tracked in the same table. Empirical appendix: format-probe-appendix-index.md — annotated hex dumps of real index TDEFs from NorthwindTraders.accdb (Jet4/ACE), format-probe-appendix-jet3-index.md — Jet3 index TDEFs + leaf pages from the Jackcess V1997 corpus (W17a). Regenerate them with dotnet run --project tools/JetDatabaseWriter.FormatProbe -- index,jet3-index. Validation requirement: any PR that lands writer disk-format bytes in this area MUST update the validation matrix and add the strongest feasible Access/DAO coverage: DAO CompactDatabase/OpenRecordset automation for automatable scenarios, with manual Access UI checks kept as supplemental evidence — see §8 and dao-validation-strategy.md.

⚠️ Reverse-engineered notes. The mdbtools spec is partial; many fields are documented as ???. The empirical appendix has been generated from NorthwindTraders.accdb and confirms the §3.1 / §3.2 layouts. DAO CompactDatabase now covers representative ACE/Jet4 advanced key encodings, bitmask/sentinel emission, and B-tree maintenance, but byte-for-byte parity for some sort-key encodings and individual surgical split/merge sub-phases remains a probe gap. Run the probe again against any new fixture before relying on a new offset.

Mutation ownership

Leaf and intermediate builders execute IndexMutationKernel for maximal key prefixes, capacity/progress, headers, bookmark/child metadata, bitmap boundaries and sentinel/free-space finalization. They retain existing bulk key-suffix copies and entry-list iteration as explicit effects. Complete emitted images retain the native layouts; byte-level conformance does not prove the ordered tree, allocator ownership or containing edit’s atomicity. See the emission boundary.

Intermediate-page seeks use generated Dafny entry admission, child-pointer and selection/progress controls around the existing vectorized span comparison. Shared prefixes can include bookmark and child-pointer bytes; the selected pointer is reconstructed from the canonical entry. The comparison sign, stable page image and supplied layout remain adapter contracts. This boundary does not grant edit/reclamation authority or prove a complete tree traversal; see the formal scope.

RelationshipManager owns declaration checks, relationship catalog lifecycle and schema-rewrite orchestration. ForeignKeyMetadataEditor owns the physical FK descriptor layout, logical TDEF-chain writes, partner links and leaf reservations. It has no relationship-catalog or table-catalog dependency. Unlinked leaf reservations are released on failure; TDEF writes link them inside the caller’s existing transaction. The relationship workflow keeps native-security checks ahead of physical edits and coordinates catalog state with those edits.

Index capacity and publication

IndexPageCodec validates every pointer and layout before considering fullness, including entries after an already overfull payload. It sizes prefix-compressed payloads and entry-start bitmasks before encoding. Its TryBuild methods return null for valid entries that exceed capacity; invalid metadata still throws. The tree builder/editor recognize the dedicated IndexCapacityException when a throwing build API encounters capacity, so a general range error cannot select splitting or rebuilding.

IndexMaintainer owns key/metadata preparation and TDEF/usage-map finalization. Ordinary and catalog tree surgery belongs to IndexBTreeEditor; catalog insertion accepts an encoded entry after catalog policy has run. Staged leaf and cross-leaf plans validate append addresses and page ownership, and complete neighboring-page reads before the ordered append, neighbor, leaf and ancestor writes. The intermediate rewrite context owns deepest-first pending operations, collapse, replacement/max propagation and split-tail staging; root publication is a separate step. The incremental metadata state carries pending root changes across TDEF refreshes; its finalization links reservations through usage maps and TDEF writes inside the existing caller-owned transaction.

Index mutations order entries by encoded key, ascending data-page number and row number. The bookmark resolves equal-key ties in the native on-disk order; declaration order cannot substitute for it. IndexEntrySplicer leaves input lists unchanged. After removal validation, sorted survivor and addition runs merge backward in the private result buffer; unordered runs sort the private result buffer by complete native order. Duplicate or absent removal targets retain their refusal behavior. Generated sign/bookmark completion establishes the order under the explicit byte-span adapter contract; a fully verified scalar comparator provides independent conformance reference and emitted-bookmark correspondence. Tree descent and ancestor maxima use complete entries during mutation; query key searches retain their key-only comparisons. The containing B-tree edit remains handwritten; the proof survey explains the allocation and measurement boundary.

Relationship-backed incremental maintenance admits only the reviewed native layouts and ascending fixed, noncalculated Int32 relationship keys with generation-specific flags. Local relationship references must pass admission. Every physical index slot must be represented, its reachable pages must exactly match its usage-map membership, and different physical slots must be disjoint before edits and at successful completion. A failed completion audit throws so the statement rolls back. Logical indexes may share a physical slot. Generated leaf-chain admission additionally validates actual owner/type/link headers, reciprocal adjacency, unique membership and complete coverage of the acquired physical leaves. Staged neighbors are re-read and must retain their original reciprocal links and physical-tree membership before any write. Acquisition completeness, reciprocal cross-table metadata and full separator/ordered-tree semantics remain separate obligations. Other relationship forms retain the row-based rebuild fallback. Complete tree/map inventories are still acquired per mutation, with O(n log n) sorting/membership work; this is not logarithmic maintenance.

CompactRawIndexBuilder is a separate bounded staging path. It consumes ordered stored keys and relocated bookmarks without invoking collation encoders, packs one page group at a time, and alternates two caller-owned fence streams for successive levels. The streams may be files; repeated fence I/O and output are bounded separately from in-memory admission. It emits decrypted index pages, leaves every stream caller-owned, and returns an explicitly unvalidated result only after flushing and a final cancellation check. Private partial output is not publication evidence. Tree ownership, usage maps, destination encryption, relocation and whole-database validation belong to the compact-copy driver. See the Compact & Repair plan for the precise boundary and the validation matrix for controlled native index-byte acceptance.

Descriptor preservation during schema rewrites

Fresh DAO120 controls retain selected General/General Legacy column versions through native DML. In the measured compact cases, indexed Text is rebuilt to the database header’s default version while Memo retains its version. PublicTextCollationDaoTests establishes this independently with native-authored columns and an index: changing only the masked header default changes the compacted Text version, while an untouched control remains General and native seeks succeed in both. Library schema edits still preserve the explicitly requested column descriptors. Broader mixed-column/locale coverage remains F9.

CreateIndexAsync adds a declared index to an existing table through the atomic table-replacement path. Preparation checks the complete logical-index inventory for name collisions, including relationship indexes, and validates key columns, directions and primary-key admission before replacement creation. Copying every existing row builds the new tree and enforces uniqueness/required keys under the statement rollback or explicit savepoint. Surviving descriptors use the existing preservation mapping; the new descriptor uses the ordinary table-creation codec. The original table identity, AutoNumber high water and relationships are retained. Unlike a column edit, adding an index retains the opaque table NameMap because the column names and identities do not change. This operation has the same whole-table resource and native preservation limits as column edits; it does not establish in-place DDL or complete native coverage.

Add/Drop/Rename retains surviving physical and logical index descriptors by index name. Physical bookkeeping, native flags, column-order bytes and opaque descriptor bytes survive; rebuilt column numbers, usage-map pointers, tree roots, logical index numbers and partner references come from the replacement graph. The Jet4/ACE maintained index-row count uses the replacement value while its surrounding opaque bookkeeping words survive. Jet3 statistics remain estimates rather than being replaced by live row counts. Invalid logical-to-physical index references refuse mutation before temporary storage is created.

Source physical-index sharing is retained before FK emission. Distinct source descriptors remain distinct even when their keys match; aliases of one source physical index retain that shared tree. FK-only trees use the source key directions and physical flags before rebuilding, including IgnoreNulls key membership. Ordinary covering indexes are reused only when they shared the same source physical identity. Sparse column-map slots retain their original positions while active column numbers are retargeted by key ordinal. Metadata agreement, logical-kind and direction checks remain guards against an inconsistent replacement. A newly added shortcut primary key receives a unique name reserved against every source logical index and retained index property map, so it cannot take an existing FK or orphan map identity.

IndexDescriptorPreservationTests compares synthetic opaque bytes across all formats, schema operations and write modes, including shared self-FK trees, index reads, uniqueness, complete rollback, standalone descending FK trees and distinct native descriptor identities. Separate DAO-authored sources exercise native metadata, reads, constraints, mutation and compact/reopen. Synthetic marker preservation does not establish native acceptance of those invented marker bytes or a general relocation contract for opaque metadata.

Native-accepted planted variants are checked before any writer edit with positive and negative DML. Descending FK controls match both endpoints’ directions; changing only one endpoint is not an accepted native layout. DAO3.6 and DAO120 normalize the matched descending FK trees to ascending during compact, including an independent compact of the original control. Writer edits preserve the source direction before compact; compacted output follows that independently observed native normalization.

In the Microsoft-authored ComplexFields.accdb Documents table, physical descriptor byte 47 is 02 on the attachment-supporting index. DAO120 omits that index from TableDef.Indexes; changing only this byte to zero exposes the same index with Unique and Required set. The preserved 02 form permits attachment reads, insertion, compact and reopened extraction after a column rename. This observation establishes visibility for that specimen, not every bit of the trailing field. Native tests compare original visibility rather than requiring internal supporting indexes to become public DAO indexes.

Explicit text sort orders

ColumnDefinition.TextCollation selects the existing General97 (Jet3), General Legacy (Jet4/ACE LCID 0x0409, version 0) or General (Jet4/ACE LCID 0x0409, version 1) encoder family. Null uses the header default. Validation rejects undefined enum values, non-Text/Memo storage and incompatible descriptor generations before mutation; calculated Text follows its declared result type. Schema rewrites retain surviving raw descriptors and unknown sort orders instead of replacing them with a public enum choice.

ColumnMetadata reports the supported family separately from raw TextSortOrderId and nullable TextSortOrderVersion. Non-text and descriptorless linked-text columns have null fields; Jet3 has no version. Unknown identifiers retain raw fields with a null family. Native identifier zero retains the existing General Legacy encoder fallback; reporting it does not invent a new native collation. Stored key generation uses the column sort order, while expression comparisons use the database sort order. No encoder, locale rule or text code page changes here.

PublicTextCollationTests checks declaration/default metadata, Text/Memo/calculated Text, preallocation refusal, unknown-ID reporting, uniqueness, seeks and preservation through add/drop/rename. TextSortOrderFixtureTests compares public metadata with pinned native fixture families. PublicTextCollationDaoTests covers writer-created columns, schema edits and row updates followed by native case-equivalent seek, duplicate refusal, insert/update and compact/readback. Local x86 DAO3.6 (dao360.dll 10.0.26100.5074) checks passed for Jet3 General97 and Jet4 General Legacy. Fresh DAO120 Jet4/ACE General Legacy/General checks remain guarded and were skipped on this host. Broader sort/code-page inventories, locale/long-key parity and mixed native column forms remain F9/E2 work.

Null keys and cascade planning

Non-primary unique indexes allow multiple all-Null keys; partly Null composite keys still participate in uniqueness. IgnoreNulls omits all-Null keys in rebuilds, incremental changes and catalog splices. Required and primary indexes reject a Null component before mutation. Northwind Employees supplies real-file evidence for repeated Null WindowsUserName keys; the format/write-mode regression matrix covers batch inserts, updates and reopen behavior.

Cascade updates plan the transitive relationship graph against stored old rows and merged new rows before mutation, with cancellation and a depth limit. Foreign-key checks use the composed parent rows so a simple stable cycle can establish its new keys within the statement. Distinct parent tables reaching the same child column at equal depths are refused, including agreeing diamonds and a diamond with an additional earlier direct path. Agreeing unequal-depth paths are accepted. The planner retains each source parent’s first arrival generation because later planning generations replay earlier changes. Every generation still checks agreement on each assigned column, using relationship key normalization including fixed binary padding rather than object identity. Contradictory assignments are deliberately refused: DAO accepts the characterized update of two distinct parent keys but leaves the child matching only one relationship. Restricted deletes count seek locations without loading child MEMO/OLE payloads; unsupported seeks retain the snapshot fallback.

A moving composite key copies every physical component to its dependents, even when a text component’s new spelling compares equal. Updating (Alpha, 1) to (ALPHA, 5) copies ALPHA as well as 5 through the graph. Key equivalence controls convergence and conflict checks; it does not suppress the physical assignment. Native composite chain, cycle and diamond checks verify General97 and the version-0 General Legacy and version-1 General sort orders at LCID 0x0409, including mixed-case dependent keys, unaffected composite neighbors, update/delete, rollback and compact/readback. This establishes those graph cases rather than a complete collation inventory.

The writer’s 64-edge cascade bound is an independent resource policy, not a Microsoft engine limit. DaoCascadeDepthTests characterizes simple chains: DAO3.6 on Jet3 and DAO120 on Jet4/ACE accept 50 update edges, but refuse 51 with error 3000 (reserved error -1623), while deleting 65 edges succeeds. On DAO-authored ACE chains, the writer accepts 64 edges and refuses 65 before mutation, for both updates and deletes. Native reopen and compact accept that 64-edge output; native update execution limits do not imply that its stored graph is invalid. Caller rollback restores bytes, and direct, automatic and caller-transaction refusals preserve bytes at the writer bound.

For a simultaneous self-referencing update, DAO3.6 and DAO120 preserve the caller’s entire foreign key when any component is explicitly assigned, including an assignment equal to the stored value. Only an unassigned FK follows its own cascade. Thus changing [Id]=1 to 5 while explicitly retaining [ParentId]=1 is refused; assigning 5, another stable parent or scalar Null is allowed when the remaining dependents satisfy the cascade policy. Final validation checks an unchanged FK if the composed statement removes its previously referenced parent. It still permits unrelated updates to an already orphaned unchanged key. DaoSelfRelationshipUpdateTests compares scalar and non-Null composite cases on DAO-authored Jet4/ACE databases, opens the writer output natively and verifies compact/readback. Ordinary regressions cover all write modes, byte-identical refusal and continued caller-transaction use followed by rollback.

Only an entirely Null foreign key is exempt from parent matching. A partial-Null composite key must match every component of an existing nullable unique parent, including the Null components. Parent dependency matching also includes entirely Null keys: changing or deleting such a nullable unique parent affects every entirely Null child key, even when another identical Null parent remains. Cascade copies the new parent key or deletes the child; restricted changes are refused. Entirely Null dependency seeks fall back to a row scan so a covering IgnoreNulls index cannot hide those children. Relationship creation validates existing partial keys by the same rule. DAO3.6 on Jet3/Jet4 and DAO120 on Jet4/ACE establish these semantics; clearing only one component of a self-reference is refused when no matching parent exists, while clearing the entire key succeeds. Unrelated updates still leave an already orphaned unchanged key alone. DAO3.6 checks on native Jet3 files cover scalar/composite self-key assignments and two-/three-table cascade cycles, including caller rollback, native readback and compact. DaoCompositeCascadeGraphTests and DaoCascadePathTests extend the native comparisons to composite graphs, unequal-depth convergence, overlapping diamonds and caller assignments merged with downstream cascades.

Unenforced relationships persist catalog metadata without physical foreign-key indexes. Native DAO emits only the existing primary indexes for a relationship with dbRelationDontEnforce, permits orphans and allows related table deletion. Enforced relationships emit physical foreign-key indexes and block that deletion.

Index-cursor seek and last-entry intermediate descent track visited pages and refuse a repeated child or tail pointer before rereading its target. It reports JetCorruptDataException with CorruptIndex and that page number. Synthetic self-cycle and two-page-cycle regressions in IntermediateIndexCycleTests cover Jet3 and ACE cursor paths and public Jet3/Jet4/ACE index reads; they establish corruption refusal rather than native index-layout evidence.

Index-cursor Contains, exact seeks, list ranges and streaming ranges track visited leaf pages and share a maximum of 1,048,576 leaves per walk. A repeated page or exhausted traversal budget raises JetCorruptDataException with CorruptIndex and the next page number before that page is read. Each leaf checks cancellation. Streaming callers can receive earlier rows before the corruption is encountered; they must observe enumeration completion. Synthetic self-cycle and two-leaf-cycle regressions in IndexCursorTests establish bounded refusal, with valid multi-leaf and cancellation controls. They add no native index-layout evidence.

1. Background

Row enumeration does not consume index information today; it remains a linear data-page scan via the per-table page-usage bitmap. Index schema metadata is observable via IAccessReader.ListIndexesAsync (R1), and exact equality seeks are exposed via IAccessReader.SeekRowsAsync (R3). Microsoft Access / DAO CompactDatabase remains the decisive compatibility validator for writer-emitted index bytes because it exercises the engine’s own index and catalog walkers.

The two pinned tests that codify the original limitation, both still passing as of W16:

2. Reader-side prerequisite

Before any writer work, the reader needs to be able to enumerate indexes from a TDEF and (optionally) traverse leaf pages. This is the foundation that makes every writer change round-trippable in this repo’s own test suite.

Recommended phasing:

  1. Phase R1 — TDEF index metadata. ✅ Implemented (2026-04-24). Parses the logical-index block and index name list out of the existing TDEF byte buffer. Surfaced as IAccessReader.ListIndexesAsync(tableName, CT) returning IReadOnlyList<IndexMetadata> (with IndexKind, IndexColumnReference, real-idx sharing via RealIndexNumber, FK fields, and cascade flags). No B-tree page traversal. Tests in tests/JetDatabaseWriter.Tests/Indexes/IndexMetadataTests.cs.
  2. Phase R2 — Leaf-page enumeration. Walk page-type 0x04 leafs from first_dp and decode entries (per the index entry layout in §4 below). Used internally for assertions in writer tests; not necessarily exposed publicly.
  3. Phase R3 — Index seeking. ✅ Implemented (2026-05-21). IndexCursor descends 0x03 intermediate pages, probes 0x04 leafs, honours §4.2 entry-start bitmasks, §4.4 prefix compression, non-unique sibling leaf walks, and the §4.5 tail_page append fall-through. The public reader surface is IAccessReader.SeekRowsAsync(tableName, indexName, keyValues, CT), which performs exact Jet4/ACE seeks and materialises matching rows through the same typed row path as Rows(...). Range scans are intentionally not part of this API.

Phases R2/R3 require the encoded sort-key implementation that the writer also needs (§5), so doing R2 before W1 amortizes that cost.

3. TDEF layout (per mdbtools HACKING.md “TDEF (Table Definition) Pages”)

The TDEF page chain (page type 0x02, linked through next_pg at offset 4) is concatenated by TableDefReader.ReadTDefBytesAsync into a single byte array. Within that buffer, the layout (Jet4 / ACE) is:

Section Size Notes
Page header 8 bytes page_type=0x02, unknown=0x01, tdef_id, next_pg
Jet4 TDEF block 55 bytes tdef_len, unknown, num_rows, autonumber, autonum_flag, unknown[3], ct_autonum, unknown[8], table_type, max_cols, num_var_cols, num_cols, num_idx at relative offset 39 (absolute 47 from page start), num_real_idx at absolute 51, used_pages, free_pages
Real-index entries num_real_idx × 12 (Jet4) / × 8 (Jet3) Already skipped by TableDefReader.ReadTableDefAsync. Per mdbtools each Jet4 entry: unknown(4) + num_idx_rows(4) + unknown(4)
Column descriptors num_cols × 25 (Jet4) / × 18 (Jet3) Already parsed
Column names num_cols × length-prefixed 2-byte len + UTF-16 (Jet4); 1-byte len + ANSI (Jet3)
Real-index “physical” descriptors num_real_idx × 52 (Jet4) / × 39 (Jet3) See §3.1
Logical-index entries num_idx × 28 (Jet4) / × 20 (Jet3) See §3.2
Logical-index names num_idx × length-prefixed Same encoding as column names
Variable-length column trailing block iterates while col_num != 0xFFFF Per-var-col used_pages / free_pages pointers

3.1 Real-index “physical” descriptor (Jet4: 52 bytes)

Verified against Companies in NorthwindTraders.accdb (appendix). Layout:

4 bytes  unknown      // probe shows constant `83 07 00 00` across all real-idx entries in a TDEF;
                     // value differs across tables but is constant within one table — looks like a per-tdef cookie
30 bytes col_map      // 10 × { col_num(2), col_order(1) }; col_num=0xFFFF marks unused slot
4 bytes  used_pages   // FK to usage bitmap for this index
4 bytes  first_dp     // root index page (0x03 / 0x04 / sometimes 0x01 for tiny tables)
1 byte   flags        // index_flags table below
9 bytes  unknown      // first byte varies (probe sees 0x80, 0x81, 0x89); remaining 8 bytes appear zero

Index flags (flags byte; not exhaustive per mdbtools):

Bit Meaning
0x01 Unique
0x02 IgnoreNulls
0x08 Required

Probe-confirmed (NorthwindTraders.accdb Companies PK, MSysObjects PK): Access emits flags = 0x00 on the real-idx physical descriptor of primary-key indexes. PK uniqueness is signalled by the logical-idx index_type = 0x01 discriminator (§3.2), not by the 0x01 flag bit. IAccessReader.ListIndexesAsync surfaces IndexMetadata.HasUniqueFlag for the raw flag and IndexMetadata.EnforcesUniqueness for the semantic answer.

Jet3 real-idx physical descriptor (39 bytes) — W17a probe-confirmed

The mdbtools spec lists 39 bytes for Jet3 but does not enumerate the field map. The W17a probe (format-probe-appendix-jet3-index.md) was run across 6 Jet3 fixtures (indexTestV1997.mdb, compIndexTestV1997.mdb, testIndexCodesV1997.mdb, testV1997.mdb, mdbtools/nwind.mdb, Jet3Test.mdb); for every real-idx slot the candidate at phys-desc bytes 34..37 resolved to a page_type = 0x04 leaf or 0x03 intermediate, while the leading 8-byte skip entry slots either failed to resolve or pointed at data pages (0x01). Re-dumping indexTestV1997.mdb (2026-10) pinned the rest of the descriptor; earlier revisions of this section put a 4-byte cookie before col_map and had no used_pages, which was wrong:

30 bytes col_map      // 10 × { col_num(2), col_order(1) }, starting at byte 0; col_num=0xFFFF marks an
                      // unused slot, whose order byte Access leaves as junk
4 bytes  used_pages   // usage-map row byte + 3-byte page, as on Jet4 (Table1's FK indexes name rows 7 and 6
                      // of page 0x26); newly emitted table indexes receive their own usage-map pointers
4 bytes  first_dp     // root index page (0x03 / 0x04, sometimes 0x01 for tiny tables)
1 byte   flags        // same bit semantics as Jet4 (0x01=Unique, 0x02=IgnoreNulls, 0x08=Required); Access 97
                      // writes 0x00 for normal and FK indexes and 0x09 for the primary key, never the 0x80 bit

Key deltas vs. Jet4:

IndexLayout.WriteRealIdxDescriptor writes this descriptor for either format.

col_order per col-map slot: bit 0x01 is the ascending flag. The writer emits 0x01 for ascending and clears the bit (0x00 in current output) for descending; DAO CompactDatabase preserves the mixed-direction descriptors covered by AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair.

3.2 Logical-index entry (Jet4: 28 bytes)

Verified against Companies in NorthwindTraders.accdb (appendix).

4 bytes  unknown        // probe shows constant `59 06 00 00` across all logical-idx entries in a TDEF;
                        // varies across tables — same per-tdef cookie pattern as §3.1
4 bytes  index_num      // logical index number; NOT necessarily sequential
4 bytes  index_num2     // index into the real-index list (which physical idx backs this)
1 byte   rel_tbl_type   // FK role: 0x01 on parent-side FK logical entries,
                        // 0x02 on child-side FK logical entries; 0x00 for non-FK
4 bytes  rel_idx_num    // -1 (0xFFFFFFFF) when this is not a FK; otherwise the
                        // partner logical-index number, not the partner real-idx slot
4 bytes  rel_tbl_page   // page number of the other table in the FK
1 byte   cascade_ups    // FK cascade-updates: only bit 0x01 (CASCADE_UPDATES_FLAG)
                        // signals "cascade enabled". DAO/Access stamps placeholder
                        // 0x04 (CASCADE_SET_DEFAULT_FLAG) into this byte for every
                        // index — including PK and standalone — so a `!= 0` test
                        // produces false positives. Mask to 0x01 (Jackcess IndexImpl).
1 byte   cascade_dels   // FK cascade-deletes: same 0x01-mask rule as cascade_ups.
1 byte   index_type     // 0x01 = primary key, 0x02 = foreign key, otherwise normal
4 bytes  trailing       // probe sees zeros; HACKING.md does not document this tail

Sharing confirmed empirically: Companies (15 user columns) has 16 logical indexes but only 5 real indexes — multiple logical FK indexes share the same index_num2 (real-idx slot).

Jet3 logical-idx entry (20 bytes) — W17a probe-confirmed

The mdbtools spec lists 20 bytes for Jet3 without enumerating the field map. The W17a probe verified index_type = 0x01 at byte 19 on the MSysAccessObjects.PrimaryKey logical-idx in every fixture sampled. Removing the Jet4 leading 4-byte cookie and trailing 4-byte tail from the §3.2 layout produces the 20-byte slice with all per-field offsets shifted left by 4 bytes:

4 bytes  index_num
4 bytes  index_num2
1 byte   rel_tbl_type
4 bytes  rel_idx_num   // -1 (0xFFFFFFFF) when this is not a FK
4 bytes  rel_tbl_page  // page number of the other table in the FK
1 byte   cascade_ups   // same 0x01-mask rule as Jet4 (see §3.2)
1 byte   cascade_dels  // same 0x01-mask rule as Jet4 (see §3.2)
1 byte   index_type    // 0x01 PK, 0x02 FK, otherwise normal

This matches the offsets AccessReader.ParseIndexMetadata uses for the Jet3 path (W17b 2026-04-26: the prior typo that read index_num / index_num2 from Jet4 offsets even on Jet3 was fixed; both fields now read from the §3.2 shifted-left offsets). IndexLayout.WriteLogicalEntry writes either format’s entry.

Jet3 FK entries, pinned from indexTestV1997.mdb (2026-10; Table1 is the child of Table2 and Table3):

3.3 Sharing rules

“There can be more logical index infos than physical index infos (currently only seen for foreign-key indexes). In this situation, one or more of the logical indexes actually share the same underlying physical index (the index_num2 indicates which physical index backs which logical index).” — HACKING.md

When the user enables “enforce referential integrity” in the Access GUI, both sides of the relationship gain an extra logical index (type 0x02). If the FK columns are already covered by a user-created index, the new logical FK index reuses that physical index data instead of duplicating it.

3.4 DAO FK compatibility findings (updated 2026-05-12)

The fk-dao-baseline FormatProbe mode creates the same parent/child/enforced-FK schema through this writer and through DAO, then dumps MSysObjects, MSysACEs, MSysRelationships, TDEF FK logical entries, and CompactDatabase output. Current empirical facts:

Access-authored fixtures add three facts the DAO baseline above does not show (2026-10 re-dump of indexTestV1997.mdb, indexTestV2000.mdb, nwind.mdb and NorthwindTraders.accdb):

DAO3.6 Jet3 and DAO120 Jet4/ACE comparisons in DaoRelationshipMetadataTests verify all four cascade policies, reciprocal TDEF links, native flags, name reordering and unique-index preference. DaoRelationshipCatalogIdentityTests separately checks punctuation and accents through immediate native relationship creation after writer rename, using format-specific Access collation order. Existing statistics slots survive creation and rename byte-for-byte. For the tested four-child/three-distinct-Long-key fixture, native new-FK statistics contain UInt32 values 4 and 3, plus a zero UInt32 on Jet4/ACE; the writer initially emits a zero slot. Native insert/delete, orphan refusal and cascades accept those entries before compact, and compacted statistics match an independently native-compacted source. This does not assign general meanings to unexplained statistics bytes. Unsupported default collations refuse relationship creation, rename and relationship-bearing schema rewrites with UnsupportedTextCollation before mutation. Native probes place underscores before letters on all three formats and hyphens differently under General97 versus General Legacy.

4. Index page layout (page types 0x03 and 0x04)

0x04 = leaf, one entry per row. 0x03 = intermediate. For very small tables, first_dp can point directly at a 0x01 data page.

4.1 Page header

1 byte   page_type      // 0x03 or 0x04
1 byte   unknown        // 0x01
2 bytes  free_space
4 bytes  parent_page    // TDEF pg for this idx
4 bytes  unknown(0)     // Jet4 / ACE ONLY — not present in Jet3 (shifts every field below by +4)
4 bytes  prev_page      // sibling at this level
4 bytes  next_page      // sibling at this level
4 bytes  tail_page      // pointer to tail leaf (PK-style append optimization)
1/2 bytes pref_len      // Jet3: one byte; Jet4/ACE: two bytes, little-endian
1 byte   level         // index level; separate from the shared-prefix length

Per-format absolute offsets (Jet3 prefix width and level verified against mdbtools/nwind.mdb):

Field Jet3 (.mdb Access 97) Jet4 / ACE (.mdb 2000–2003, .accdb)
parent_page 0x04 (4) 0x04 (4)
prev_page 0x08 (8) 0x0C (12)
next_page 0x0C (12) 0x10 (16)
tail_page 0x10 (16) 0x14 (20)
pref_len 0x14 (20), u8 0x18 (24), u16 LE
level 0x15 (21) 0x1A (26)
bitmask start 0x16 (22) 0x1B (27)
first entry 0xF8 (248) 0x1E0 (480)

These constants are exposed as Constants.IndexLeafPage.{Jet3,Jet4}{PrevPage,NextPage,TailPage,PrefLen,Bitmask,FirstEntry}Offset and bundled into IndexPageLayout (IndexPageLayout.Jet3 / IndexPageLayout.Jet4) so writer + reader code paths pick the correct offsets per format.

4.2 Entry-start bitmask

A bitmask immediately following the header marks where each entry begins.

The bitmask encodes 1 bit per byte of payload (LSB-first within each byte). The first entry is implicit (no bit set). A bit at position N indicates an entry begins at offset (first_entry_offset + N).

Sentinel bit (2026-05-04): Access/DAO also writes a “one-past-the-end” sentinel bit at the position immediately after the last entry (i.e. at payloadEnd - first_entry_offset, where payloadEnd = page_size - free_space). This sentinel does NOT correspond to an actual entry — it marks the boundary between used and free space. DAO validates this sentinel during Compact & Repair and rejects pages that omit it. The sentinel is present on every non-empty leaf page in NorthwindTraders.accdb (verified across all leaf pages). IndexPageCodec.BuildLeafPage writes the sentinel; IndexPageCodec.DecodeLeafEntries excludes it from the scan by stopping at payloadEnd.

Jet3 confirmed empirically by W17a (format-probe-appendix-jet3-index.md, MSysAccessObjects.PrimaryKey leaf page 24 of indexTestV1997.mdb): bitmask at offset 0x16, first entry at 0xF8, 9-byte stride for LongInteger int keys (1B flag + 4B BE int + 3B page + 1B row), and the bitmask byte pattern 00 02 04 08 10 20 40 80 … matches the LSB-first 9-byte stride exactly.

The §4.1 page-header layout is NOT identical between Jet3 and Jet4. Earlier revisions of this document incorrectly claimed it was. Jet4 / ACE inserts an extra 4-byte unknown(0) field at offset 0x08, shifting prev_page, next_page, tail_page, and pref_len by +4 bytes. See the per-format offset table above; verified against tests/JetDatabaseWriter.Tests/Databases/NorthwindTraders.accdb (page 2790, MSysObjects.ParentIdName middle leaf) and Jackcess com.healthmarketscience.jackcess.impl.JetFormat.

4.3 Entry record

IndexPageCodec.NextEntryStart and strict leaf bitmap admission execute the generated Dafny scans FindIndexEntryStart and CheckIndexLeafBitmap. Their contracts establish the least later entry boundary before the payload end, strictly decreasing scan work, the implicit first entry, exact nonempty end sentinel and clear bitmap tail. Empty payloads admit no bitmap bits. The scans bound every bitmap read by the supplied page and layout. Complete key decoding, tree traversal and edit/ownership composition remain separate; see the formal boundary.

1 byte   flags          // 0x7F = ascending non-null, 0x80 = descending non-null,
                        // 0x00 = ascending null, 0xFF = descending null
n bytes  encoded key    // per-column, in column-map order; null cols contribute only the flag
3 bytes  data page      // page containing the row (24-bit, big-endian per HACKING.md)
1 byte   data row       // row number on that page
4 bytes  child page     // ONLY on 0x03 intermediate pages; this entry is the LAST entry
                        // on the referenced child page

“0x80 is the one’s complement of 0x7F and all text data in the index would then need to be negated. The reason for this negation is descending order.” — HACKING.md

4.4 Prefix compression

JET leaf and intermediate pages compress entries via a single page-shared prefix held in the header pref_len field. An earlier revision of these notes hypothesised a second per-entry incremental compression scheme; that hypothesis was incorrect (see §4.4.2) — the symptom that motivated it was a 4-byte header offset bug in our writer (§4.1).

4.4.1 Page-shared prefix (pref_len header field)

If the page header pref_len (Jet3 offset 0x14, u8; Jet4 / ACE offset 0x18, u16 LE) is non-zero, the first pref_len bytes of the first entry are implicitly prepended to every subsequent entry on that page. So a 4-byte-prefix page could cut every int-key entry from 9 bytes (flag + int32 + 3-byte page + 1-byte row) to 5 bytes. IndexPageCodec.BuildLeafPage (writer) computes the longest byte-wise prefix common to every entry and emits this on enablePrefixCompression: true; IndexPageCodec.DecodeLeafEntries (reader) re-prepends those bytes to entries beyond the first.

The shared bytes can include row and intermediate child pointers, so a reader must reconstruct the complete entry before separating its key and pointers. In mdbtools/nwind.mdb, Orders’ ShippersOrders root page 592 stores prefix/level bytes 00 01; leaves 593–595 store 06 00. The leaf prefix contains the five-byte key plus one row-pointer byte. Treating the header as u16 makes the root prefix 256; limiting the prefix to the key loses matching rows. Jet3 writers cap shared prefixes at 255 and write the prefix without occupying the adjacent level slot.

4.4.2 Per-entry incremental compression — hypothesis WITHDRAWN (2026-05-02)

A prior diagnostic session (2026-05-01) hypothesised that Access-authored leaves with pref_len = 0 use a per-entry incremental prefix scheme stripping leading bytes shared with the previous entry. That hypothesis was wrong. The corrupt-looking entries observed on MSysObjects.ParentIdName (NorthwindTraders.accdb page 2790) were the result of our writer reading pref_len from the wrong byte offset — we were reading offset 0x14 (correct for Jet3) on a Jet4 / ACE page where pref_len actually lives at 0x18. The byte at 0x14 on a Jet4 page is the low half of tail_page, which happened to be 0x00 0x00 for the inspected leaf, so the writer mis-computed pref_len = 0 instead of the true pref_len = 1 (or higher), then re-encoded entries beyond the first as if they carried the canonical prefix.

Fix shipped: Constants.IndexLeafPage exposes per-format header offsets, IndexPageLayout carries them (PrefLenOffset, PrevPageOffset, NextPageOffset, TailPageOffset), and every writer + reader call site (IndexBTreeBuilder, IndexCursor, IndexPageCodec, IndexBTreeEditor, and the IndexMaintainer path) reads/writes through the layout. See §4.1 for the corrected offset table.

No evidence of any per-entry incremental compression scheme has been found in Jackcess (com.healthmarketscience.jackcess.impl.IndexData only models the §4.4.1 page-shared prefix). Treat any future “unaccountable per-entry stripping” symptom as a strong signal of a header-offset / decode bug rather than a missing format feature.

4.5 Tail-page optimization

PK-style indexes that mostly append (e.g. autoincrement) maintain a tail_page pointer on the rightmost leaf. New leaf pages chain through this pointer until full, then propagate up the tree. A reader cursor that walks the index tree must follow tail_page after exhausting the normal traversal, otherwise it misses recent appends.

5. Sort-key encoding (“alphabetic sort order”)

Status: text/memo encoding is production-routed by each column’s stored sort order for General 97, General Legacy and General (1033 version 1); fixed-type encodings are implemented in W2 / W12 / W13. The General encoder uses V2010 long-row suffix tables that are byte-exact for the checked-in Access-authored Table11 / Table11_desc contexts and the DAO-derived 65-character contribution tables covering plain, auxiliary, row10, row11 and row12 contexts. Broader arbitrary-context V2010 suffix coverage and binary long-key suffix coverage remain future compatibility expansions.

Frozen DAO3.6 General97 keys in General97EncoderFixtureTests retain embedded U+0000 as inline 0x10. A simple expansion such as sharp s contributes both significant positions before a later accent; its two inline contributions must both advance the significance stream. These complete native key/bookmark comparisons cover the specified NUL and expansion/accent contexts, without establishing arbitrary locale or code-page support.

Per HACKING.md the General Legacy encoding (Access 2000–2007, locale 1033 version 0):

Char range Encoded bytes
0–9 0x56–0x5F
A–Z 0x60–0x79
a–z 0x60–0x79 (case-insensitive)

Text key terminator: 0x00 (or 0xFF when negated for descending).

Access 2010 introduced a General encoding (locale 1033 version 1) that is different from General Legacy. HACKING.md does not document the new encoding. The production implementation uses the Jackcess port (com.healthmarketscience.jackcess.impl.GeneralIndexCodes plus the index_codes_gen.txt / index_codes_ext_gen.txt resource tables) and a table-driven V2010 long-row suffix provider derived from DAO-authored contribution matrices. IndexKeyEncoder selects it from the column descriptor for index maintenance and seek-key encoding.

For non-text fixed types the encoding is conceptually big-endian with a sign-flip on the high bit:

⚠️ The exact byte sequences above are conventional B-tree sort-key encodings and match what Jackcess emits. They are NOT independently verified against this codebase’s test fixtures and need to be byte-compared with NorthwindTraders.accdb index leafs before use.

Because text data is mangled by sort-key encoding, “covered queries” (queries answerable from index alone) are not possible against text columns. This is a JET property, not a library limitation.

Text-key dispatch reads each column’s descriptor: General 97 has no version slot; General Legacy is sort order 0x0409, version 0, and General is version 1. A raw sort order of zero selects General Legacy. New columns take the database header’s default; a zero header default is normalized to 0x0409 (General 97 for new Jet3 columns). Schema rewrites retain each surviving column’s stored sort order. Unsupported nonzero sort orders refuse index-maintaining writes before mutation. Inferred queries and Includes use scans when their seek keys cannot be encoded; explicit seeks report the unsupported key. Jet3 seeks use the Jet3 index-page layout, including relationship checks and cascades.

Validation rules, defaults and calculated expressions compare text with the database’s sort order, using complete comparison keys without index limits or trimming trailing spaces. Like and StrComp keep their existing semantics. Access-host DAO tests remain the acceptance check for behavior not pinned by stored fixture keys.

Includes normalize text joins with that same database comparison, trimming trailing spaces for join equality. They use a scan if a candidate index’s join-column sort order differs from the database default, and check complete join values after a seek so a stored index prefix cannot create a false match.

6. MSysIndexes / MSysIndexColumns / MSysRelationships catalog tables

Probe-confirmed across the full Jackcess corpus: 88 of 89 .mdb and .accdb fixtures under tests/JetDatabaseWriter.Tests/Databases/ opened cleanly (the one failure was AesEncrypted.accdb, which the reader then took for password-protected because of a creation-date byte at header offset 0x62; it holds no password and now opens). Zero of those 88 contain MSysIndexes or MSysIndexColumns. The corpus spans Access 97, 2000, 2003, 2007, 2010, and 2019, plus the upstream Jackcess test suite (which includes fixtures named indexTest*, bigIndexTest*, compIndexTest*, testIndexProperties*, testIndexCodes*, binIdxTest*, indexCursorTest*, testRefGlobalV2000 — i.e. files specifically authored to exercise index, FK, and relationship behaviour). See format-probe-appendix-index.md for the ACCDB scan and format-probe-appendix-mdb-catalogs.md for the full 89-file headline + per-fixture verdict table. Index metadata in every JET format this library targets lives entirely in the per-table TDEF block (sections §3.1 / §3.2 above). A writer that emits the TDEF blocks correctly does NOT need to populate any index catalog tables for ACCDB or .mdb output.

MSysRelationships does exist in every fresh ACCDB and in every .mdb fixture probed (Jet3 + Jet4) and is required for cross-table foreign keys. The ACCDB schema is in format-probe-appendix-index.md; the .mdb per-fixture catalog rows are in format-probe-appendix-mdb-catalogs.md (W9 implementer should re-probe the .mdb MSysRelationships TDEF for column-level layout before emitting rows for .mdb output).

Catalog table Status Required by writer? Notes
MSysObjects Already written yes AccessWriter already emits this.
MSysIndexes Not present in any of 88 fixtures (Access 97 → 2019, Jet3 / Jet4 / ACE) no Confirmed across the full Jackcess test corpus including fixtures explicitly authored to exercise indexes, FKs, and relationship behaviour. mdbtools docs that imply this table exists are misleading for every JET format this library writes.
MSysIndexColumns Not present in any of 88 fixtures no Same.
MSysRelationships Exists in every probed fixture (ACCDB + every .mdb) yes for FKs ACCDB column layout in format-probe-appendix-index.md. .mdb schema not yet decoded at the column level — re-probe before W9 emits rows for .mdb output.

7. Implementation phases (writer)

Leaf and intermediate pages share one validated emitter. Throwing and nullable builders use the same metadata, shared-prefix, payload and bitmask checks; invalid pointers are checked across every entry before capacity refusal. Validated values are emitted once with their original pointer widths and prefix limits.

This is the historical phase log for writer-side index support; every phase below has shipped (see the per-row ✅ status). Early rows record what a phase did at the time — e.g. W2 lists the types it initially left as NotSupportedException — and later phases (W7/W12/W13/W19) lift those limits, so read the table as a cumulative history rather than a current limitation list. The reader-side foundation (R1+R2) these phases build on landed long ago.

Phase Scope Cost (rough) Status
W1 Plumb IndexDefinition model + BuildTDefPage extension to emit num_real_idx > 0 with the 52-byte real-idx descriptors and 28-byte logical-idx entries medium ✅ Implemented (2026-04-24). Single-column, non-unique, ascending, Jet4/ACE only. Round-trips through ListIndexesAsync. Originally shipped with first_dp = 0 (no leaf pages); W3 below has since wired in an empty leaf per index and patches first_dp to point at it. See §7.1 below.
W2 Sort-key encoders for fixed types (int*, double, datetime, guid, currency). Skip text. medium ✅ Implemented (2026-04-24). src/JetDatabaseWriter/Indexes/IndexKeyEncoder.cs. Supports Byte, Integer, LongInteger, Money, Float, Double, DateTime. Not supported: Guid, Numeric, Boolean, text — each throws NotSupportedException. Per-type encodings match the conventional B-tree sort-key recipe in §5 (BE + sign-bit flip for signed integers; BE + sign-flip-or-complement for IEEE-754; ascending-then-ones-complement for descending). Round-trip ordering verified for every supported type via 17 unit tests in tests/JetDatabaseWriter.Tests/Indexes/IndexKeyEncoderTests.cs. Byte sequences have NOT been compared against a real Access-authored leaf — see §8.
W3 Leaf-page (0x04) emitter: bulk-build a single leaf for tables that fit on one page. medium ✅ Implemented (2026-04-24). src/JetDatabaseWriter/Indexes/IndexPageCodec.cs (Jet4/ACE only). Emits the §4.1 page header (page_type=0x04, parent_page=<TDEF>, prev/next/tail=0, pref_len=0), the §4.2 entry-start bitmask (1 bit per entry beyond the first, LSB-first within each byte at offset 0x1B), and §4.3 entry records (encoded key + 24-bit BE data page + 1-byte data row). Wired into AccessWriter.CreateTableAsync so each IndexDefinition allocates one empty leaf page at table-creation time and the leaf’s page number is patched into the matching real-idx first_dp field. No prefix compression, no splits, no maintenance hooks — the leaf is empty at create time and any subsequent insert/update/delete leaves the index stale until Access rebuilds it on Compact & Repair. 5 unit tests in tests/JetDatabaseWriter.Tests/Indexes/IndexPageCodecLeafPageTests.cs plus 2 round-trip tests in IndexWriterTests.cs (CreateTable_WithIndex_EmitsLeafPageWithMatchingParent, CreateTable_WithMultipleIndexes_EmitsOneLeafPagePerIndex).
W4 B-tree split on overflow: emit intermediate 0x03 pages, maintain tail_page chain. high ✅ Implemented (2026-04-25). src/JetDatabaseWriter/Indexes/IndexBTreeBuilder.cs. Pure builder over a sorted leaf-entry list — packs entries into one or more chained leaf pages (prev/next sibling pointers per §4.1) and emits zero or more levels of intermediate (0x03) pages until a single root remains. Each intermediate entry mirrors the last entry of its child plus a 4-byte child-page pointer (§4.3). Returns (IReadOnlyList<byte[]> Pages, long RootPageNumber) so the caller can append the pages and patch the root into the real-idx first_dp. Sub-phase A shipped (2026-04-25): prefix compression — IndexPageCodec.BuildLeafPage accepts an enablePrefixCompression flag (default false for direct callers) and IndexBTreeBuilder.Build opts in for both leaf (0x04) and intermediate (0x03) pages. The longest byte-wise prefix common to every entry on a page is hoisted into the §4.4 pref_len header field and stripped from every entry beyond the first; the first entry is always written whole. Packing decisions still use uncompressed entry sizes, so leaf/intermediate count is unchanged from the pre-compression layout — sub-phase A is a strict on-disk size win without affecting tree shape. Sub-phase D shipped (2026-04-26): multi-level rebuild from existing tree — TryMaintainIndexesIncrementalAsync now handles trees rooted at an intermediate (0x03) page by descending through IndexPageCodec.ReadFirstChildPointer to the leftmost leaf, walking the leaf-sibling chain via IndexPageCodec.ReadNextPage, splicing the insert/delete diff into the collected entry list, and rebuilding via IndexBTreeBuilder. The bulk-path fall-through that fired for every multi-level tree is gone. Leaf splits and merges propagate correctly through any number of intermediate levels because the rebuild emits a fresh, well-formed tree when needed; when the rebuilt tree still fits on the existing single leaf, the writer now reuses that leaf in place for Compact & Repair compatibility. Superseded by W4-C surgical phases (2026-04-27): in-place mutation for non-append multi-page change-sets now lives in §7.12–§7.18; tail_page chain shipped in W18 (2026-04-26). Driven from W5 MaintainIndexesAsync. Microsoft Access compact-and-repair validation now passes for the covered create/table/FK flows. 9 unit tests in tests/JetDatabaseWriter.Tests/Indexes/IndexBTreeBuilderTests.cs plus 26 in IndexPageCodecAndEntrySplicerTests.cs and 10 round-trip tests in IndexMaintenanceTests.cs (3 of which exercise the new multi-level path).
W5 Index maintenance hooks in InsertRowDataAsync, UpdateRowsAsync, DeleteRowsAsync, and the copy-and-swap path used by AddColumnAsync / DropColumnAsync / RenameColumnAsync. high ✅ Implemented. Bulk-rebuild strategy: MaintainIndexesAsync decodes every live row through TableSnapshotReader.ReadRowsAsync, which reads each row from its own data page through the writer’s Pager and returns it with its location (so inside a transaction it sees the journaled rows, and each entry points at the row its key came from; the schema rewrite passes the rows it just copied, with the locations the inserts returned), encodes & sorts keys via IndexKeyEncoder, rebuilds a B-tree via IndexBTreeBuilder, and patches the root into the real-idx first_dp. When a rebuilt tree still fits on the existing single leaf, that leaf is rewritten in place; larger rebuilds emit fresh pages, update the real-index usage-map row, then deallocate replaced old index pages through the global free-list allocator for ordinary user tables. Jet3 rebuilds find replaced trees by walking old roots and preserve pages named by native index maps (§7.19). Newly emitted table indexes have map pointers, while general Jet3 index-map maintenance remains incomplete. Access system tables and generated complex flat child tables stay conservative because DAO CompactDatabase currently relies on their exact complex/system artifacts after schema rewrites. As of W19 (2026-04-26) every scalar JET column type Microsoft Access itself permits to index participates in the bulk rebuild: Byte, Integer, LongInteger, Money, Float, Double, DateTime, Numeric (W13), Guid (W12), Binary (W19), and Text / Memo across the entire BMP via the W7 General Legacy port. (OLE Object, Attachment, and Multi-Value (Complex) columns are not in this list because Microsoft Access itself does not permit indexes over them — ResolveIndexes rejects such IndexDefinitions with NotSupportedException per W18.) Prefix compression in emitted leaves and intermediates landed in W4 sub-phase A (2026-04-25). 7 round-trip tests in IndexMaintenanceTests.
W6 MSysIndexes / MSysIndexColumns catalog rows in BuildEmptyDatabase, gated on DatabaseFormat. medium ✅ Closed as not-needed. See §6 — zero of 88 fixtures across Access 97 → 2019 contain these tables; no work item.
W7 Text sort-key encoder (General Legacy first; General 1033v1 if a clean spec emerges). high; defer if possible ✅ Implemented (2026-04-26). Full Jackcess-port GeneralLegacyTextIndexEncoder replaces the ASCII-only stop-gap. Routes Text (0x0A) and Memo (0x0C) through a state machine that emits inline bytes + the EXTRA / UNPRINTABLE / CRAZY auxiliary streams with END_TEXT (0x01) / END_EXTRA_TEXT (0x00) framing exactly as Jackcess does, using the per-codepoint resource tables IndexCodeTables/index_codes_genleg.txt.gz (BMP U+0000–U+00FF) and IndexCodeTables/index_codes_ext_genleg.txt.gz (U+0100–U+FFFF) embedded as gzipped resources. Trailing-space stripping and the 127-char (MAX_TEXT_INDEX_CHAR_LENGTH = 255 / 2) prefix truncation match Jackcess. Descending support inverts the payload + END_EXTRA_TEXT in place and appends an unflipped trailing 0x00; the leading flag is never flipped. Surrogate pairs fall back to the documented 0x3F extra byte. Single- and multi-column text/memo keys participate in the W5 bulk B-tree rebuild and W11 multi-column composite key concatenation, including descending text columns (the previous fall-through path is gone). Remaining: secondary collation weights beyond what the resource tables encode. See §7.6. Apache 2.0 attribution recorded in the top-level THIRD-PARTY-NOTICES.md.
W8 Primary-key API (new ColumnDefinition(...) { IsPrimaryKey = true } plus a multi-column variant). PK is a unique non-null logical index with index_type = 0x01. small (depends on W1) ✅ Implemented. Two equivalent declaration paths: the ColumnDefinition.IsPrimaryKey shortcut (synthesizes a single PK IndexDefinition named "PrimaryKey") and an explicit new IndexDefinition(name, columns) { IsPrimaryKey = true }. Logical-idx index_type = 0x01; per §3.1 the real-idx flags byte stays at 0x00 (uniqueness lives in the discriminator). PK key columns are forced non-nullable. Mixing the column-flag shortcut with an explicit PK IndexDefinition, or declaring more than one PK, throws ArgumentException. Both single- and multi-column PKs participate in W5 / W11 live B-tree leaf maintenance — the W11 composite-key concatenation path covers PK uniqueness via the MaintainIndexesAsync logical-idx-walk that promotes any real-idx referenced by an index_type == 0x01 entry to unique. The W8 “multi-column PKs ship the schema only” caveat was lifted by W26 (2026-04-27); see §7.16. 16 round-trip tests in IndexPrimaryKeyWriterTests.
W9 Foreign-key / relationship API. Adds the 28-byte logical-idx entry with rel_idx_num / rel_tbl_page populated, plus the MSysRelationships row. medium ✅ W9a + W9b shipped — MSysRelationships row emission (W9a) plus per-TDEF FK logical-idx entries on both PK-side and FK-side TDEFs (W9b; Jet4/ACE at first, Jet3 since 2026-10, see §7.19). See §7.2.
W10 Cascade flags (cascade_ups, cascade_dels) and FK enforcement on insert/update/delete. The library has no SQL engine, so enforcement is a runtime check inside AccessWriter. medium ✅ Implemented. Runtime FK enforcement on InsertRowAsync / InsertRowsAsync (object[] and generic), UpdateRowsAsync, DeleteRowsAsync. Inserts validate FK-side parent presence (null components are allowed); updates validate FK-side parent presence only on the rows whose foreign key they change, as Access does (EnforceFkOnForeignUpdateAsync; a key left unchanged is not re-checked, even when it has no parent), and either cascade PK changes to dependents or throw based on CascadeUpdates; deletes either cascade or throw based on CascadeDeletes. Cascade depth capped at 64. Parent snapshots cached per public call (FkContext). Relationships with EnforceReferentialIntegrity = false are silently excluded. 11 tests in ForeignKeyEnforcementTests.
W11 Lift the W1-era restrictions on IndexDefinition: multi-column non-PK indexes, IsUnique (real-idx flags & 0x01), and DescendingColumns (per-column ascending flag cleared in col_order). Live W5 maintenance handles composite keys and per-column direction; unique violations throw InvalidOperationException after the bulk rebuild. medium ✅ Implemented (2026-04-25). Public surface adds IndexDefinition.IsUnique + IndexDefinition.DescendingColumns. ResolveIndexes accepts multi-column non-PK declarations. BuildTDefPageWithIndexOffsets writes col_order = 0x01 for ascending slots and clears the ascending bit (0x00) for descending slots, then sets the real-idx flags byte to 0x01 for non-PK unique indexes (PK uniqueness still uses the §3.1 logical-idx index_type discriminator with flags = 0x00). MaintainIndexesAsync was rewritten to read every populated col_map slot, encode each column with the slot’s direction, concatenate to a composite key, and (when the real-idx flag bit OR the logical-idx PK discriminator marks the index unique) detect adjacent-duplicate keys after the sort. RewriteTableAsync forwards HasUniqueFlag, DescendingColumns, IgnoreNulls and IsRequired through one projection (IndexHelpers.ProjectIndexes) shared by the rename-column and add/drop-column copy-and-swap paths; the add/drop path used to drop IgnoreNulls and IsRequired. The projection drops an index on a dropped column, and throws JetLimitationException naming an index whose key column has no name (a col_num the table does not have), where it used to drop that index too. Pre-write unique enforcement is superseded by W15; the previous “descending text indexes go stale” note is resolved as of W7 (2026-04-26) — descending text/memo keys now round-trip through the full Jackcess encoder. DAO CompactDatabase now covers representative mixed-direction composite text/integer indexes in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair; byte-for-byte comparison against an Access-authored descending leaf remains a separate probe gap. 15 round-trip tests in IndexWriterAdvancedTests. See §7.6.
W12 Extend IndexKeyEncoder to support Guid (0x0F) so single- or multi-column indexes whose key column is a GUID participate in the W5 live B-tree rebuild instead of falling through to the stale-leaf path. small ✅ Implemented (2026-04-25). IndexKeyEncoder.EncodeGuidEntry emits a 19-byte block: leading flag (0x7F asc / 0x80 desc) + two 9-byte segments (8 GUID display-order data bytes + length byte). On descending the data bytes and the FINAL length byte are bit-flipped but the intermediate 0x09 length byte is NOT flipped, matching Jackcess IndexData.writeGeneralBinaryEntry. GUID display order is byte 3,2,1,0,5,4,7,6,8..15 of the in-row storage layout (which is Guid.ToByteArray()-shaped). 7 unit tests in IndexKeyEncoderTests plus 2 round-trip tests in IndexWriterAdvancedTests (GuidIndex_BulkInsert_RebuildsLeafWithExpectedEntryCount, UniqueGuidIndex_DuplicateInsert_Throws). DAO CompactDatabase now covers a representative GUID-keyed index in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair; byte-for-byte comparison against an Access-authored GUID-keyed leaf remains a probe gap.
W13 Extend IndexKeyEncoder to support Numeric (0x10) (Decimal columns) so Decimal-keyed indexes participate in the W5 live B-tree rebuild instead of falling through to the stale-leaf path. medium ✅ Implemented (2026-04-25; descriptor-scale path corrected by W23). IndexKeyEncoder.EncodeNumericEntry produces a 1-byte flag + 17-byte payload (1 sign byte + 16-byte big-endian unsigned mantissa) using the Jackcess FixedPointColumnDescriptor (ACCDB / ACE) and LegacyFixedPointColumnDescriptor (Jet4 .mdb) byte-twiddling rules — see §7.4. Current rebuild and incremental paths call EncodeNumericEntryAtDeclaredScale, so the canonical sort-key scale is the column descriptor’s declared NumericScale, matching Access row storage; values with more fractional digits are rounded half-to-even before encoding. Mantissas that exceed 16 bytes after rescale throw NotSupportedException, which the W5 catch swallows to fall through to the stale-leaf path. Cross-sign descending order under the legacy form preserves the documented Jackcess MS KB 837148 quirk (negatives sort before positives in lex regardless of magnitude). DAO CompactDatabase now covers a representative NUMERIC-keyed index and descriptor-scale row storage in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair; byte-for-byte comparison against an Access-authored NUMERIC-keyed leaf remains a probe gap. 12 unit tests in IndexKeyEncoderTests plus numeric round-trip/incremental coverage in IndexWriterAdvancedTests and IndexNumericKeyIncrementalTests.
W14 DropRelationshipAsync and RenameRelationshipAsync — reverse of W9a/W9b for catalog rows; FK logical-idx entries on both side TDEFs are also removed by Drop. small ✅ Implemented (2026-04-25, extended by W26 2026-04-27, wide-TDEF mutation 2026-05-21, and DAO compact hardening 2026-05-23). DropRelationshipAsync(string) rewrites MSysRelationships as the remaining live rows, excluding every matching szRelationship, and removes the matching FK logical-idx entry from each side’s TDEF (Jet3 too since 2026-10) (matched by index_type == 0x02 + rel_tbl_page + exact backing real-idx col_map). Rewriting the system table live-only avoids relationship tombstones that DAO relationship enumeration and CompactDatabase reject. W26 (2026-04-27): trailing real-idx physical-descriptor slots that the removal left unreferenced are reclaimed via TryReclaimTrailingRealIdxAsync; mid-array orphans are still left for Compact & Repair (renumbering cross-tdef rel_idx_num references is out of scope). Wide-TDEF mutation (2026-05-21): drop/reclaim now operate on stitched logical TDEF buffers, so wide endpoint TDEF chains are handled; if a shrink no longer needs every old continuation page, the unreachable continuation pages are deallocated through the global free-list allocator after the rewritten chain is flushed. RenameRelationshipAsync(old, new) rejects collisions case-insensitively (via ReadExistingRelationshipNamesAsync), rewrites MSysRelationships as live rows with szRelationship replaced on every match, and short-circuits the no-op same-name path. The per-TDEF logical-idx name cookies on both sides are rewritten through the same logical-buffer layer so IndexMetadata.Name reflects the new name immediately on re-open, including when the rename grows into a continuation page. Type=8 relationship MSysObjects rows are emitted on create, renamed while preserving their security identity, and deleted with their matching MSysACEs rows on drop. Prevalidated full rows keep catalog and security index keys correct, including supported additional indexes. 12 round-trip test methods in RelationshipMutationTests; DAO CompactDatabase coverage for shortened-chain reclamation and rename on a multi-page child TDEF lives in DaoStorageMaintenanceTests.
W15 Pre-write unique-index enforcement: hoist the W11 post-write duplicate-key check ahead of the row encode so the offending row never hits disk. Single insert, batch insert (object[] + generic), and update paths all participate. small ✅ Implemented (2026-04-25). Helpers LoadUniqueIndexDescriptorsAsync, EncodeCompositeKeyForUniqueCheck, CheckUniqueIndexesPreInsertAsync, CheckUniqueIndexesPreUpdateAsync, CheckUniqueIndexesCore on UniqueIndexChecker and called from AccessWriter. The pre-write check parses the TDEF for unique real-idx slots (real-idx flags & 0x01 OR an associated logical-idx with index_type = 0x01), encodes a composite key per effective row using the same IndexKeyEncoder path the W5 bulk rebuild uses (including descriptor-scale NUMERIC encoding from W23), and throws InvalidOperationException on the first existing-vs-pending or pending-vs-pending collision. InsertRowsAsync materialises the batch up front so intra-batch duplicates are detected before the first InsertRowDataLocAsync call. UpdateRowsAsync reuses its already-loaded snapshot and substitutes the post-update payloads at the matching row indices before encoding. The W11 post-write check inside MaintainIndexesAsync is retained as defense-in-depth (it still fires for indexes whose encoder rejects the pre-write encode — e.g. text outside General Legacy). 7 round-trip tests in IndexPreWriteUniqueEnforcementTests. See §7.5.
W16 Replace the W10 O(N) parent-snapshot scan in EnforceFkOnInsertAsync with an O(log N) B-tree seek over the parent’s PK / FK index. Insert path only; cascade-update and cascade-delete on the parent (W10 child-table scan) still load a snapshot. small ✅ Implemented (2026-04-26). IndexCursor walks the parent’s real-idx first_dp through any number of 0x03 intermediate pages down to the matching 0x04 leaf, honouring the §4.2 entry-start bitmask and §4.4 prefix compression (re-prepends the leaf’s pref_len bytes to every entry beyond the first; intermediate descent picks the first child whose summary key is ≥ the search key). AccessWriter.EnforceFkOnInsertAsync resolves a per-relationship ParentSeekIndex (real-idx slot + per-column ascending flags + foreign-table column indexes), encodes the FK row’s composite key via the existing IndexKeyEncoder, and seeks. Resolution is cached on FkContext.SeekIndexes so a bulk insert hits the TDEF lookup once per relationship per call. CreateRelationshipAsync now calls MaintainIndexesAsync on both sides after EmitFkPerTdefEntriesAsync so pre-existing parent rows are visible to the cursor (the W9b empty leaf is replaced by a populated B-tree). Self-referential bulk inserts are still served correctly: the parent check first looks in the per-call FkContext.InsertedParentKeys set that AugmentParentSetsAfterInsert fills, so a row that satisfies its own FK against an earlier row in the same batch succeeds without waiting for the next index rebuild (see §7.7). Falls back to the W10 HashSet path when no covering parent real-idx exists, when first_dp == 0, or when the descriptor-aware seek encoder rejects a key component. Wide endpoints whose TDEF spans several pages are read through the stitched logical TDEF and seek like any other. Descriptor-scale Numeric relationship keys were routed into this seek path on 2026-05-22. Cascade-side seek superseded by W22 (2026-04-27) — EnforceFkOnPrimaryUpdateAsync / EnforceFkOnPrimaryDeleteAsync now also use index seek via the FK-side child real-idx; see §7.11. 3 new round-trip tests in ForeignKeyEnforcementTests (Insert_LargeParentTable_SeekPathFindsExistingKey, Insert_TextKeyFk_SeekPathHonoursGeneralLegacyEncoding, Insert_BulkChildren_SeekPathReusesParentIndexAcrossRows); the existing 11 W10 tests pass unchanged. Validation gap: the original W16 evidence used writer-emitted leaves. Current ForeignKeyIncrementalEvidenceTests starts from native-authored Int32 FK/PK trees and checks seeks, mutations and native compact; broader key/collation and page forms remain scoped by §8.
W22 Replace the W10 / W16 child-table O(N) snapshot scan inside EnforceFkOnPrimaryUpdateAsync and EnforceFkOnPrimaryDeleteAsync with an O(log N + K) B-tree seek over the child-side FK real-idx (the same per-TDEF entry W9b emits on every relationship). small ✅ Implemented (2026-04-27; descriptor-scale NUMERIC seek/row decoding added 2026-05-22). IndexCursor.FindRowLocationsAsync adds row-pointer enumeration on top of the W16 exists-only descent — collects every (dataPage, rowIndex) pair whose canonical key matches, including the sibling-leaf walk for non-unique keys. W22 also adds the ChildSeekIndex / ChildSeekKeyColumn records (Indexes/Models), RelationshipSeekPlanner.ResolveChildSeekIndexAsync (mirror of ResolveParentSeekIndexAsync against the child TDEF), IndexHelpers.TryEncodeChildSeekKey (encodes a typed parent-PK tuple via the descriptor-aware IndexKeyEncoder path; Numeric uses EncodeNumericEntryAtDeclaredScale; returns null on encoder rejection), RelationshipChildRowLocator.TrySeekChildLocationsAsync (resolves the seek hits to row locations) and PartialColumnReader.TryReadColumnValuesTypedAsync (single-row decoder at the row’s location; returns null if any in-scope column is Memo / Ole / Complex / Attachment because those require LVAL/complex traversal). W22 changed the EnforceFkOnPrimaryDeleteAsync signature from (string, IReadOnlyList<string?>, ...) to (string, TableDef primaryDef, List<object?[]>, ...) so the seek path could encode typed parent PKs, and extended the changes tuple of EnforceFkOnPrimaryUpdateAsync to carry OldFullRow so the OLD typed PK was available for the child-side seek; both called TryProcessCascadeXxxWithSeekAsync BEFORE the snapshot fallback. Since 2026-10 both enforcers are split into RelationshipEnforcer’s cascade plans, which seek the same way before the snapshot fallback (§7.11). Resolution cached on FkContext.ChildSeekIndexes (parallel to W16’s FkContext.SeekIndexes). Falls back to the legacy O(N) child snapshot when no covering child real-idx exists, when the FK key encoder rejects any component, or when the child row contains LVAL/complex columns the single-row reader cannot decode (cascade-delete recursion needs the typed full row to resolve grandchildren). CRITICAL implementation note (regression seen during dev): the snapshot-fallback deletedSet keys MUST be built with BuildCompositeKey(pk, identity) (where identity[i] = i), not an ad-hoc helper — BuildCompositeKey applies 'S:UPPER' for case-insensitive text, 'N:decimal' for cross-width numerics, 'D:UTC-ticks' for DateTime, 'G:HEX' for Guid, 'B:base64' for byte[], and '?:0/1' for booleans, all separated by '|'. Reinventing the normalisation produces non-matching keys and silently misses cascade victims. 2 new round-trip tests in ForeignKeyEnforcementTests (Delete_PkSide_WithCascade_BulkSeeksChildIndex, Update_PkSide_WithCascade_BulkSeeksChildIndex); the existing 14 W10/W16 tests pass unchanged. See §7.11.
W19 Extend IndexKeyEncoder to support Binary (0x09) so single- or multi-column indexes whose key column is a variable-length binary (≤ 255 bytes) participate in the W5 live B-tree rebuild and the W16 parent-PK seek path. small ✅ Implemented (2026-04-26). IndexKeyEncoder.EncodeBinaryEntry accepts byte[] (and ArraySegment<byte> / ReadOnlyMemory<byte> / Memory<byte>) and routes through a shared EncodeGeneralBinaryEntry(ReadOnlySpan<byte>, bool ascending) packer factored out of the W12 GUID path — both produce byte-identical output for 16-byte input, so the GUID byte sequence and existing GUID tests are unchanged. Variable-length: ⌈len/8⌉ 9-byte segments (8 zero-padded data bytes + 1 length byte: 0x09 for intermediate segments, the actual valid-byte count for the final segment). Empty input emits one zero-length final segment so empty values round-trip and sort below any non-empty value. Descending bit-flips data bytes and the FINAL length byte; intermediate 0x09 length bytes are NOT flipped (matches Jackcess IndexData.writeGeneralBinaryEntry). Binary was added to IndexKeyEncoder.IsColumnTypeSeekable so W16 parent-PK seeks engage on binary-keyed FKs (Jet3, Jet4 and ACE). 9 unit tests in IndexKeyEncoderTests (null, empty, 3 bytes, exactly 8, 9-byte multi-segment, descending flip rules, ascending + descending lex ordering, non-byte[] rejection) plus 4 round-trip tests in IndexBinaryKeyTests (bulk insert, unique-violation detection, multi-column composite Text+Binary, descending direction). DAO CompactDatabase now covers a representative BINARY-keyed index in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair; byte-for-byte comparison against an Access-authored BINARY-keyed leaf remains a probe gap. See §7.10 below.
W17 Lift the “Jet3 (.mdb Access 97) rejects IndexDefinition entirely” limitation. Pin the Jet3 real-idx (39 B), logical-idx (20 B), and leaf-page (0x04) layouts empirically; emit them from BuildTDefPageWithIndexOffsets; route Jet3 leaf builds through IndexPageCodec / IndexBTreeBuilder (page size 2048, bitmask at 0x16, first entry at 0xF8); thread Jet3 through MaintainIndexesAsync and the W15 unique pre-check. medium ✅ W17a + W17b + W17c + W17d shipped. W17a (probe + spec) shipped (2026-04-26): JetDatabaseWriter.FormatProbe WriteJet3IndexAppendixAsync dumps the TDEF + first leaf page for every user table with numIdx > 0 across 6 Jet3 fixtures (indexTestV1997.mdb, compIndexTestV1997.mdb, testIndexCodesV1997.mdb, testV1997.mdb, mdbtools/nwind.mdb, Jet3Test.mdb) into format-probe-appendix-jet3-index.md. Output confirmed (i) first_dp lives at phys-desc bytes 34..37 on Jet3 — not in the leading 8-byte skip-entry block as the mdbtools spec implies for Jet4-style used_pages; (ii) index_type = 0x01 PK discriminator at logical-idx byte 19; (iii) §4.2 leaf bitmask at 0x16 / first entry at 0xF8 with the 9-byte stride for LongInteger keys matching the bitmask LSB-first pattern exactly; (iv) §4.1 page header is identical between Jet3 and Jet4. Spec corrections landed in §3.1 (“Jet3 real-idx physical descriptor”), §3.2 (“Jet3 logical-idx entry”), and §4.2 (“Jet3 confirmed empirically”). W17b (TDEF + schema-only leaf emission) shipped (2026-04-26): the Jet3Mdb rejection in AccessWriter.CreateTableAsync is gone. BuildTDefPageWithIndexOffsets now branches on format and emits the §3.1 39-byte real-idx (no used_pages slot, first_dp at phys+34, flags at phys+38; the 2026-10 re-dump in §3.1 supersedes the “no used_pages slot” reading: col_map starts at byte 0, used_pages sits at phys+30..33 and first_dp at phys+34..37) and the §3.2 20-byte logical-idx (no leading cookie / trailing tail; index_num/index_num2/rel_idx_num/index_type at the shifted-left offsets). IndexPageCodec.BuildLeafPage emits a single empty leaf per index — page size 2048, header identical to Jet4, bitmask at 0x16 (no bits set on an empty page), first-entry slot at 0xF8, free_space = pageSize - 0xF8. CatalogArtifactWriter.CreateTableAsync (formerly AccessWriter.CreateTableInternalAsync) patches first_dp exactly the same way as Jet4. At the W17b checkpoint the Jet3 leaf was schema-only and stayed empty until Access rebuilt it on Compact & Repair; W17c below superseded that interim state by adding live Jet3 maintenance. Reader bugfix: AccessReader.ParseIndexMetadata was reading index_num / index_num2 from Jet4 offsets even on Jet3; the offsets are now shifted left by 4 bytes per §3.2, restoring round-trip through ListIndexesAsync. 5 round-trip tests in IndexWriterTests cover single index, primary key (via ColumnDefinition.IsPrimaryKey shortcut), three-index emission, leaf-page byte-pattern (page-type 0x04 count + free_space = 2048 - 0xF8), and the no-indexes regression path. W17d shipped (2026-04-26): TryMaintainIndexesIncrementalAsync and the helper DescendToLeftmostLeafAsync now branch on _format == DatabaseFormat.Jet3Mdb and select IndexPageLayout.Jet3 for IndexBTreeBuilder.Build, IndexPageCodec.DecodeLeafEntries, IndexPageCodec.TryBuildLeafPage, and IndexPageCodec.ReadFirstChildPointer. The §3.1 39-byte real-idx physical-descriptor walk uses realIdxPhysSz = 39 and physFirstDpOff = 34 instead of the Jet4 52 / 38. The W15 pre-write unique-index check (LoadUniqueIndexDescriptorsAsync) was extended in the same commit so the W11 post-write duplicate detection — previously the only safety net on Jet3, and skipped by the incremental path — is no longer relied on; PK / unique violations are caught before disk mutation via the §3.2 20-byte logical-idx layout (logIndexNum2Off = 4, logIndexTypeOff = 19). 2 new round-trip tests in IndexMaintenanceTests (IncrementalFastPath_SplicesSingleLeaf_OnInsertAndDelete, IncrementalFastPath_RebuildsMultiLevelTree) — the latter forces an intermediate (0x03) root by bulk-loading 400 INT rows then splicing one more, and asserts the post-rebuild file contains at least one 0x03 page and the reader sees all 401 rows. W17c (live leaf maintenance) shipped (2026-04-26): IndexPageLayout (Jet3 / Jet4 static instances) parameterises IndexPageCodec, IndexBTreeBuilder.Build, IndexPageCodec.DecodeLeafEntries / IndexPageCodec.TryBuildLeafPage / IndexPageCodec.ReadFirstChildPointer, and the writer’s create-time + bulk-maintain paths. MaintainIndexesAsync no longer short-circuits on Jet3Mdb — it reads the §3.1 39-byte real-idx + §3.2 20-byte logical-idx with per-format sizes (realIdxPhysSz, logIdxEntrySz) and offsets (physFirstDpOff = 34, physFlagsOff = 38, logIndexNum2Off = 4, logIndexTypeOff = 19) selected via a bool jet3 switch, then drives IndexBTreeBuilder.Build with IndexPageLayout.Jet3. The W15 pre-write unique pre-check participates because the same TDEF-walk helper now reads PK discriminators from the Jet3 logical-idx at the shifted-left offsets. 7 round-trip tests in IndexMaintenanceTests cover insert (single + bulk), update, delete (latest-leaf entry-count check), PK duplicate enforcement, text-key (General Legacy through Jet3), and AddColumn round-trip. The LimitationsTests.SchemaEvolution_* pinned tests still pass. The README no longer lists a Jet3 index-maintenance limitation. See §7.7 below.

7.1 W1 — what shipped (2026-04-24)

Kept as the foundational reference for every later writer phase.

Public surface added on IAccessWriter / AccessWriter:

What the writer emits into the new TDEF page (Jet4/ACE):

W1 explicit constraints (enforced by ResolveIndexes):

Validation: round-trip tests in IndexWriterTests. This W1 reference predates the broader DAO compact matrix; use writer-disk-format-validation-matrix.md for the current strongest validation level.

7.2 W2–W10 — additional notes

The per-phase summaries in the W-table above carry the public-surface delta and the explicit caller constraints. The implementation details (helper names, internal layout, test counts) live in the C# XML-doc summaries on the helpers themselves — grep for the helper name in src/JetDatabaseWriter/AccessWriter.cs and its domain modules (Indexes/, Schema/, Catalog/, ValueEncoding/, Transactions/, Relationships/). Notes that don’t fit naturally into either:

7.3 W11 — what shipped (2026-04-25)

Kept as a brief reference because W11 lifted the W1 single-column-non-PK restriction, which is widely cited.

Public surface added on IndexDefinition:

Validation: 15 round-trip tests in IndexWriterAdvancedTests plus representative DAO CompactDatabase coverage in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair. W11 expands the on-disk byte surface in two places: the flags byte at real-idx phys + 42 and the col_order byte in each col_map slot.

7.4 W13 — what shipped (2026-04-25)

Per-value layout (after the entry flag byte):

[sign byte (1)] [unsigned mantissa, big-endian (16)] = 17 bytes

Construction sequence:

  1. Decompose the .NET decimal into sign, natural scale, and 96-bit unsigned mantissa.
  2. Round to the column descriptor’s declared scale when necessary, then validate targetScale ≥ naturalScale; rescale via BigInteger.Pow(10, targetScale − naturalScale). Mantissa exceeding 16 bytes after rescale throws NotSupportedException (W5 catches and falls through to the stale-leaf path).
  3. Pack the rescaled magnitude into 16 BE bytes; prepend 0x80 if negative, 0x00 if positive.
  4. Apply the format-specific twiddling rules from Jackcess IndexData:
  Legacy (LegacyFixedPointColumnDescriptor, Jet4 .mdb) New-style (FixedPointColumnDescriptor, ACCDB / ACE)
asc & pos no flip; byte[0] = 0xFF byte[0] = 0xFF; no flip
asc & neg flip all 17 bytes; byte[0] = 0x00 byte[0] = 0xFF; flip all 17 bytes
desc & pos flip all 17 bytes; byte[0] = 0xFF byte[0] = 0xFF; flip all 17 bytes
desc & neg no flip; byte[0] = 0x00 byte[0] = 0xFF; no flip
  1. Prepend the entry flag byte unflipped (0x7F asc / 0x80 desc; 0x00 / 0xFF for null entries).

The original W13 implementation computed targetScale from each rebuild snapshot. W23 superseded that: MaintainIndexesAsync, incremental maintenance, and unique checks now use the column descriptor’s declared NumericScale. This matches Access row storage, where every normal Numeric fixed slot is 17 bytes ([sign][16-byte magnitude]) and the scale lives in the descriptor, not in each row cell.

W13 caveats:

7.5 W15 — what shipped (2026-04-25)

W15 hoists the W11 unique-index duplicate-key check from the post-write MaintainIndexesAsync rebuild to a pre-write pass that runs before any row is encoded onto disk. The on-disk byte format is unchanged — W15 is purely an in-memory validation. The post-write check is retained as defense-in-depth (it still fires for indexes whose encoder rejects the pre-write encode).

Helpers now live on UniqueIndexChecker and are called from AccessWriter:

Wiring:

W15 caveats:

7.6 W7 — what shipped (2026-04-26)

W7 replaces the W2-era ASCII-only stop-gap (0–9 / A–Z / a–z, NotSupportedException on anything else) with a full Jackcess-port “General Legacy” text-index encoder that covers the entire BMP. This unblocks:

New file: src/JetDatabaseWriter/Indexes/Collation/GeneralLegacyTextIndexEncoder.cs. Direct port of com.healthmarketscience.jackcess.impl.GeneralLegacyIndexCodes (Apache 2.0).

Embedded resources (gzipped to keep the assembly small):

Each line in the resource files declares one of six handler kinds:

Kind Letter Description
Ignored X Codepoint contributes nothing to any stream (e.g. control chars).
Simple S Inline bytes only — the common case for ASCII letters/digits.
International I Inline bytes + extra-codes stream bytes (for accent/case weights).
Unprintable U Unprintable-codes stream bytes only (no inline contribution).
UnprintableExt P Single unprintable-codes “ext-modifier” byte placed via the offset/midfix scheme.
InternationalExt Z Inline + extra + crazy-flag bit set on the running crazy-codes mask.
Significant G Inline bytes that participate in the post-END_TEXT extra stream (rare).

CharHandler subclasses (SimpleCharHandler, InternationalCharHandler, UnprintableCharHandler, UnprintableExtCharHandler, InternationalExtCharHandler, SignificantCharHandler, plus a sentinel IgnoredCharHandler) are tagged with a CharHandlerType enum and dispatched by the inner loop in Encode.

State-machine layout of the emitted entry block:

┌────────┬──────────────────┬─────────┬───────────────────┬─────────┐
│ flag   │ inline payload   │ END_TXT │ extra/unprint/    │ END_EXT │
│ (1 B)  │ (per-char S/I/Z) │ (0x01)  │ crazy aux streams │ (0x00)  │
└────────┴──────────────────┴─────────┴───────────────────┴─────────┘

Aux-stream serialization order after END_TEXT: trimmed extra-codes stream → unprintable codes (length-prefixed and offset-encoded with the 0x06 midfix) → crazy codes (terminated with the documented FF 02 80 FF 80 suffix). TrimExtraCodes strips the placeholder fill the international codes wrote — the trimming is the reason END_TEXT and END_EXTRA_TEXT are distinct bytes.

Descending pass: the entire payload region (flag-byte exclusive, trailing END_EXTRA_TEXT inclusive) is one’s-complemented in place; a fresh unflipped 0x00 is then appended so the descending entry still ends in 0x00 and can be parsed by the same scanner. The leading flag (0x80 for descending non-null) is never flipped — it is the descending signal itself.

Truncation + padding rules (matching Jackcess toIndexCharSequence):

Routing in IndexKeyEncoder.EncodeEntry:

if (columnType == Text || columnType == Memo)
{
    return GeneralLegacyTextIndexEncoder.Encode(ToText(value!), ascending);
}

Memo (Memo, 0x0C) is routed through the same encoder and uses the same bounded prefix, including the General Legacy CR/LF exception above. The reader-side LVAL chain is not re-fetched during indexing — only the inline preview / loaded string value is encoded.

Wiring + subsystems updated:

Validation:

W7 caveats / not done:

7.7 W16 — what shipped (2026-04-26)

CreateRelationshipAsync checks existing rows before writing relationship metadata. For an enforced relationship, each foreign key with no null component must match a parent key using the parent column descriptors and their text collation. Keys with a null component are exempt, and relationships with EnforceReferentialIntegrity = false allow orphaned rows.

Replaces the W10 O(N) parent-table snapshot inside EnforceFkOnInsertAsync with an O(log N) B-tree seek over the parent’s PK / FK index. Cascade-update / cascade-delete on the parent (EnforceFkOnPrimaryUpdateAsync, EnforceFkOnPrimaryDeleteAsync) shipped under W22 (2026-04-27); see §7.11.

New helper:

Writer-side wiring (in AccessWriter):

Self-referential bulk inserts: AugmentParentSetsAfterInsert records the key of every row a batch inserts in FkContext.InsertedParentKeys[rel.Name], for each relationship that relates the table to itself, and RequireParentKeyAsync checks that set before attempting the seek, so a row that satisfies its own FK against an earlier row in the same InsertRowsAsync batch succeeds without re-running the index rebuild. Until 2026-10 the keys went into FkContext.ParentKeySets, and only once the seek path had created that relationship’s entry, which it did on the first non-null key: in a batch such as [[5, null], [6, 5]] row 5 was never recorded and row 6 was refused on Jet4 and ACCDB (Jet3 passed, because its key set re-reads the data pages). The seek path also seeded an empty ParentKeySets entry, so a key the encoder rejected was checked against that empty set instead of the parent’s rows; ParentKeySets now holds only the parent snapshot.

W16 fallback triggers (drop back to the W10 HashSet path):

Unresolvable relationships (2026-10): an enforced relationship whose primary or foreign table, or one of whose key columns, cannot be found fails closed. Every write the relationship would have to check throws InvalidOperationException (“Foreign-key constraint ‘X’ cannot be enforced: its primary table ‘T’ was not found”, or “table ‘T’ has no column ‘C’”): an insert with a non-null foreign key (any insert when a foreign-key column itself is missing), an update that changes the foreign key, an update that changes the referenced key, and a delete of primary-table rows whose key is not null (any matching delete when the primary column itself is missing). The HashSet path used to read a missing parent as an empty table and report “no matching row”, and the primary side threw a bare “Table ‘T’ was not found.”, or skipped a relationship whose key column was missing. Null foreign keys (unless a foreign-key column itself is missing), updates that leave the key alone and deletes that match nothing never resolve the relationship, so they still succeed. The error comes before any row is written, even when another relationship of the same write cascades: an update resolves every relationship whose referenced key it changes before it rewrites any dependent row, and a delete finds every dependent row, through every cascade level, before it deletes any (§7.11). DropRelationshipAsync removes such a relationship.

Validation: 3 new round-trip tests in tests/JetDatabaseWriter.Tests/Relationships/ForeignKeyEnforcementTests.cs — large parent (5 000 rows, deep-key probe), text key (General Legacy round-trip on the seek path), bulk children (per-call cache reuse). The existing 11 W10 tests pass unchanged. Validation gap (§8): native-authored Int32 parent/child trees are now exercised by ForeignKeyIncrementalEvidenceTests, including native seek/edit/compact. This does not close the broader native key/collation and page-form inventory in the validation matrix.

7.8 W17a — what shipped (2026-04-26)

W17a is the empirical-probe sub-phase only. No AccessWriter or AccessReader behaviour changes; the _format == DatabaseFormat.Jet3Mdb rejection in CreateTableAsync stays in place until W17b lands.

What landed:

What W17a confirmed (carried into §3.1, §3.2, §4.2):

The read-only W17a probe left the following questions, now characterized by Jet3IndexDefinitionTests against DAO3.6:

W17a validation gap: the probe is read-only on real Access-authored bytes, so there is no Microsoft Access compact-and-repair concern at this phase. The §8 standing gap applies again starting at W17b.

7.9 W4-C-1 + W4-C-2 — what shipped (2026-04-26)

W4-C-1 (single-leaf incremental INSERT) and W4-C-2 (single-leaf incremental DELETE) lift the W5 “every mutation rebuilds the whole B-tree from a snapshot” cost when the index B-tree is rooted on a single leaf page (page_type = 0x04, no sibling pointers, no tail-page chain) AND the post-mutation entry list still fits on that one page. Multi-level trees and overflowing single-leaf trees fall back to MaintainIndexesAsync exactly as before. (Numeric-keyed indexes also fell back to bulk in this sub-phase pending the W13 per-rebuild canonical-scale pre-pass; superseded by W23 (2026-04-27) — the canonical scale now comes from the column’s declared scale persisted in the TDEF descriptor, so numeric keys participate in the fast path; see §7.13.)

Current helper ownership, after the original W4-C helper facade was deleted on 2026-05-31:

New writer-side helper, now owned by IndexMaintainer: TryMaintainIndexesIncrementalAsync(tdefPage, tableDef, insertedRows?, deletedRows?, ct). Returns true when every supported real-idx was maintained incrementally; the caller MUST then NOT call MaintainIndexesAsync. Returns false on any fall-back trigger:

Per-call hint shape: each public mutation builds IReadOnlyList<(RowLocation Loc, object[] Row)> for inserted and/or deleted rows and passes them as the hint. The hint is consumed by EncodeHintEntries (per real-idx, per row, encoded in col_map order).

Wiring in AccessWriter:

Pre-write unique-index enforcement (W15) still runs before the data-page mutation, so the fast path does not re-check uniqueness post-write. The bulk path’s post-write check (defense-in-depth for encoder-rejected indexes) is unchanged.

Page reuse: when the fast path succeeds, the existing single leaf is rewritten in place and first_dp remains stable. This keeps the table’s real-index usage-map row valid and avoids creating orphaned leaves during ordinary single-leaf insert/update/delete maintenance.

Validation:

W4-C-1/W4-C-2 caveats:

7.10 W19 — what shipped (2026-04-26)

W19 closes the last remaining IndexKeyEncoder gap on the supported-by-Access list: variable-length Binary (0x09) columns can now back a live, B-tree-maintained index. Prior to W19 an IndexDefinition over a binary column would throw NotSupportedException from the encoder on the first row insert that triggered MaintainIndexesAsync.

Encoding (IndexKeyEncoder.EncodeBinaryEntry):

Implementation note — refactor of W12 GUID path:

W16 parent-PK seek participation:

Validation surface:

W19 caveats:

7.11 W22 — what shipped (2026-04-27)

Changing a referenced non-null key to a key containing Null follows the same dependency checks as any other key change. A restrictive relationship refuses the update while dependent rows remain. A cascading relationship copies the new key, including its Null components, to dependent rows; all cascades validate assigned child constraints and recompute calculated values before writing, including rows reached through a self-relationship. Required child keys therefore refuse the entire update before any mutation. An old key containing Null has no dependents to move.

W22 closes the W16 follow-up — EnforceFkOnPrimaryUpdateAsync and EnforceFkOnPrimaryDeleteAsync no longer load an O(N) child snapshot per relationship per call. They now use an O(log N + K) seek over the FK-side child real-idx that W9b already emits on every relationship.

New cursor surface:

Additions, named where they live now:

Signature changes:

Both cascade enforcers called TryProcessCascadeXxxWithSeekAsync BEFORE the snapshot fallback and returned bool so the snapshot path ran only when the seek path could not complete.

PlanCascadeDeletesAsync walks the cascade before any row effect. It resolves each relationship and its dependent rows through the child seek or snapshot fallback, refuses restrictive relationships, and collects hidden complex children. Caller-owned locations and rows already reached by another cascade are excluded after restriction checks, so self and overlapping cascades cannot delete or count a row twice. AddCascadeDeletesAsync contributes deepest cascades and flat-child deletions to one shared MutationPlan in their planned order.

PlanCascadeUpdatesAsync likewise prepares final dependent rows before writing. It counts restrictive dependencies without decoding complete rows when a safe child seek exists, combines all key changes for a reached child, and folds self-cascade assignments into caller rows rather than creating a second rewrite. Unreadable carried MEMO/OLE values refuse. Unique checks use the composed final rows for each table, including caller and cascade rows together for a self-related table.

MutationExecutor.ApplyAsync checks every replacement’s arity, unreadable-value admission and encoded capacity before the first row effect. AddCascadeUpdates, complex-child staging and append-only history contribute to the same per-TDEF changes as the caller. The executor applies each table’s prepared rows, then maintains its indexes/count/counters once under its explicit native policy. Incomplete original index hints retain the rebuild fallback. Unassigned fixed Text bytes and secure-erase/LVAL deletion rules travel with their individual row changes.

Cancellation is checked throughout preparation and shared application. A cancellation or failure during effects is handled by the owning TransactionLifecycle statement/savepoint, which restores its pages, counters and derived state. No cascade or complex planner owns a competing rollback loop. Commit retains its separate cancellation/fence rules; private initialization or replacement callers must preserve their unpublished destination on failure. UpdateDeleteCancellationTests, MutationPipelineTests and the statement fault partitions retain these containing-path obligations.

CRITICAL implementation note (regression seen during dev):

Falls back to the legacy O(N) child snapshot when:

Validation:

W22 caveats:

7.12 W4-C-3 + W4-C-4 — what shipped (2026-04-27)

W4-C-3 (surgical multi-level descent + leaf-local mutate) and W4-C-4 (surgical leaf split + propagate up) close the longest-standing W4 follow-up: non-append mutations against a multi-level tree no longer always re-emit the whole index on a fresh page-number range and orphan every old page. When every change in the batch lands on the SAME leaf, the affected pages are now mutated in place at their existing page numbers — zero orphans on the W4-C-3 path, exactly one new appended page on the W4-C-4 split path.

Current helpers:

IndexMaintainer.TryMaintainIndexesIncrementalAsync calls the shared IndexBTreeEditor.TrySurgicalMultiLevelMaintainAsync planner after the tail-append fast path. It supplies the index root’s TDEF offset as well as the encoded changes.

  1. Capture the first key’s descent and confirm whether the remaining changes use that path. The one-leaf case retains its small representation and stages the spliced leaf once.
  2. If that leaf needs grouped ancestor handling, continue with its prepared splice, siblings and original maximum key. General planning groups changes by target leaf; clustered keys reuse captured paths instead of descending again.
  3. Stage leaf replacements, splits, merges and ancestor updates before publication. The single-group and general cases share the same publication plan and commit code. Catalog-specific ancestor helpers remain available separately.
  4. Publish appended pages, neighbor patches and dependent leaf/ancestor rewrites before an optional root-pointer update. Reserved pages are released if planning or reservation cannot complete. Pending changes to earlier index descriptors are flushed before this publication can patch the TDEF.
  5. Return both whether the mutation was handled and whether its root changed. The maintainer reloads TDEF bytes only after a root change.

Malformed or unsupported paths, unsafe neighbor relationships and bounded-group refusals retain their bulk fallback. Relationship-backed indexes retain their separate native-interoperability rebuild policy. The phase notes below describe the historical split/merge extensions; the current planner shares their staging and publication machinery.

Validation:

W4-C-3 / W4-C-4 caveats:

7.13 W23 — what shipped (2026-04-27)

W23 drops the Numeric bail from every incremental fast path (W4-C-1, W4-C-2, W4-C-3, W4-C-4, W18, W4-D). Before W23 every NUMERIC-keyed mutation fell through to the bulk rebuild because the canonical sort-key scale required a snapshot of every live row to compute max(natural-scale) per index. After W23 the canonical scale is the column’s DECLARED scale, persisted in the TDEF column descriptor and read on load — the same scheme Microsoft Access itself uses.

What’s persisted:

What changed in encoding: new IndexKeyEncoder.EncodeNumericEntryAtDeclaredScale(value, ascending, declaredScale, legacy) rounds the input value to the declared scale via decimal.Round(d, declaredScale, MidpointRounding.ToEven) before delegating to the existing EncodeNumericEntry. This matches Access’s “round half to even” (banker’s rounding) on store. Half-even is MidpointRounding.ToEven’s default behaviour and is what Access has used since at least Jet 4.

Where the new path engages:

Metadata preservation:

Validation:

W23 caveats:

7.14 W4-C-6-v2 — what shipped (2026-04-27)

W4-C-6-v2 closes the rightmost-leaf-merge bail in AccessWriter.TrySurgicalMultiLevelMaintainAsync. Before this phase, when the dead leaf was the parent intermediate’s last child (mergeParent.TakenIndex == mergeParent.Entries.Count - 1) the cross-leaf path bailed because removing that child would shrink the parent’s tail_page header (§4.5) and the surgical commit had no way to recompute it. After this phase the merge engages on the rightmost-child case as well, and the new tail_page value cascades up every captured ancestor whose own rightmost child is the parent we just modified.

What changed in TryStageIntermediateRewrites (the deepest-first intermediate-rewrite loop):

What did NOT change:

Validation:

W4-C-6-v2 caveats:

7.15 W4-C-7-v2 — what shipped (2026-04-27)

TryStageIntermediateRewrites was renamed TryStageIntermediateRewritesAsync and converted to async so the higher-level intermediate split case (children are themselves intermediates, not leaves) can read the rightmost child’s effective tail_page on demand. The previous v1 bail (if (!parentOfLeaf.Contains(deepest)) return false;) is removed; mid-level and root-level intermediate overflows now split in place identically to the parent-of-leaf case shipped in W4-C-7 v1.

The new GetEffectiveTailPageAsync(intermediatePage, overrides, rewrites, ct) helper returns the rightmost-leaf page reachable through the supplied intermediate’s subtree, consulting (in order) the per-page intermediateTailOverrides dictionary populated as deeper intermediates are processed in this batch, the staged existingPageRewrites byte map, and finally the cache-backed live page. After splitting any intermediate, both halves’ tail values are recorded in intermediateTailOverrides so a shallower split that subsequently looks up either page picks up the post-split value rather than the now-stale live header.

Mutable plumbing: the ref nextAllocatedPageNumber + out long? newRootPage parameters could not survive the conversion to async, so the new IntermediateStagingState class wraps both and is passed by reference through TrySurgicalMultiLevelMaintainAsync → TryStageIntermediateRewritesAsync.

Tests: 3 new round-trip tests in IndexSurgicalRecursiveIntermediateSplitTests exercise multi-level (composite 255-byte TEXT key) trees through bulk insert + cross-leaf delete, drip-feed inserts that cascade splits up through mid-level intermediates, and delete-and-reinsert cycles. Representative ACE/Jet4 DAO CompactDatabase coverage for advanced key encodings and post-insert update/delete/insert B-tree maintenance now lives in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair; byte-for-byte Access-authored parity remains tracked in §8.

W4-C-7-v2 caveats:

7.16 W26 — what shipped (2026-04-27)

Closes the two W14 follow-ups plus a stale README claim about multi-column primary keys.

A. DropRelationshipAsync trailing real-idx reclaim. TryRemoveFkLogicalIdxEntryAsync now returns the real-idx slot number it severed (instead of bool). After both PK-side and FK-side removals succeed, DropRelationshipAsync calls TryReclaimTrailingRealIdxAsync(tdefPage, ct) once per affected TDEF (skipping the second call when self-referential). The reclaim helper:

  1. Walks the post-removal logical-idx entries to build a bool[numRealIdx] referenced map (each logical-idx points at its real-idx via index_num2 at offset +8).
  2. Counts contiguous trailing unreferenced slots.
  3. Removes the corresponding leading real-idx skip block entries (12 bytes each on Jet4) AND trailing real-idx physical descriptors (52 bytes each on Jet4) via two left-shifts.
  4. Decrements num_real_idx and updates tdef_len.

The “trailing only” restriction sidesteps the cross-TDEF renumbering problem: if we removed a non-trailing slot, every other TDEF whose logical-idx entries store rel_idx_num > R referencing this TDEF would need its rel_idx_num decremented. Trailing-only reclaim leaves every still-referenced slot’s index unchanged, so no other TDEF needs to be touched. In the common case (create + drop), the dropped FK got the last slot and is reclaimed cleanly; multi-drop in any order still reclaims every now-trailing orphan.

B. RenameRelationshipAsync TDEF logical-idx name cookie update. After MSysRelationships is rewritten as live rows with the new szRelationship, the rename method groups matches by (PK table, FK table) pair (mirroring DropRelationshipAsync), resolves both side TDEFs and column numbers, and calls TryRenameFkLogicalIdxNameAsync(tdefPage, columnNumbers, otherTdefPage, newName, ct) on each side. The helper:

  1. Locates the matching FK logical-idx entry the same way drop does (index_type == 0x02 + rel_tbl_page + RealIdxColMapMatches).
  2. Walks the names section to find the entry’s variable-length name byte range.
  3. Computes delta = newNameRecordSize - oldNameRecordSize and shifts the trailing variable-column block (rest of names + var-col block) by delta via Buffer.BlockCopy (overlap-safe).
  4. Writes the new length-prefixed name (UTF-16 on Jet4/ACE, ANSI on Jet3, JetFormat.EncodeTDefNameRecord) and updates tdef_len. The entry and its name then move together to the new name’s position under the database’s Access collation on every format.

The hidden parent-side name survives rename. The child-side entry takes the public relationship name, including on self-referencing relationships. Disambiguation against existing names on the same TDEF runs through PickUniqueLogicalIdxNameAsync (using MakeUniqueLogicalIdxName). Growth is handled by the logical TDEF-chain writer, so a rename can spill into a continuation page instead of leaving the cookie stale.

C. Multi-column primary keys participate in live B-tree leaf maintenance. This was effectively shipped as a side effect of W11 (composite-key concatenation) + W23 (canonical NUMERIC scale from descriptor); the README and §7 W8 row both still claimed multi-column PKs “ship the schema only”. The W11 MaintainIndexesAsync already iterates per real-idx, encodes each column with its slot direction, concatenates to a composite key, and promotes any real-idx referenced by a index_type == 0x01 logical-idx entry to unique. PK uniqueness is handled by the same logical-idx-walk that promotes non-PK unique-flag indexes; multi-column PK leaf maintenance has worked since W11 (2026-04-25) without being claimed.

Tests: 3 new round-trip tests in IndexPrimaryKeyWriterTests (CompositePrimaryKey_OnInsert_ParticipatesInBulkRebuild, CompositePrimaryKey_OnUpdateAndDelete_LeafReflectsLatestState, CompositePrimaryKey_SurvivesAddColumn_LeafRebuilt) confirm the PK leaf is maintained on bulk insert, update + delete, and AddColumnAsync rewrite. Relationship mutation coverage in RelationshipMutationTests confirms rename/drop via IndexMetadata.Name + IndexMetadata.RealIndexNumber round-trips, including multi-page endpoint TDEFs. Current DAO compact coverage for shortened-chain reclamation and relationship rename lives in DaoStorageMaintenanceTests, while mid-array drop orphan byte parity remains a residual gap.

W26 caveat:

Shortened logical TDEF chains no longer leave unreachable continuation pages behind; the old pages are deallocated through the global free-list allocator after the rewritten chain is flushed.

7.17 W4-C-8 — what shipped (2026-04-27)

The single-leaf and grouped phases of TrySurgicalMultiLevelMaintainAsync handle leaf and intermediate splits of arbitrary width — the previous “3+ pages → fall through to W4-D bulk rebuild” floor is gone.

What changed:

Tests: 5 new round-trip tests in IndexSurgicalNWaySplitTests cover (1) a 40-row bulk insert whose splice spans 3 leaf pages on a fresh table, (2) a 200-row bulk insert spanning ~10 leaf pages, (3) a small seed + 60-row append batch onto a single rightmost leaf (exercises tail_page cascade), (4) a deeply seeded tree + drip-fed inserts + final 50-row clustered batch that triggers an N-way leaf split feeding multiple summaries into a near-capacity parent intermediate (W4-C-8 + W4-C-7-v2 interaction), and (5) post-N-way uniqueness enforcement on every leaf page produced by the split. The full 2345-test suite passes; representative DAO compact coverage for advanced-index maintenance now lives in AdvancedIndexKeysAndBTreeMaintenance_SurviveCompactAndRepair, with per-sub-phase byte parity still tracked in §8.

W4-C-8 caveats:

7.18 W4-C-8+ — what shipped (2026-04-27)

Closes the trailing surgical-bail listed under §7.17 caveats: a multi-group delete batch that empties an entire intermediate page subtree no longer falls through to the W4-D bulk rebuild. The previous bail in TryStageIntermediateRewritesAsync

if (newEntries.Count == 0) return false;  // pre-W4-C-8+

…has been replaced with a recursive collapse that orphans the now-empty intermediate, emits a Remove op against the grandparent at the captured IndexInParent, and re-enqueues the grandparent into pending so the same machinery can chain another collapse if the grandparent itself empties out. Only when the empty page has no captured grandparent (true root collapse) does the path bail; that case is rare and W4-D handles it cleanly by allocating a fresh single-leaf root.

What changed:

Tests: 3 new round-trip tests in IndexSurgicalCascadingIntermediateCollapseTests:

Running the full 2348-test suite passes. Validation gap §8 still applies.

W4-C-8+ caveats:

7.19 Jet3 FK logical-index entries — what shipped (2026-10)

Before this, RelationshipManager only had Jet4 layout code and skipped Jet3: CreateRelationshipAsync wrote the MSysRelationships rows but no TDEF entries, DropRelationshipAsync left the entries of an Access-authored relationship in place, and a schema rewrite (AddColumn / DropColumn / RenameColumn) removed the partners’ entries instead of re-linking them. Enforcement was unaffected, since it reads MSysRelationships.

Tests: Jet3ForeignKeyIndexTests (byte layout, linking, sharing, cascade bytes, leaf coverage and enforcement, drop of writer- and Access-authored relationships, rename order, self-referencing and multi-column relationships, a continuation-page spill, rollback, every write mode, fresh writer-created Jet3 relationship enforcement, and SingleMutations_InTableWithForeignKeyEntry_ReclaimReplacedTrees for the replaced trees); IndexLayoutWriterTests; the Jet3 rows of RelationshipSchemaRewriteTests.SchemaRewrite_OfAccessAuthoredTable_KeepsEveryForeignKeyLinkConsistent (indexTestV1997.mdb and mdbtools nwind.mdb) and IndexMaintenanceInTransactionTests.SingleInserts_IntoChildWithForeignKeyIndex_StayIncrementalAndKeepIndexesConsistent.

DAO3.6 accepts native-authored Jet3 tables with natively initialized relationship storage after writer relationship creation and rename, enforces invalid-child refusal and configured cascades before compact, and compacts/readbacks the result. New FK descriptors have nonzero usage-map pointers; existing native map members remain current without changing unrelated rows. DaoSelfRelationshipUpdateTests and DaoJet3CascadeCycleTests add native simultaneous self-key and cyclic-update/delete evidence. NativeJet3SchemaRewriteTests separately covers CP1252/CP932 tables, continued TDEFs, catalog identity, raw keys and owned-map growth. Fresh writer-created Jet3 databases include native core catalog tables, ownership and permissions. Jet3NativeBootstrapTests verifies native open, row mutation, first relationship enforcement after writer reopen and compacted readback. Jet3SystemOwnedMapTests and Jet3SystemUsageMapDaoTests verify first allocation, core REFERENCE maps and isolation of shared rows/bitmaps, including native catalog growth, cascades, query creation and compact. Jet3IndexUsageMapTests covers current tree maps, native REFERENCE promotion and FK spill allocation when existing map pages are naturally full. Jet3 roots and continuations carry VC.

8. Validation strategy

The current cross-feature coverage matrix lives in writer-disk-format-validation-matrix.md. General DAO validation rules live in dao-validation-strategy.md. Update the matrix whenever a writer disk-format feature gains stronger coverage.

Every new writer phase landing on disk format should be validated against:

  1. Round-trip in this repo: writer -> reader (using the relevant parser) -> assert structural equivalence.
  2. DAO on Windows: when possible, mutate an Access-authored fixture such as Northwind, run DAO.DBEngine.120.CompactDatabase, reopen the compacted file, and verify the feature that motivated the test.
  3. Cross-tool sanity: open with Jackcess or mdbtools (mdb-schema -E) and confirm the index metadata matches.
  4. Manual Access UI verification: keep manual Compact and Repair notes as supplemental evidence for UI-only workflows, not as the only signal when DAO can automate the same mutation.

Automated DAO coverage now exists for the matrix rows covering the highest-risk PK/FK compact paths, multi-table index/FK stress, storage-maintenance index-page reuse and TDEF-chain reclamation, encrypted compact, fresh ACCDB bootstrap, a representative Northwind-hosted complex-column/LVAL compact path, representative ACE/Jet4 advanced index key encodings plus B-tree maintenance, and conditional Jet3 writer-emitted primary/normal index maintenance in an Access-authored .mdb host when the installed DAO engine can open Access 97 files. Treat remaining §7 validation-gap notes as byte-level parity or per-format residual gaps, not as blanket statements that DAO compact coverage is absent. Promote any remaining high-risk residual gap into DaoValidationTests.cs, DaoStorageMaintenanceTests.cs, or AccessRoundTripTests.cs.

RenameIndexAsync rewrites only logical index name records and their ordering, preserving each entry’s index_num, physical descriptors, roots, data and partner references. The bounded logical-chain writer handles continuation growth/shrink and retains uninterpreted trailing bytes and original padding. Matching subtype-2 index property targets follow the rename; column/object targets with the same name remain unchanged. Orphan destination index properties refuse before writes to avoid rebinding unknown metadata. Both definition and schema-traversal byte budgets apply before growth. RenameIndexTests checks exact unrelated-page and descriptor preservation, wide definitions with LVAL maps, metadata-target identity, PK/FK constraints, byte-exact rollback and partial-write retry.

9. Residual work and compatibility gaps

The W-table in §7 records both historical shipped phases and current behavior. All W4 B-tree maintenance follow-ups listed in earlier revisions are now shipped: tail-page append handling, single-leaf mutation, same-leaf and cross-leaf surgical mutation, leaf merge, N-way leaf/intermediate split, and recursive intermediate collapse. Keep the detailed phase notes in §7 as implementation history; treat the items below as the remaining live follow-ups.

10. References

Native Jet3 stored Name/Type/Version keys retain an embedded NUL weight 0x10, while DAO3.6 parameterized expression comparison treats embedded NUL as ignored. General97EncoderFixtureTests independently checks the frozen composite stored key and fresh native equality/ordering with zero- and 126-character prefixes. The comparison-key path omits the NUL weight; stored-index encoding retains it.

This site is open source. Improve this page.