System Limitations & Architectural Invariants

This document catalogs the architectural limits, binary format invariants, and deliberate scope boundaries of WebDB V1.

WebDB is engineered for ultra-lean, deterministic execution inside browser runtimes (WebAssembly, Web Workers, OPFS, and IndexedDB). To guarantee zero dynamic heap thrashing, instant startup times, and sub-100 KB binary footprints, specific physical boundaries are enforced across storage, virtual machine execution, and query compilation.

---

1. Summary of System Limitations

Category	Parameter	V1 Limit	Error Thrown / Behavior on Breach
Storage	Page Size	4,096 bytes (4 KB)	Rigid physical constant.
Storage	Maximum Row Size	2,048 bytes	RowSizeLimitExceededError (compile/insert time)
Storage	Maximum Database Size	16 TB (physical)	Browser Storage Quota Exceeded (OS/device limit)
Storage	Maximum Tables per DB	16 tables (Page 1) / $\mathrm{\infty }$ (chained)	CatalogFullError (forward-compatible to $\mathrm{\infty }$)
Storage	Maximum Columns per Table	256 columns	TooManyColumnsError (DDL time)
Storage	Maximum Identifier Length	64 characters	IdentifierTooLongError (DDL time)
VM	Register File Size	64 registers per frame (r[0]..r[63])	TooManyRegistersError (compile time)
VM	Active Cursor Slots	16 cursors per frame (c[0]..c[15])	TooManyCursorsError (compile time)
VM	Subquery Nesting Depth	8 frames (depth 0..7, correlated only)	SubqueryNestingTooDeepError (compile time)
VM	Output Result Buffer	64 KB	Yields STATUS_BUFFER_FULL (chunked streaming)
VM	Transient Query Arena Ceiling	16 MB (default, configurable)	QueryArenaExhaustedError (fail-fast OOM)
Query	Maximum Sort Columns (ORDER BY)	8 columns	TooManyOrderByColumnsError
Query	Maximum Group By Columns (GROUP BY)	8 columns	TooManyGroupByColumnsError
Query	Secondary Indexes	8 indexes (Page 1), up to 8 columns each	TooManyIndexesError (DDL time)
DDL	Schema Mutations (ALTER TABLE)	$O(1)$ rename; transactional table rebuild (ADD/DROP COLUMN)	CannotDropColumnError, NotNullConstraintError
Query	Table Joins	Up to 16 tables per query level	TooManyCursorsError if > 16 cursors needed
Query	Subqueries	Correlated depth ≤ 7; sequential unlimited	SubqueryNestingTooDeepError (compile time)
Query	Client-Side Projections (projectRow)	Terminal execution only (not in subqueries)	VM executes native bytecode; JS projections bypassed
Concurrency	Concurrent Writers	1 writer (Web Locks API)	Serialized in FIFO order via browser locks
Concurrency	Active Transactions per Connection	1 top-level (nested via savepoints)	Queued via ExclusiveLeaseQueue; tx.transaction() uses savepoints

---

2. Storage & Binary Layout Invariants

2.1 Rigid Page Size (4,096 Bytes)

• Invariant: Every database page, internal node, leaf node, and WAL frame is sized to exactly 4,096 bytes ($2^{12}$).
• Rationale: Aligns with standard operating system virtual memory page sizes and browser block I/O (IndexedDB block clustering and OPFS sync blocks). Eliminates variable-length page header decoding.

2.2 Maximum Row Size (2,048 Bytes) & No Overflow Pages

• Invariant: Serialized row records (header + null-bitmap + fixed-size columns + var-length strings/blobs) must never exceed 2,048 bytes ($4096/2$).
• Mathematical Proof: A B+Tree leaf page requires at least 2 records to maintain B-Tree balance invariants during node splits without cascading page collapses. Capping row size at $\frac{\text{Page Size}}{2}=2048\text{ bytes}$ guarantees every leaf page can hold at least 2 cells plus the page header and slot directory.
• Failure Mode: Attempting to insert or update a row whose serialized length $>2048$ bytes immediately halts and throws RowSizeLimitExceededError. Overflow page chains are omitted in V1 to preserve zero-fragmentation slotted page math.

2.3 Maximum Database File Size (16 TB Physical / Browser Quota)

• Invariant: Page IDs are stored as 32-bit unsigned integers (uint32_t page_id).
• Physical Upper Bound: $2^{32}\times 4096\text{ bytes}=17,592,186,044,416\text{ bytes}=16\text{ TB}$.
• Browser Runtime Limit: In actual web browsers, storage is governed by the browser's storage manager quota (typically 60% of available disk space on desktop, or 2 GB–20 GB on mobile). When the device disk fills, the underlying IVfsAdapter write fails and yields DiskFullError.

