Issue Overview: Managing Fact Assertions/Retractions with Transaction-Scoped Identifiers

The core challenge involves designing a SQLite schema to manage temporal fact assertions and retractions using transaction-scoped identifiers while ensuring atomicity. The system requires all fact modifications (assertions via INSERTs and retractions via UPDATEs/DELETEs) within a transaction to reference a shared transaction identifier (tx_number). This identifier must be generated within the transaction to guarantee that rollbacks eliminate both fact modifications and their associated transaction record.

The schema uses two critical tables:

1. Transact: Stores transaction metadata with auto-incrementing tx_number and timestamps.
2. Fact_p: Stores facts with foreign keys (asserted, retracted) referencing Transact.tx_number.

A view (Fact) with INSTEAD OF triggers abstracts fact operations:

• INSERT into Fact sets asserted to the current transaction’s tx_number.
• DELETE from Fact sets retracted to the current transaction’s tx_number (soft delete).
• UPDATE is implemented as DELETE + INSERT to preserve history.

The critical limitation is SQLite’s lack of transaction-level triggers or statement-level triggers, which would allow automatic generation of a Transact record at transaction start. Without this, developers cannot guarantee that all fact operations in a transaction reference the same tx_number and that the Transact row is created atomically with fact modifications.

Possible Causes: Trigger Granularity and Transaction Metadata Isolation

Three architectural constraints create this problem:

1. Absence of Transaction-Bound Triggers
SQLite triggers are tied to table events (INSERT/UPDATE/DELETE), not transactional boundaries (BEGIN/COMMIT). A trigger cannot execute when a transaction starts, making it impossible to auto-generate a Transact row at BEGIN.

2. Row-Level Trigger Execution
SQLite supports only row-level triggers (FOR EACH ROW), which fire once per modified row. If a trigger on Fact_p attempted to create a Transact row, it would execute redundantly for each fact modification in the transaction, risking duplicate tx_number entries or requiring complex existence checks.

3. Transaction Metadata Isolation
The Transact table must be modified within the transaction to ensure rollback consistency. However, without a pre-existing tx_number, the first fact modification in a transaction cannot reference a Transact row that doesn’t yet exist. This creates a circular dependency: the Transact row must be created before fact modifications, but there’s no mechanism to enforce this order automatically.

Troubleshooting Steps, Solutions & Fixes: Schema Refinements and Pragma-Driven Workarounds

Solution 1: Pre-Insert Transact Rows with Application-Level Enforcement

Step 1: Explicit Transact Row Creation
Require applications to explicitly insert into Transact at the start of a transaction:

BEGIN;  
INSERT INTO Transact (user) VALUES ('app_user');  
-- Subsequent fact operations use last_insert_rowid() for tx_number  

Step 2: Leverage last_insert_rowid()
In INSTEAD OF triggers on the Fact view, reference last_insert_rowid() to set asserted/retracted:

CREATE TRIGGER Fact_Insert INSTEAD OF INSERT ON Fact  
BEGIN  
  INSERT INTO Fact_p (uuid, asserted, ...)  
  VALUES (NEW.uuid, last_insert_rowid(), ...);  
END;  

Drawbacks:

• Relies on application discipline to always insert into Transact first.
• Vulnerable to incorrect tx_number references if other INSERTs occur mid-transaction.

Solution 2: Singleton Transact Row with Transaction-Held Lock

Step 1: Use BEGIN EXCLUSIVE Transactions
Open transactions in EXCLUSIVE mode to lock the database, ensuring no concurrent writes:

BEGIN EXCLUSIVE;  
INSERT INTO Transact (user) VALUES ('app_user');  
-- Proceed with fact modifications  

Step 2: Retrieve tx_number via MAX()
Modify INSTEAD OF triggers to reference the latest tx_number:

CREATE TRIGGER Fact_Insert INSTEAD OF INSERT ON Fact  
BEGIN  
  INSERT INTO Fact_p (uuid, asserted, ...)  
  VALUES (NEW.uuid, (SELECT MAX(tx_number) FROM Transact), ...);  
