Cross-Schema Temporary Table Visibility in Trigger Execution Contexts

Issue Overview: Trigger-Based UUID Mapping Blocked by Temporary Table Schema Restrictions

The core challenge involves implementing a surrogate key management system where externally provided application identifiers (id) must be mapped to internally generated UUIDs stored in a temporary table (k_map) scoped to each database connection. The goal is to enforce UUID consistency across distributed systems while insulating applications from internal metadata management.

The proposed architecture uses INSTEAD OF INSERT triggers on views to intercept application-provided data. These triggers attempt to reference a connection-specific temporary table (temp.k_map) to resolve UUID mappings. However, SQLite enforces strict schema visibility rules: triggers defined in the main schema cannot reference objects in the temp schema unless the trigger itself resides in temp. Attempts to access temp.k_map from a main-schema trigger result in errors like no such table: temp.k_map, even if the temporary table exists in the connection.

This limitation disrupts the intended design, as temporary tables are ideal for connection-specific mappings. Without them, alternative strategies must either expose internal UUIDs to applications or rely on persistent tables, risking data collisions across connections.

Possible Causes: Schema Context Isolation and Missing Connection Lifecycle Hooks

1. Schema-Specific Trigger Execution Contexts

SQLite partitions database objects into schemas: main (default), temp (connection-specific), and attached databases. Triggers execute within the schema where they are defined. A trigger in the main schema can only reference tables in main unless explicitly qualified (e.g., temp.k_map). However, SQLite intentionally blocks cross-schema references from triggers to prevent accidental data leaks and ensure trigger portability.

For example:

-- Trigger in 'main' schema cannot access 'temp' tables
CREATE TRIGGER main.i_Fact INSTEAD OF INSERT ON main.Fact  
BEGIN  
  INSERT INTO temp.k_map (id) VALUES (NEW.uuid); -- Fails: temp.k_map not visible  
END;  

2. Absence of Connection/Transaction Event Handlers

SQLite lacks built-in mechanisms to execute code automatically when a connection opens or a transaction starts/commits. This forces applications to manually initialize temporary objects (k_map) upon each connection. While CREATE TEMPORARY TABLE IF NOT EXISTS could theoretically initialize k_map on first use, it cannot be embedded within triggers due to parse-time schema validation.

3. Security and Stability Design Choices

SQLite restricts cross-schema trigger references to avoid unintended side effects. If a main-schema trigger referenced a temp table, the trigger would behave differently across connections depending on temporary table state, compromising reproducibility. Temporary objects are excluded from the database file, making them unsuitable for triggers that must persist across sessions.

Troubleshooting Steps: Schema Reconfiguration and Connection Lifecycle Management

1. Define Triggers and Views in the Temporary Schema

Create views and their associated triggers in the temp schema, granting them access to both temp and main objects:

-- Create temporary view and trigger  
CREATE TEMP VIEW Fact AS SELECT * FROM main.Fact_t;  

CREATE TEMP TRIGGER i_Fact INSTEAD OF INSERT ON Fact  
BEGIN  
  INSERT INTO temp.k_map (id) VALUES (NEW.uuid);  
  INSERT INTO main.Fact_t (uuid)  
  VALUES ( (SELECT uuid FROM temp.k_map WHERE id = NEW.uuid) );  
END;  

Key Considerations:

• Temporary objects are connection-specific, so they must be recreated on each new connection.
• Applications querying the view must reference temp.Fact instead of main.Fact.

2. Automated Schema Initialization via SQLite Auto-Extension

While SQLite lacks ON CONNECT triggers, the C API’s sqlite3_auto_extension function allows registering initialization routines that execute on every new connection. This requires embedding custom C code:

// Custom extension to create temporary objects  
void init_temp_schema(sqlite3 *db) {  
  sqlite3_exec(db, "CREATE TEMP TABLE k_map (id TEXT, uuid TEXT);", 0, 0, 0);  
  sqlite3_exec(db, "CREATE TEMP VIEW Fact AS SELECT * FROM main.Fact_t;", 0, 0, 0);  
}  