2.4 Table Catalog Layout (128-Byte TableDescriptor & Master Page Layout)

• Invariant: Page 1 hosts the master table catalog with pre-allocated slots for up to 16 user tables in v1, with infinite forward-compatibility via chained catalog pages (next_catalog_page_id).
• Why the 128-Byte Fixed Descriptor Exists:
  • Page 1 is the Master Database Page.
  • Bytes 0..99 are the Database File Header.
  • Bytes 100..4095 (3,996 bytes) house the Master Table Catalog.
  • To prevent format-breaking migrations when column counts or identifier lengths grow, table descriptors are standardized to a fixed 128-byte power-of-two struct:

    typedef struct {
        uint16_t table_id;              // Numeric table ID (1..65535)                    (offset 0..1,   2B)
        uint16_t column_count;          // Number of columns defined (1..256)             (offset 2..3,   2B)
        uint32_t root_page_id;          // Table B+Tree root Page ID                      (offset 4..7,   4B)
        uint32_t col_catalog_page_id;   // First 4KB Catalog Page ID storing ColumnMeta   (offset 8..11,  4B)
        char     name[64];              // Table name (null-padded UTF-8, max 64 chars)   (offset 12..75, 64B)
        uint32_t flags;                 // Status flags (0x1=ACTIVE, 0x2=SYSTEM)          (offset 76..79, 4B)
        uint32_t row_count_estimate;    // Approximate row count for query optimizer      (offset 80..83, 4B)
        uint64_t auto_inc_next;         // Next AUTO_INC value (0 = no AUTO_INC column)   (offset 84..91, 8B)
                                        //  Read, assign to row, then increment on INSERT
        uint8_t  _reserved[36];         // Reserved padding for future table metadata     (offset 92..127,36B)
    } TableDescriptor;                  // Exact size: 128 bytes (128 % 8 = 0)

  • Page 1 Capacity:
    • Bytes 100..2147: 16 pre-allocated TableDescriptor slots ($16\times 128\text{ B}=2,048\text{ bytes}$).
    • Bytes 2148..3171: 8 pre-allocated IndexDescriptor slots ($8\times 128\text{ B}=1,024\text{ bytes}$) for composite indexes.
    • Bytes 3172..3175: next_descriptor_catalog_page_id (uint32_t, default 0), forward-compatible chained catalog page pointer for future descriptor categories (sequences, triggers, constraints).
    • Bytes 3176..4095: 920 bytes reserved space on Page 1 for future catalog descriptors.
  • $O(1)$ Schema Dereference: C/Wasm code locates table metadata via simple pointer arithmetic without parsing:

    const TableDescriptor *tbl = (const TableDescriptor*)(page1_ptr + 100 + (table_idx * 128));

  • Infinite Table Scalability (next_catalog_page_id): Page 1 header bytes 32..35 point to the next chained 4KB catalog page if $>16$ tables are created in future versions.

2.5 Maximum 256 Columns per Table & Dedicated Column Catalog Pages

• Invariant: Tables support between 1 and 256 columns (column_count <= 256).
• Dedicated Chained Column Catalog Architecture:
  • Because columns with 64-character names require $72\text{ bytes}$ each (ColumnMeta), column metadata is decoupled from Page 1 and stored in dedicated 4KB Column Catalog Pages (page_type = 0x0C):

    typedef struct {
        uint8_t  type;            // 1=INT32, 2=INT64, 3=FLOAT64, 4=TEXT, 5=BLOB, 6=UUID, 7=ULID (offset 0,   1B)
        uint8_t  flags;           // 0x1=PRIMARY KEY, 0x2=NOT NULL, 0x4=INDEXED, 0x8=AUTO_INC     (offset 1,   1B)
        uint16_t col_offset;      // Column offset inside fixed-width data slice                  (offset 2..3, 2B)
        char     name[64];        // Column name (null-padded UTF-8, max 64 chars)                (offset 4..67,64B)
        uint8_t  _reserved[4];    // Reserved (index roots are owned by IndexDescriptor.root_page_id (offset 68..71,4B)
                                  //  on Page 1; ColumnMeta never stores index root pages)
    } ColumnMeta;                 // Exact size: 72 bytes (72 % 8 = 0, 8-byte aligned)

  • Each 4KB Column Catalog Page has a 16-byte header (CatalogPageHeader), leaving $4,096-16=4,080$ bytes of payload.
  • Each page holds $\lfloor 4080/72\rfloor =56$ columns:
    • Tables with $1\dots 56$ columns occupy exactly 1 catalog page (next_col_catalog_page_id = 0).
    • Tables with $57\dots 256$ columns chain across up to 5 catalog pages via next_col_catalog_page_id ($\lceil 256/56\rceil =5$ pages).