END;  

Drawbacks:

• EXCLUSIVE locks degrade concurrency.
• SELECT MAX() introduces overhead in large Transact tables.

Solution 3: Temp Table for Transaction Context

Step 1: Create Transaction-Scoped Temp Table
At transaction start, create a temporary table to hold the tx_number:

BEGIN;  
CREATE TEMP TABLE CurrentTx (tx_number INTEGER);  
INSERT INTO Transact (user) VALUES ('app_user');  
INSERT INTO CurrentTx VALUES (last_insert_rowid());  

Step 2: Reference Temp Table in Triggers
Modify triggers to read tx_number from CurrentTx:

CREATE TRIGGER Fact_Insert INSTEAD OF INSERT ON Fact  
BEGIN  
  INSERT INTO Fact_p (uuid, asserted, ...)  
  VALUES (NEW.uuid, (SELECT tx_number FROM CurrentTx), ...);  
END;  

Drawbacks:

• Requires modifying triggers to depend on temp tables.
• Temp tables are session-specific, complicating connection pooling.

Solution 4: Pragma-Controlled Transaction Hooks

Step 1: Use sqlite3_update_hook()
For applications with native code access, use the SQLite C API’s sqlite3_update_hook() to detect the first write operation in a transaction and inject a Transact INSERT:

void on_update(void *arg, int op, char const *db, char const *tbl, sqlite3_int64 rowid) {  
  if (!transaction_started) {  
    exec_sql("INSERT INTO Transact (user) VALUES ('app_user')");  
    transaction_started = 1;  
  }  
}  
sqlite3_update_hook(db, on_update, NULL);  

Step 2: Map tx_number via last_insert_rowid()
As in Solution 1, triggers reference last_insert_rowid().
Drawbacks:

• Application code must manage hooks, conflicting with ORM layers.
• C API-centric, unusable from pure SQL or scripting languages.

Solution 5: Shadow Table with Delayed Trigger

Step 1: Buffer Fact Modifications in a Shadow Table
Create a shadow table to queue fact operations, then process them with a trigger on Transact:

CREATE TEMP TABLE FactBuffer (  
  operation TEXT,  -- 'INSERT'/'DELETE'  
  uuid INTEGER,  
  ...  
);  

CREATE TRIGGER Transact_AfterInsert AFTER INSERT ON Transact  
BEGIN  
  -- Process buffered operations after Transact INSERT  
  DELETE FROM FactBuffer  
  RETURNING 
    CASE operation  
      WHEN 'INSERT' THEN  
        INSERT INTO Fact_p (uuid, asserted, ...)  
        VALUES (uuid, NEW.tx_number, ...)  
      WHEN 'DELETE' THEN  
        UPDATE Fact_p SET retracted = NEW.tx_number  
        WHERE uuid = uuid AND retracted IS NULL  
    END;  
END;  

Step 2: Route Fact Operations Through the Buffer
Modify the Fact view’s triggers to write to FactBuffer instead of Fact_p:

CREATE TRIGGER Fact_Insert INSTEAD OF INSERT ON Fact  
BEGIN  
  INSERT INTO FactBuffer (operation, uuid, ...)  
  VALUES ('INSERT', NEW.uuid, ...);  
END;  

Drawbacks:

• Increases complexity with temporary tables and secondary triggers.
• Risk of unprocessed buffers if Transact INSERT is omitted.

Final Recommendation: Hybrid Application-Database Approach

Combine Solution 1 (explicit Transact INSERTs) with Solution 3 (temp table context) for robustness:

1. Applications begin transactions and explicitly create a Transact row.
2. Triggers reference last_insert_rowid() or a temp table for tx_number.
3. Use PRAGMA foreign_keys = ON to enforce referential integrity.

This balances explicitness with trigger automation, minimizing reliance on SQLite’s trigger granularity limitations.