SQLite 3.50+ Binary JSONB, STRICT Table & Generated Indexes
-- SQLite 3.45 - 3.50+ High-Speed Binary JSONB + STRICT Table + Generated Index Pattern
-- Binary JSONB avoids repeated text parsing and enables localized in-place mutations in 3.50+
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
-- 1. STRICT Table Storing Binary JSONB Payload + Zero-Disk VIRTUAL Generated Columns
CREATE TABLE IF NOT EXISTS user_sessions (
session_id TEXT PRIMARY KEY,
user_id INTEGER NOT NULL,
-- Store binary JSONB BLOB; flag '4' in json_valid enforces RFC-8259 OR SQLite JSONB format
metadata BLOB NOT NULL DEFAULT (jsonb('{}')) CHECK (json_valid(metadata, 4)),
-- Virtual generated columns compute on read & power standard B-tree indexes with 0 byte storage overhead
plan_tier TEXT GENERATED ALWAYS AS (metadata ->> '$.plan') VIRTUAL,
region_code TEXT GENERATED ALWAYS AS (metadata ->> '$.geo.region') VIRTUAL,
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;
-- 2. Partial & Composite B-Tree Indexes Over Virtual JSONB Fields
CREATE INDEX IF NOT EXISTS idx_sessions_plan_user
ON user_sessions (plan_tier, user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_eu_region
ON user_sessions (region_code, updated_at DESC)
WHERE region_code IS NOT NULL;
-- 3. Insert Converting Incoming Text JSON Into Compact Binary JSONB Once on Write
INSERT INTO user_sessions (session_id, user_id, metadata)
VALUES (
'sess_0195f410a9c2',
1042,
jsonb('{"plan":"pro","mfa":true,"geo":{"region":"eu-central","asn":24940},"quotas":{"runs":12}}')
);
-- 4. Localized In-Place Mutation (SQLite 3.50+ optimizes jsonb_replace / jsonb_set internal byte shifts)
UPDATE user_sessions
SET metadata = jsonb_set(metadata, '$.quotas.runs', (metadata ->> '$.quotas.runs') + 1),
updated_at = unixepoch()
WHERE session_id = 'sess_0195f410a9c2';
-- 5. Read Back Clean Text JSON When Returning to HTTP/API Layer (Uses idx_sessions_plan_user!)
EXPLAIN QUERY PLAN
SELECT session_id, user_id, json(metadata) AS metadata_json
FROM user_sessions
WHERE plan_tier = 'pro';