• Dynamic Row Null-Bitmap Scaling:
  • The row's binary Null-Bitmap is sized dynamically as $\lceil \text{column\_count}/8\rceil$ bytes.
  • For a 256-column table, the Null-Bitmap is exactly 32 bytes ((256 + 7) >> 3 = 32).
• Interaction with Single Row Boundary (2,048 Bytes):
  • All serialized row records must satisfy the rigid physical constraint $\le 2048\text{ bytes}$ (RowSizeLimitExceededError).
  • For 256 columns of 4-byte integers (INT32), row size is $1+32+(256\times 4)=1,057\text{ bytes}\ll 2,048\text{ bytes}$.
  • Tables utilizing wide 8-byte columns (INT64, FLOAT64, UUID) or strings across all 256 columns must ensure non-null populated fields do not exceed 2,048 bytes per row.
• Failure Mode: Calling db.createTable() with $>256$ column definitions immediately throws TooManyColumnsError.

2.6 Maximum Identifier Length (64 Characters)

• Invariant: Table names and column names support up to 64 characters (stored as fixed 64-byte null-padded UTF-8 arrays char name[64]).
• Industry Standard Compatibility: Matches the enterprise SQL identifier limits of PostgreSQL (NAMEDATALEN = 64) and MySQL (64 chars), accommodating descriptive domain names (e.g. organization_billing_subscription_events).
• Failure Mode: Providing an identifier exceeding 64 characters immediately halts DDL compilation and throws IdentifierTooLongError.

---

3. Memory & Virtual Machine (VDBE) Execution Limits

3.1 64-Register File per Frame (r[0]..r[63])

• Invariant: Each VmFrame provides 64 evaluation registers per query nesting level.
• Memory Footprint: 64 tagged union structs $\times 16\text{ bytes}=1,024\text{ bytes}$, pre-allocated inline in each VmFrame at offsets 224..1247.
• Design Rationale: 64 registers comfortably covers the worst-case simultaneous register pressure of a maximally complex single query level — 16-table join predicates + 8 ORDER BY sort key extractions + 8 GROUP BY keys + 6 aggregate accumulators + UDF results + filter temporaries ≈ 46 registers peak. The 64-register ceiling provides a 38% safety margin.
• Validation: The bytecode compiler checks all register operands at compile time; any reference to register index $\ge 64$ halts compilation with TooManyRegistersError.

3.2 16 Active Cursor Slots per Frame (cursors[0]..cursors[15])

• Invariant: A single query level can open at most 16 concurrent cursors (table scans, index seeks, sorter iterators, arena cursors).
• Join Support: With 16 cursor slots, a single query level supports up to 16-table joins (one scan cursor per table, plus index seek cursors sharing the pool).
• Subquery Isolation: Each nesting frame has its own independent cursor set. A correlated subquery at depth=1 gets a fresh cursors[0..15] without disrupting the outer query's pinned cursors at depth=0.
• Buffer Headroom Guarantee: Across all 8 nesting frames (depth 0..7), the worst-case maximum number of simultaneously active cursors is $8\times 16=128$. In a standard 512-slot cache (2 MB), at least $512-128=384$ slots remain permanently unpinned for page eviction. In a 1,024-slot cache (4 MB), at least $1,024-128=896$ slots remain unpinned. Eviction deadlock is mathematically impossible.

3.3 8-Frame Nesting Stack (Correlated Subquery Depth ≤ 7)