// Register auto-extension  
sqlite3_auto_extension((void (*)(void))init_temp_schema);  

Limitations:

• Requires application modification and C/C++ integration.
• Not feasible for pure SQL solutions or languages without direct C API access.

3. Application-Managed Connection Initialization

Mandate that applications execute setup SQL upon connecting:

CREATE TEMP TABLE k_map (id TEXT UNIQUE NOT NULL, uuid TEXT DEFAULT(hex(randomblob(16))));  
CREATE TEMP VIEW Fact AS SELECT * FROM main.Fact_t;  
CREATE TEMP TRIGGER i_Fact ... ;  

Implementation Notes:

• Applications must ensure setup SQL runs before any data operations.
• Use IF NOT EXISTS clauses to avoid errors on reinitialization.

4. Persistent UUID Mapping with Connection Identifier Hybrids

Abandon temporary tables and instead use persistent tables augmented with a connection_id column. Generate a unique connection identifier (e.g., via randomblob(16)) at application startup:

CREATE TABLE main.k_map (  
  connection_id BLOB NOT NULL,  
  id TEXT NOT NULL,  
  uuid TEXT DEFAULT(hex(randomblob(16))),  
  PRIMARY KEY (connection_id, id)  
);  

Modify triggers to include the connection identifier:

CREATE TRIGGER main.i_Fact INSTEAD OF INSERT ON main.Fact  
BEGIN  
  INSERT INTO main.k_map (connection_id, id) VALUES (x'1234...', NEW.uuid);  
  -- Use connection_id in subsequent operations  
END;  

Trade-offs:

• Requires connection ID management but eliminates temp table dependencies.
• Increases table size, necessitating periodic cleanup of stale entries.

5. Leveraging In-Memory Databases for Ephemeral Data

Attach an in-memory database as a separate schema, functioning as a temporary store:

ATTACH ':memory:' AS mem;  
CREATE TABLE mem.k_map (id TEXT UNIQUE NOT NULL, uuid TEXT);  

CREATE TRIGGER main.i_Fact INSTEAD OF INSERT ON main.Fact  
BEGIN  
  INSERT INTO mem.k_map (id) VALUES (NEW.uuid);  
  INSERT INTO main.Fact_t (uuid) SELECT uuid FROM mem.k_map WHERE id = NEW.uuid;  
END;  

Caveats:

• In-memory data persists only within the connection, similar to temp.
• Triggers in main can access mem schema tables without restrictions.

6. Replication-Safe UUID Generation Strategies

If the primary concern is collision-resistant UUIDs during merges, consider using application-generated UUIDs (version 4 or 7) instead of SQLite’s randomblob(16). This shifts UUID management to the application layer, bypassing the need for mapping tables:

CREATE TABLE main.Fact_t (  
  uuid TEXT PRIMARY KEY NOT NULL,  -- App provides UUID  
  id TEXT UNIQUE NOT NULL  
);  

CREATE VIEW main.Fact AS SELECT id FROM main.Fact_t;  

CREATE TRIGGER main.i_Fact INSTEAD OF INSERT ON main.Fact  
BEGIN  
  INSERT INTO main.Fact_t (uuid, id) VALUES (NEW.uuid, NEW.id);  
END;  

Advantages:

• Eliminates mapping tables and cross-schema complexities.
• Applications control UUIDs, ensuring global uniqueness.

Final Recommendations

1. Adopt Temporary Schema Triggers: Redefine triggers and views in the temp schema to enable cross-database access. This aligns with SQLite’s visibility rules while isolating connection-specific state.
2. Automate Initialization via SQLite C API: For controlled environments, use sqlite3_auto_extension to enforce temporary object creation on connection startup.
3. Hybrid Persistent Mapping: If temporary tables prove untenable, use persistent tables with connection identifiers to simulate per-connection isolation.

By rearchitecting schema dependencies and leveraging SQLite’s extensibility, the UUID mapping system can operate reliably without requiring application-level UUID management.