• Invariant: VmContext maintains a fixed stack of 8 VmFrame structs (frames[0..7], depth field 0..7).
• What counts toward depth: Only simultaneously active correlated subqueries consume frame depth. Sequential scalar subqueries (multiple in a SELECT clause), FROM derived tables (materialized in arena), EXISTS/IN semi-joins (run to completion before outer continues), and JOINs (same frame, more cursors) do NOT consume frame stack depth.
• Supported depth: Depth 7 = one outer query + 7 levels of nested correlated subqueries. Depth 3–4 covers virtually all real-world SQL. Depth 7 is a future-proof ceiling that eliminates any practical chance of hitting the limit.
• Hard limit: Compiling a correlated subquery requiring depth > 7 throws SubqueryNestingTooDeepError at compile time.
• Memory Footprint & Future Expansion Budget: 8 frames $\times \mathrm{1,280}\text{ bytes}=\mathrm{10,240}\text{ bytes}$ ($+8\text{ B}$ header $=\mathrm{10,248}\text{ bytes}$). The VmContext memory window is rigidly pre-allocated at $\mathrm{12,288}\text{ bytes}$ (0x401080..0x40407F), providing 2,040 bytes of unallocated expansion cushion ($\mathrm{12,288}-\mathrm{10,248}=\mathrm{2,040}\text{ B}$). While each VmFrame includes 32 bytes of internal padding for word-alignment, this 2,040-byte unallocated cushion is the designated future expansion budget: if a future engine version requires wider frames (e.g. expanding from 16 to 32 cursors per frame adds $16\times 12\text{ B}=192\text{ bytes}$ per frame) or deeper frame stacks, the expansion is absorbed entirely within this cushion without moving the Output Result Buffer at 0x404080 or breaking memory offsets.

3.4 64 KB Fixed Result Buffer

• Invariant: Output rows are streamed into a pre-allocated 64 KB memory window.
• Streaming Protocol: When the 64 KB boundary is reached, the VM yields STATUS_BUFFER_FULL. JavaScript drains the rows, resets result_offset = 0, and calls vm_step() to continue. Memory overhead for query results is strictly bounded at 64 KB regardless of table size.

3.4 Transient Query Arena Ceiling (Default: 16 MB, Fully Configurable)

• Invariant: Dynamic aggregation hash tables (GROUP BY) and unindexed sorting buffers (ORDER BY) bump-allocate from the Transient Query Arena (0x420000..Ceiling).
• Why 16 MB is the Default:
  • Browser Tab Safety: Mobile browsers (iOS Safari Jetsam, Android Chrome) terminate web application tabs with zero warning when heap spikes exceed device thresholds. The 16 MB default prevents runaway analytical queries from crashing the user's browser tab.
  • Fail-Fast Predictability: Queries with unexpected Cartesian products or pathological cardinality fail fast rather than stalling the UI thread.
• Extending the Arena Beyond 16 MB:
  • Not a Physical Limit: Because the arena sits at the very tail of the memory map (0x420000 to end of memory) with no structures placed after it, it can expand dynamically via WebAssembly.Memory.grow() up to available host memory (up to 2 GB – 4 GB in Wasm32).
  • Configuration: Developers can configure a larger ceiling upon opening the database:

    const db = await WebDB.open({
      name: "analytics_db",
      maxQueryMemory: 64 * 1024 * 1024, // 64 MB (or 128 MB, 256 MB, etc.)
    });

• Fail-Fast Invariant: If aggregation or sorting exceeds the configured ceiling (max_query_memory), the VM halts immediately with STATUS_ERR_ARENA_EXHAUSTED and the Promise rejects with QueryArenaExhaustedError. Silent truncation, dropped rows, or partial sums are strictly prohibited.
• Instant Recovery: Resetting arena_offset = 0 reclaims 100% of transient memory in $O(1)$ time with zero fragmentation.

---

4. Query & SQL Feature Limits

4.1 Maximum 8 Sort Columns for ORDER BY

• Invariant: Multi-column sorting (orderBy([ { column, direction, nulls }, ... ])) accepts at most 8 sort columns.
• Rationale:
  • Stores the sorting descriptor in a compact 18-byte KeyInfo struct (num_keys: uint8, directions: uint8[8], null_orders: uint8[8]).
  • Allows the multi-key comparator loop in the in-place Introsort algorithm to remain unrolled and SIMD-friendly.
• Failure Mode: Emitting an ORDER BY with $>8$ columns throws TooManyOrderByColumnsError at compile time.

4.2 Maximum 8 Grouping Columns for GROUP BY

• Invariant: Grouping clauses (groupBy(['col1', 'col2', ...])) accept at most 8 grouping columns.
• Rationale:
  • Standardizes the FNV-1a composite hash computation and linear-probe equality checks in the Transient Query Arena.
  • Keeps the 40-byte AggBucket layout compact and deterministic.
• Execution Pathways:
  • Hash Aggregation (Unindexed): Accumulates groups in an open-addressing hash table starting at 1,024 buckets (40 KB) and doubling at 70% load factor up to the 16 MB arena ceiling (QueryArenaExhaustedError on overflow).
  • Stream Aggregation (Indexed/Sorted): Groups pre-ordered by an index or sorter stream through in $O(1)$ constant memory without allocating a hash table.
• Failure Mode: Querying with $>8$ grouping columns throws TooManyGroupByColumnsError at compile time.

4.3 Secondary & Composite Indexes (IndexDescriptor Architecture)

• Supported in V1: Both single-column (createIndex(tableName, 'col')) and composite multi-column (createIndex(tableName, ['col1', 'col2']), up to 8 columns per index) B+Tree indexes, with optional uniqueness enforcement ({ unique: true }).

• Page 1 Capacity: Page 1 pre-allocates 8 IndexDescriptor slots (128 bytes each at bytes 2148..3171), shared across automatic primary key indexes (pk_<table>) and user-defined secondary indexes (TooManyIndexesError if exceeded):

  typedef struct {
      uint16_t index_id;              // Numeric index ID (1..65535)                    (offset 0..1,   2B)
      uint16_t table_id;              // Owning table numeric identifier                (offset 2..3,   2B)
      uint32_t root_page_id;          // Index B+Tree root Page ID (0 if unallocated)   (offset 4..7,   4B)
      uint8_t  column_count;          // Number of indexed columns (1=single, 2..8=comp)(offset 8,     1B)
      uint8_t  flags;                 // 0x1=UNIQUE, 0x2=PRIMARY, 0x4=SPATIAL/VECTOR    (offset 9,     1B)
      uint16_t column_indices[8];     // 0-indexed column IDs participating in index   (offset 10..25,16B)
      uint8_t  col_directions[8];     // Sort order per column (0=ASC, 1=DESC)          (offset 26..33, 8B)
      char     name[64];              // Index identifier name (null-padded UTF-8)      (offset 34..97,64B)
      uint8_t  _reserved[30];         // Reserved for partial index filters / stats     (offset 98..127,30B)
  } IndexDescriptor;                  // Exact size: 128 bytes (128 % 8 = 0, power of 2)

  • Single-Column Execution: Populates column_count = 1 and column_indices[0] = col_idx, storing typed (indexed_value, rowid) entries.
  • Composite Index Execution: Populates column_count = 2..8 and column_indices[0..N-1], serializing composite tuples into order-preserving binary keys via serialize_composite_key.

4.4 Join Constraints & Cursor Slot Allocation (Query Builder vs. Engine Hard Limit)

A critical distinction exists between the TypeScript Query Builder layer and the Wasm Execution Engine layer:

1. TypeScript Query Builder Layer (No Arbitrary Syntactic Ceiling):
   • The fluent query builder API (db.from('...').join(...).join(...)...) has no arbitrary limit on the number of .join() calls chained in client code.
   • Developers can syntactically chain 20+ joins without TypeScript compiler errors.
2. Wasm Execution Engine Layer (16 Cursor Hard Limit per Frame):
   • The physical WebAssembly virtual machine allocates a fixed array of 16 cursor slots per VmFrame (Cursor cursors[16], byte offsets 32..223).
   • Every joined table in a query plan requires at least 1 cursor for table scanning or index lookup (plus any intermediate sorter or hash probe iterators within the frame).
   • Consequently, the VDBE runtime can execute queries joining up to 16 tables per query level.
3. Compiler Validation & Failure Mode (TooManyCursorsError):
   • When compiling a query AST into bytecode, the WebDB Query Compiler calculates the total number of cursor slots required by the execution plan.
   • If the query requires more than 16 cursors (for example, attempting to execute a 17+ table join, or a complex join graph that exhausts available cursor slots), compilation immediately halts and throws:

     TooManyCursorsError: Query requires 21 cursors, exceeding the engine limit of 16 cursor slots per frame.

4. Supported Join Semantics in V1:
   • Supported: INNER JOIN and LEFT OUTER JOIN.
   • Execution Strategy: Left-deep Nested Loop Join and Indexed Nested Loop Join (outer table scan + inner table B+Tree index seeks).
   • Deferred: RIGHT JOIN and FULL OUTER JOIN (deferred to V1.1+).
5. Cost Considerations (Absence of CBO in V1):
   • WebDB V1 executes joins in the exact order specified by the developer in the query builder (left-deep pipeline).
   • Indexed nested loops on indexed foreign keys run in $O(M\log N)$ time.
   • However, unindexed nested loops over multiple tables degrade with Cartesian multiplier complexity ($O(R_1\times R_2\times \dots )$). Creating secondary indexes on joined foreign keys is strongly recommended when joining 3+ tables.

4.5 Subqueries & 8-Frame Correlated Execution Stack

WebDB V1 supports both uncorrelated and correlated subqueries via its 8-frame nesting stack (VmContext.frames[8], depth 0..7):

1. Correlated Subqueries (WHERE col = (SELECT ... WHERE inner.x = outer.y)):
   • Supported Nesting Depth: Up to 7 levels of nested correlated subqueries (depth 0 = outer query, depth 1..7 = nested subqueries).
   • Frame Push & Pop: When entering a correlated subquery, the VM executes ctx->depth++, allocating a fresh VmFrame with an independent 64-register file and 16 cursor slots. On completion, the result register is passed to frames[ctx->depth - 1], ctx->depth-- is popped, and the parent resumes at its saved pc.
   • Failure Mode: If a correlated subquery hierarchy exceeds depth 7, the compiler rejects the query and throws SubqueryNestingTooDeepError.
2. Sequential Scalar Subqueries (SELECT (SELECT a FROM ...), (SELECT b FROM ...)):
   • Sequential subqueries execute to completion one after the other.
   • They reuse the same frame slot (depth = 1), resetting registers and cursors between runs.
   • Limit: Unlimited. Sequential subqueries do not consume stack depth.
3. Semi-Joins & Anti-Joins (EXISTS, NOT EXISTS, IN (subquery)):
   • EXISTS and NOT EXISTS run to first match and short-circuit.
   • IN (subquery) evaluates the inner set into an ephemeral hash set in the Transient Query Arena (0x420000) for $O(1)$ probing.
4. Derived Tables (FROM (SELECT ...)):
   • Derived tables are materialized into the Transient Query Arena (0x420000) and iterated using an arena cursor (flags = 0x4).
   • They do not consume subquery stack depth (depth = 0).

4.6 Join & Subquery Evolution Roadmap

Phase	Target Scope	Join Features	Subquery Features	Implementation Architecture
V1.0 (Current)	Core Engine	Up to 16 tables per frame (INNER & LEFT JOIN); query builder has no syntactic ceiling, compiler throws TooManyCursorsError if > 16 cursors required	Correlated subqueries up to depth 7 (SubqueryNestingTooDeepError); sequential subqueries unlimited; derived tables via arena cursors	8-frame nesting stack (VmContext), 64 registers + 16 cursors per frame; Left-Deep Nested Loop & Index Seek
V1.1	Expressive Queries	In-Arena Hash Joins; RIGHT JOIN support	Hash-accelerated IN (subquery) semi-joins in Transient Query Arena	In-Arena Hash Tables (0x420000), register hoisting, hash probe iterators
V1.2	Analytical Queries	Arbitrary join trees with manual hints	Subquery decorrelation heuristics	Adaptive join selection (hash vs nested loop); cross-frame cursor caching
V2.0	Enterprise SQL	Cost-Based Optimizer (CBO), FULL OUTER JOIN	Advanced subquery unnesting, Cost-based decorrelation	Catalog statistics & histograms, dynamic programming join ordering ($O(3^N)$)

4.7 Aggregations & Analytical Queries

• Supported Aggregate Functions: COUNT(*), COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col).
• Post-Aggregation Filtering: having(...) clauses evaluated after bucket aggregation.
• Deferred: Window functions (OVER (PARTITION BY ...)), ROLLUP, CUBE, and common table expressions (WITH ...).

4.8 Schema Mutations (ALTER TABLE)

WebDB supports dynamic schema mutations via db.alterTable() and dedicated DDL methods (renameTable, renameColumn, addColumn, dropColumn), split into two architectural pathways:

1. $O(1)$ Metadata-Only Mutations (renameTable, renameColumn):
   • Mechanism: Directly updates the fixed 64-byte UTF-8 name field inside TableDescriptor (on Page 1) or ColumnMeta (on the table's dedicated Column Catalog Page 0x0C), increments schema_version, and recomputes page CRC32 checksums in $O(1)$ time with zero data or index page rewrites.
   • Index Continuity: Because IndexDescriptor.columnIndices references columns by numeric index (0..255), renaming a column preserves all single-column and composite B+Tree indexes in place without rebuilding index trees. Renaming a table also updates auto-named index descriptors (pk_<old> $\to$ pk_<new>, idx_<old>_* $\to$ idx_<new>_*).
2. Transactional Table-Rebuild Mutations (addColumn, dropColumn):
   • Why Table Rebuild is Required: Row records use a compact binary layout with a column-count-dependent Null-Bitmap ($\lceil \text{column\_count}/8\rceil$ bytes) and precomputed fixed-slice col_offset pointers. Changing column_count alters the binary layout of every row.
   • Atomic Execution: addColumn and dropColumn execute atomically inside a WAL transaction (or a nested savepoint when invoked inside an active db.transaction() block). All existing rows, autoIncNext sequences, primary key indexes, and surviving secondary indexes are migrated and rebuilt; any constraint failure (NotNullConstraintError, RowSizeLimitExceededError, UniqueConstraintViolationError) rolls back the entire operation cleanly.
   • Guardrails & Constraints:
     • Dropping a PRIMARY KEY column or the last remaining column on a table is rejected with CannotDropColumnError.
     • Adding a PRIMARY KEY column to a table that already defines a primary key is rejected.
     • Dropping a column automatically cascade-drops any secondary index referencing that column while preserving all other indexes.

4.9 Client-Side Projection (projectRow) & Subquery Boundary Limitation

• Current V1 Architecture: Terminal queries executed through the Query Builder (via .toArray() or .first()) run an in-memory post-processor projectRow. This handles JavaScript-side scalar expression evaluation, client-side UDF execution, column aliasing, and fallback formatting on the raw result rows returned by the VDBE VM.
• Subquery Invariant / Limitation: Subqueries (such as derived tables in FROM (subquery), scalar subqueries, or IN (subquery) filters) compile and execute strictly within the core VDBE virtual machine and bytecode frames. Consequently, any projection logic, expression transforms, or custom aliases handled exclusively in JavaScript projectRow are not evaluated or accessible within subquery contexts. Subqueries only have access to raw columns and native VM registers emitted by bytecode.
• Resolution Roadmap: Full composability will be achieved by lowering all scalar functions and expressions directly into VDBE bytecode opcodes (OP_STR_*, OP_MATH_*, OP_ADD, OP_CALL_UDF, etc.) during compilation, eliminating the need for client-side JavaScript row interception.

---

5. Concurrency & Transaction Limits

5.1 Single Writer Lock (Multi-Tab Coordination)

• Invariant: Only one browser tab or Web Worker may hold an active write transaction or execute a database operation at any instant.
• Mechanism: Coordination is enforced via the browser's native Web Locks API (navigator.locks.request('webdb_lock_...')) combined with Page 1 changeCounter / schemaVersion cache coherency checks across tabs.
• Behavior: Concurrent tabs attempting transactions wait asynchronously in FIFO order, automatically replaying any uncheckpointed WAL frames and invalidating stale unpinned buffer pool pages when acquiring the cross-tab lock. If a holding tab crashes or closes, the browser automatically releases the lock without file corruption.

5.2 Serialized Top-Level Transactions & Nested Savepoints

• Invariant: A single WebDB instance serializes concurrent top-level db.transaction() calls in FIFO order via ExclusiveLeaseQueue.
• Nested Savepoints: Calling tx.transaction(async (nestedTx) => ...) inside an active transaction captures an in-memory and WAL savepoint (captureSavepoint / restoreSavepoint), allowing inner blocks to roll back independently without aborting the outer transaction.

5.3 WAL Auto-Checkpoint Threshold (256 Frames / 100 Committed Transactions)

• Invariant: The Write-Ahead Log (.wal file) automatically triggers a passive checkpoint and truncation when it accumulates $\ge 256$ frames (~1 MB) or $\ge 100$ committed transactions.
• Rationale: Keeps crash recovery scans fast (<10 ms on database open) and bounds storage expansion in quota-constrained environments.

5.4 Abandoned Transaction Timeout (30 Seconds)

• Invariant: A transaction holds an exclusive lease on the single active VmContext execution slot. If an open transaction remains idle without executing a query, commit, or rollback for 30 seconds, the Host Transaction Coordinator automatically aborts the transaction, issues an internal ROLLBACK, releases the lease, and rejects subsequent operations in that block with TransactionTimeoutError.
• Rationale: Prevents dangling transactions (e.g. unhandled Promise rejections, crashed client callbacks, or stalled network requests within transaction blocks) from permanently locking the single-writer FIFO queue and starving other operations.

5.5 Low-Level Sync Primitives, High-Level DML & Cross-Tab Reactivity

WebDB provides low-level primitives for building offline-first synchronization engines without baking opinionated network protocols into the core engine:

1. High-Level DML & Conditional Upserts (update, delete, upsert, upsertMany):
   • Supports direct db.update() / db.delete(), fluent QueryBuilder.update() / QueryBuilder.delete(), and idempotent db.upsert() / db.upsertMany().
   • Index-Consistent Row Updates & Deletes: In-place updates preserve physical slot rowids ((pageId << 16) | cellIdx) and update only modified secondary index keys. Row deletions and page-overflow updates automatically maintain secondary index slot pointers across the modified 4 KB page.
   • Last-Write-Wins (LWW) Conflict Guard: UpsertOptions supports conflictTarget (primary key or unique column(s)), mode ('merge' | 'replace' | 'ignore'), and a conditional where: (existingRow, incomingRow) => boolean predicate so stale remote payloads are ignored in a single atomic pass.
2. Transactional Change Capture (onBeforeCommit, onCommit, Changeset.coalesced()):
   • Every transaction and standalone DML operation accepts TransactionOptions ({ origin?: 'local' | 'remote' | string, meta?: Record<string, any> }) and records row-level MutationRecords (insert, update, delete with pk, oldRow, newRow, and changedColumns).
   • changeset.coalesced() squashes multiple mutations to the same (table, pk) within a transaction (insert + update -> insert, update + update -> merged update, insert + delete -> omitted, update + delete -> delete) and respects nested savepoint rollbacks.
   • db.onBeforeCommit() runs inside the active transaction lease before WAL commit, allowing outbox table writes to commit or roll back atomically alongside application data. db.onCommit() fires after WAL durability to wake up push workers.
3. Real-Time Multi-Tab / Worker Notifications (db.subscribe, BroadcastChannel):
   • db.subscribe(tables, callback) notifies listeners on local commits (crossTab: false) and broadcasts table invalidation events over BroadcastChannel (webdb_sync_<vfs>_<name>) so other open tabs/workers immediately sync their buffer pool state and notify UI subscribers (crossTab: true).

---

6. Supported Data Types & Conversions

Type	Binary Storage	Width	Notes & Emulation
INT32	32-bit signed integer	4 bytes	Little-endian. Clamped range: $-2^{31}\dots 2^{31}-1$.
INT64	64-bit signed integer	8 bytes	Represented in JS as bigint. Used for row IDs and millisecond timestamps.
FLOAT64	64-bit IEEE 754 float	8 bytes	Standard JavaScript number representation.
TEXT	UTF-8 byte stream	Var (2B len + data)	Max length bounded by 2048-byte row ceiling.
BLOB	Raw binary bytes	Var (2B len + data)	Max length bounded by 2048-byte row ceiling.

Unsupported Types & Recommended Emulations

• DECIMAL / NUMERIC: Store as integer cents/micros (INT64) or formatted strings (TEXT).
• DATE / DATETIME: Store as UNIX epoch milliseconds (INT64) or ISO-8601 strings (TEXT).
• BOOLEAN: Store as INT32 (0 for false, 1 for true).
• JSON: Store as stringified UTF-8 text (TEXT).

---

7. Comparison Matrix: WebDB V1 vs. SQLite Defaults

Specification Metric	WebDB V1	SQLite Default	Architectural Trade-Off in WebDB
Engine Footprint	< 100 KB	~800 KB - 1.5 MB	Zero SQL parser in engine, ultra-lean VDBE.
Page Size	Rigid 4 KB	Configurable (512B - 64KB)	Deterministic memory layout for browser cache.
Max Row Size	2,048 bytes	1,000,000,000 bytes	Eliminates overflow page complexity in V1.
Tables per Database	16 tables (Page 1) / $\mathrm{\infty }$	Unlimited ($2^{31}-1$)	$O(1)$ 128B descriptors; forward-compatible link.
Columns per Table	256 columns	2,000 columns	Dedicated catalog pages; dynamic 32-byte Null-Bitmap.
Max Identifier Length	64 characters	Unlimited (no fixed limit)	Standard SQL length (char name[64]); zero string parsing.
Sort Columns (ORDER BY)	8 columns	Unlimited	Compact 18B key descriptor; unrolled comparisons.
Grouping Columns (GROUP BY)	8 columns	Unlimited	Compact 40B AggBucket; unrolled key comparisons.
Secondary Indexes	Up to 8 indexes (1..8 cols each)	Multi-column composite	Fixed 128B IndexDescriptor slots on Page 1.
Schema Mutations (ALTER)	$O(1)$ rename; atomic rebuild ADD/DROP	$O(1)$ ADD; rebuild DROP	Compact Null-Bitmap + fixed-slice offsets rebuilt in WAL tx.
Virtual Registers	64 per frame (8-frame stack)	Unlimited (dynamic)	Static 1,024B inline per frame; per-level isolation.
Subquery Nesting	Depth ≤ 7 (correlated)	Unlimited	8-frame fixed stack; sequential subqueries unlimited.
Table Joins	Up to 16 tables (16 cursors/frame)	Unlimited	One cursor per table; 16-cursor frame ceiling.
Query Arena Ceiling	16 MB (default, configurable)	Bound by host memory	Safe default for mobile tabs; configurable up to 2 GB.
Concurrency Model	Web Locks (Single Writer)	POSIX / Win32 file locks	Native browser multi-tab serialization.