PostgreSQL JSON vs JSONB: Deep Dive into Operators, Indexing & Performance

-- Create a table to compare JSON and JSONB storage behaviour side by side
CREATE TABLE io.thecodeforge.storage.comparison (
    id          SERIAL PRIMARY KEY,
    raw_json    JSON,    -- stored verbatim, re-parsed on every access
    binary_json JSONB    -- parsed once on insert, stored as binary tree
);

-- Insert the same payload into both columns.
-- Note the intentional duplicate key 'status' and extra whitespace.
INSERT INTO io.thecodeforge.storage.comparison (raw_json, binary_json)
VALUES (
    '{  "user_id": 42,  "status": "pending",  "status": "active"  }',
    '{  "user_id": 42,  "status": "pending",  "status": "active"  }'
);

-- Retrieve both columns and observe the differences
SELECT
    raw_json,     -- JSON: whitespace preserved, BOTH 'status' keys kept
    binary_json   -- JSONB: whitespace stripped, duplicate key deduplicated (last wins)
FROM io.thecodeforge.storage.comparison
WHERE id = 1;

-- Check the actual storage size on disk
SELECT
    pg_column_size(raw_json)    AS json_bytes,
    pg_column_size(binary_json) AS jsonb_bytes
FROM io.thecodeforge.storage.comparison
WHERE id = 1;

-- Real-world benchmark: measure parse overhead at scale
-- Run this on a table with 1M rows to see the JSON vs JSONB difference
CREATE TABLE io.thecodeforge.benchmark.json_parse_test AS
SELECT
    generate_series(1, 1000000) AS id,
    ('{"name":"user_' || generate_series(1, 1000000)
     || '","email":"user' || generate_series(1, 1000000)
     || '@example.com","age":' || (random() * 80 + 18)::INT
     || ',"city":"City_' || (random() * 100)::INT || '"}')::JSON AS data_json,
    ('{"name":"user_' || generate_series(1, 1000000)
     || '","email":"user' || generate_series(1, 1000000)
     || '@example.com","age":' || (random() * 80 + 18)::INT
     || ',"city":"City_' || (random() * 100)::INT || '"}')::JSONB AS data_jsonb;

-- Benchmark: extract 'age' field from JSON (re-parses every row)
\timing on
SELECT COUNT(*) FROM io.thecodeforge.benchmark.json_parse_test
WHERE (data_json ->> 'age')::INT > 65;
-- Typical result: ~8,200ms on 1M rows

-- Benchmark: extract 'age' field from JSONB (pre-parsed, no re-parse)
SELECT COUNT(*) FROM io.thecodeforge.benchmark.json_parse_test
WHERE (data_jsonb ->> 'age')::INT > 65;
-- Typical result: ~2,100ms on 1M rows — roughly 4x faster

-- Clean up
DROP TABLE io.thecodeforge.benchmark.json_parse_test;

-- Set up a realistic product catalogue table
CREATE TABLE io.thecodeforge.catalogue.products (
    product_id   SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    attributes   JSONB NOT NULL  -- flexible attributes vary by category
);

INSERT INTO io.thecodeforge.catalogue.products (product_name, attributes) VALUES
    ('Wireless Headphones', '{
        "brand": "SoundPeak",
        "specs": {"battery_hours": 30, "driver_mm": 40},
        "tags": ["audio", "wireless", "noise-cancelling"],
        "in_stock": true,
        "price_usd": 149.99
    }'),
    ('Mechanical Keyboard', '{
        "brand": "KeyForge",
        "specs": {"switch_type": "Cherry MX Blue", "layout": "TKL"},
        "tags": ["peripherals", "mechanical"],
        "in_stock": false,
        "price_usd": 89.00
    }'),
    ('USB-C Hub', '{
        "brand": "ConnectPro",
        "specs": {"ports": 7, "max_wattage": 100},
        "tags": ["accessories", "usb-c"],
        "in_stock": true,
        "price_usd": 39.99
    }');

-- (1) -> returns a JSON node (preserves type)
--     ->> returns TEXT (use for comparisons and display)
SELECT
    product_name,
    attributes -> 'brand'       AS brand_as_json,   -- returns: "SoundPeak" (JSON string)
    attributes ->> 'brand'      AS brand_as_text,   -- returns: SoundPeak (plain text)
    attributes -> 'price_usd'   AS price_as_json    -- returns: 149.99 (JSON number)
FROM io.thecodeforge.catalogue.products;

-- (2) Nested access with chained -> and path operator #>>
SELECT
    product_name,
    attributes -> 'specs' -> 'battery_hours'  AS battery_json,  -- NULL for non-headphones
    attributes #>> '{specs, battery_hours}'    AS battery_text   -- same result, cleaner syntax
FROM io.thecodeforge.catalogue.products;

-- (3) @> containment: find all in-stock products with 'wireless' tag
-- This is the operator GIN indexes are built for — use it!
SELECT product_name, attributes ->> 'price_usd' AS price
FROM io.thecodeforge.catalogue.products
WHERE attributes @> '{"in_stock": true}'          -- containment check
  AND attributes @> '{"tags": ["wireless"]}';      -- array containment works too

-- (4) ? existence: find products that HAVE an 'in_stock' key
-- Note: ? only checks TOP-LEVEL keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ? 'in_stock';  -- all rows have this key, returns all 3

-- (5) ?| any-existence: find products with ANY of these keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ?| array['warranty', 'return_policy', 'in_stock'];
-- Returns all 3 because all have 'in_stock'

-- (6) ?& all-existence: find products that have ALL of these keys
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE attributes ?& array['brand', 'price_usd', 'in_stock'];
-- Returns all 3

-- (7) Casting ->> result for numeric comparison (common pattern)
SELECT product_name
FROM io.thecodeforge.catalogue.products
WHERE (attributes ->> 'price_usd')::NUMERIC > 100.00;  -- cast TEXT to NUMERIC

-- (8) jsonb_typeof: inspect the type of a JSONB value
-- Useful for debugging — 'string', 'number', 'boolean', 'object', 'array', 'null'
SELECT
    product_name,
    jsonb_typeof(attributes -> 'price_usd')   AS price_type,     -- 'number'
    jsonb_typeof(attributes -> 'brand')        AS brand_type,     -- 'string'
    jsonb_typeof(attributes -> 'tags')         AS tags_type,      -- 'array'
    jsonb_typeof(attributes -> 'in_stock')     AS stock_type      -- 'boolean'
FROM io.thecodeforge.catalogue.products;

-- (9) jsonb_each: explode a JSONB object into key-value rows
-- Useful for dynamic pivoting or inspecting unknown structures
SELECT
    product_name,
    kv.key,
    kv.value
FROM io.thecodeforge.catalogue.products,
     jsonb_each(attributes) AS kv(key, value)
WHERE product_name = 'Wireless Headphones';

-- (10) jsonb_each_text: same but returns values as TEXT (no JSONB type wrapper)
SELECT
    product_name,
    kv.key,
    kv.value
FROM io.thecodeforge.catalogue.products,
     jsonb_each_text(attributes) AS kv(key, value)
WHERE product_name = 'Wireless Headphones';

-- (11) jsonb_array_elements: unnest a JSON array into rows for per-tag analysis
SELECT
    product_name,
    tag.value ->> 0 AS tag_value   -- each array element becomes a row
FROM io.thecodeforge.catalogue.products,
     jsonb_array_elements(attributes -> 'tags') AS tag(value);

-- Cleaner version using jsonb_array_elements_text for string arrays
SELECT
    product_name,
    tag_text
FROM io.thecodeforge.catalogue.products,
     jsonb_array_elements_text(attributes -> 'tags') AS tag_text
ORDER BY product_name, tag_text;

-- Set up an event log table with deeply nested JSONB payloads
CREATE TABLE io.thecodeforge.events.log (
    event_id    BIGSERIAL PRIMARY KEY,
    event_type  TEXT      NOT NULL,
    payload     JSONB     NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO io.thecodeforge.events.log (event_type, payload) VALUES
    ('purchase', '{
        "customer": {"id": 1001, "tier": "gold"},
        "items": [
            {"sku": "A1", "name": "Widget", "qty": 2, "unit_price": 29.99},
            {"sku": "B3", "name": "Gadget", "qty": 1, "unit_price": 29.99}
        ],
        "total": 89.99,
        "payment": {"method": "card", "last4": "4242"}
    }'),
    ('purchase', '{
        "customer": {"id": 1002, "tier": "silver"},
        "items": [
            {"sku": "C7", "name": "Cable", "qty": 5, "unit_price": 9.99}
        ],
        "total": 49.95,
        "payment": {"method": "paypal", "email": "user@example.com"}
    }'),
    ('refund', '{
        "customer": {"id": 1001, "tier": "gold"},
        "original_purchase_id": 42,
        "items": [
            {"sku": "A1", "name": "Widget", "qty": 1, "refund_amount": 29.99}
        ],
        "reason": "defective"
    }');

-- ── (1) jsonb_path_exists / @? : boolean check ──
-- Find all events where the customer is gold tier
SELECT event_id, event_type
FROM io.thecodeforge.events.log
WHERE payload @? '$.customer ? (@.tier == "gold")';

-- ── (2) jsonb_path_query : extract matching values ──
-- Get all SKUs from purchase events where qty > 1
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*] ? (@.qty > 1).sku') AS high_qty_sku
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- ── (3) Complex filter: items with unit_price > 20 in gold customer purchases ──
-- Without path expressions this requires subqueries and jsonb_array_elements.
-- With path expressions it's a single function call.
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*] ? (@.unit_price > 20)') AS expensive_item
FROM io.thecodeforge.events.log
WHERE payload @? '$.customer ? (@.tier == "gold")'
  AND event_type = 'purchase';

-- ── (4) Path expression with type casting ──
-- Extract the total as a numeric value directly
SELECT
    event_id,
    jsonb_path_query(payload, '$.total')::NUMERIC AS total_amount
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- ── (5) @@ operator : evaluate path expression as boolean ──
-- Find purchases where total exceeds 50
SELECT event_id, event_type
FROM io.thecodeforge.events.log
WHERE payload @@ '$.total > 50';

-- ── (6) Wildcard iteration: get all item names across all events ──
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[*].name') AS item_name
FROM io.thecodeforge.events.log;

-- ── (7) Array indexing: get the first item in every purchase ──
SELECT
    event_id,
    jsonb_path_query(payload, '$.items[0]') AS first_item
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase';

-- ── (8) Combining with relational filters ──
-- Recent gold customer purchases with expensive items
SELECT
    event_id,
    payload -> 'customer' ->> 'id' AS customer_id,
    jsonb_path_query(payload, '$.items[*] ? (@.unit_price > 25)') AS premium_item
FROM io.thecodeforge.events.log
WHERE event_type = 'purchase'
  AND payload @? '$.customer ? (@.tier == "gold")'
  AND created_at > NOW() - INTERVAL '7 days';

-- ── Scenario: 1 million product rows, we query by tags and brand frequently ──

-- Create the table (same structure as before, scaled up)
CREATE TABLE io.thecodeforge.catalogue.large_products (
    product_id   BIGSERIAL PRIMARY KEY,
    product_name TEXT      NOT NULL,
    attributes   JSONB     NOT NULL
);

-- ── GIN INDEX OPTION 1: jsonb_ops (default) ──
-- Indexes ALL keys, values, and key-value pairs.
-- Supports: @>, ?, ?|, ?&
-- Use when: you query by key existence AND containment
CREATE INDEX idx_products_attrs_gin
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes);          -- uses jsonb_ops by default

-- ── GIN INDEX OPTION 2: jsonb_path_ops ──
-- Only indexes values at the end of paths.
-- ~30% smaller than jsonb_ops, faster for @> only.
-- Does NOT support ?, ?|, ?&
-- Use when: you ONLY ever use @> containment queries
CREATE INDEX idx_products_attrs_gin_path
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes jsonb_path_ops);

-- ── GIN INDEX OPTION 3: Partial GIN index ──
-- Only index rows that match a WHERE clause.
-- Use when: you have a mix of active/archived data and only query active rows.
-- This is what saved us in the event ingestion incident.
CREATE INDEX idx_products_active_gin
    ON io.thecodeforge.catalogue.large_products
    USING GIN (attributes jsonb_path_ops)
    WHERE attributes @> '{"in_stock": true}';  -- only index in-stock products

-- ── GIN INDEX OPTION 4: Generated column + B-tree ──
-- Best when querying a SINGLE well-known field repeatedly.
-- Much cheaper to maintain than GIN, smaller index size.
ALTER TABLE io.thecodeforge.catalogue.large_products
    ADD COLUMN brand TEXT
    GENERATED ALWAYS AS (attributes ->> 'brand') STORED;  -- materialised at write time

CREATE INDEX idx_products_brand_btree
    ON io.thecodeforge.catalogue.large_products (brand);  -- standard B-tree

-- ── QUERY: Verify the GIN index is actually being used ──
-- Run EXPLAIN ANALYZE to confirm index usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT product_id, product_name
FROM io.thecodeforge.catalogue.large_products
WHERE attributes @> '{"tags": ["wireless"], "in_stock": true}';

-- ── QUERY: Demonstrate the generated column approach ──
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT product_id, product_name
FROM io.thecodeforge.catalogue.large_products
WHERE brand = 'SoundPeak';   -- hits the B-tree index, not GIN

-- ── BENCHMARK: GIN index impact on insert performance ──
-- Run BEFORE adding GIN index:
\timing on
INSERT INTO io.thecodeforge.catalogue.large_products (product_name, attributes)
SELECT
    'Product ' || i,
    jsonb_build_object(
        'brand', 'Brand_' || (i % 100),
        'price_usd', (random() * 500)::NUMERIC(10,2),
        'in_stock', (random() > 0.3),
        'tags', jsonb_build_array('tag_' || (i % 20), 'tag_' || (i % 7))
    )
FROM generate_series(1, 100000) AS s(i);
-- Note the time, then DROP the GIN index and run again.
-- Typical results:
--   Without GIN: ~3.2 seconds for 100K inserts
--   With GIN (jsonb_ops):      ~14.8 seconds (4.6x slower)
--   With GIN (jsonb_path_ops): ~11.2 seconds (3.5x slower)

-- ── MONITORING: Check index size and usage stats ──
SELECT
    indexrelname                    AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan                        AS times_used,
    idx_tup_read                    AS tuples_read
FROM pg_stat_user_indexes
WHERE relname = 'large_products'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Reuse the user_profiles table from the schema design section
CREATE TABLE io.thecodeforge.users.profiles (
    user_id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    email           TEXT         NOT NULL UNIQUE,
    preferences     JSONB        NOT NULL DEFAULT '{}',
    integrations    JSONB        NOT NULL DEFAULT '{}'
);

INSERT INTO io.thecodeforge.users.profiles (email, preferences, integrations) VALUES
    ('alice@example.com',
     '{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"dashboard":{"layout":"grid","widgets":["metrics","alerts"]}}',
     '{"slack":{"workspace":"acme","channel":"#alerts"}}'),
    ('bob@example.com',
     '{"theme":"light","language":"fr","notifications":{"email":true,"sms":true}}',
     '{}');

-- ── (1) jsonb_set: update a nested value ──
-- Flip Alice's SMS notification to true
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_set(
    preferences,
    '{notifications,sms}',     -- path: two levels deep
    'true'::jsonb,             -- new value (must be ::jsonb)
    false                      -- create_missing: false = error if path doesn't exist
)
WHERE email = 'alice@example.com';

-- Verify
SELECT email, preferences -> 'notifications' AS notifs
FROM io.thecodeforge.users.profiles WHERE email = 'alice@example.com';
-- Result: {"email": true, "sms": true}

-- ── (2) jsonb_set with create_missing = true ──
-- Add a new key that doesn't exist yet
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_set(
    preferences,
    '{notifications,push}',    -- this key doesn't exist yet
    'true'::jsonb,
    true                       -- create it
)
WHERE email = 'alice@example.com';

-- ── (3) Chained jsonb_set for multiple updates in one statement ──
-- Change theme AND add a new notification pref in one UPDATE
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_set(
    jsonb_set(preferences, '{theme}', '"system"'::jsonb, false),
    '{notifications,digest}', '"weekly"'::jsonb, true
)
WHERE email = 'alice@example.com';

-- ── (4) jsonb_insert: add without overwriting ──
-- Add a 'timezone' key — will ERROR if it already exists
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_insert(
    preferences,
    '{timezone}',              -- path to insert at
    '"America/New_York"'::jsonb
)
WHERE email = 'alice@example.com';

-- ── (5) jsonb_strip_nulls: clean up null values ──
-- Useful when API payloads contain null for optional fields
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_strip_nulls(preferences)
WHERE email = 'alice@example.com';

-- ── (6) jsonb_agg: build a JSON array from relational rows ──
-- Construct an API response: list all users with their theme as JSON
SELECT jsonb_agg(
    jsonb_build_object(
        'email', email,
        'theme', preferences ->> 'theme',
        'has_slack', integrations ? 'slack'
    )
) AS api_response
FROM io.thecodeforge.users.profiles;

-- ── (7) jsonb_object_agg: build a JSON object from key-value rows ──
-- Pivot user preferences into a single JSON object keyed by email
SELECT jsonb_object_agg(email, preferences -> 'notifications')
    AS all_notifications
FROM io.thecodeforge.users.profiles;

-- ── (8) jsonb_pretty: format for debugging ──
SELECT email, jsonb_pretty(preferences)
FROM io.thecodeforge.users.profiles
WHERE email = 'alice@example.com';

-- ── (9) Removing a key: set it to NULL then strip nulls ──
-- PostgreSQL has no jsonb_remove_key function, so we use this pattern
UPDATE io.thecodeforge.users.profiles
SET preferences = jsonb_strip_nulls(
    jsonb_set(preferences, '{dashboard}', 'null'::jsonb, false)
)
WHERE email = 'alice@example.com';

-- ── (10) Monitoring WAL impact of JSONB updates ──
-- Run this before and after your update workload to measure overhead
SELECT
    pg_size_pretty(pg_total_relation_size('io.thecodeforge.users.profiles'))
        AS total_size,
    pg_size_pretty(pg_relation_size('io.thecodeforge.users.profiles'))
        AS table_size,
    pg_size_pretty(pg_total_relation_size('io.thecodeforge.users.profiles')
        - pg_relation_size('io.thecodeforge.users.profiles'))
        AS toast_size;

-- Check dead tuple count (indicates vacuum pressure from TOAST rewrites)
SELECT
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::NUMERIC / GREATEST(n_live_tup, 1) * 100, 1)
        AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'profiles';

-- ── A production-grade hybrid schema for a SaaS user profile system ──

CREATE TABLE io.thecodeforge.users.sso_profiles (
    -- Anchor fields: relational columns for everything queryable/joinable
    user_id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    email           TEXT         NOT NULL UNIQUE,
    account_tier    TEXT         NOT NULL DEFAULT 'free'
                                 CHECK (account_tier IN ('free', 'pro', 'enterprise')),
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    last_active_at  TIMESTAMPTZ,

    -- JSONB for flexible, schema-light data that varies per user
    preferences     JSONB        NOT NULL DEFAULT '{}',  -- UI prefs, notification settings
    integrations    JSONB        NOT NULL DEFAULT '{}'   -- third-party OAuth tokens, webhooks
);

-- Document the expected structure in a column comment
COMMENT ON COLUMN io.thecodeforge.users.sso_profiles.preferences IS
    'User UI and notification preferences. Expected top-level keys:
     theme (string), language (string), notifications (object with email/sms/push booleans),
     dashboard (object with layout string and widgets array).';

COMMENT ON COLUMN io.thecodeforge.users.sso_profiles.integrations IS
    'Third-party integration configs. Keys are service names (slack, github, jira).
     Each value is an object with service-specific fields.';

-- Index anchor fields the relational way
CREATE INDEX idx_sso_tier ON io.thecodeforge.users.sso_profiles (account_tier);
CREATE INDEX idx_sso_last_active ON io.thecodeforge.users.sso_profiles (last_active_at DESC NULLS LAST);

-- GIN index only on the JSONB we'll actually query
CREATE INDEX idx_sso_preferences_gin
    ON io.thecodeforge.users.sso_profiles USING GIN (preferences jsonb_path_ops);

-- Insert realistic user data
INSERT INTO io.thecodeforge.users.sso_profiles (email, account_tier, preferences, integrations) VALUES
    ('alice@example.com', 'pro',
     '{"theme": "dark", "language": "en", "notifications": {"email": true, "sms": false}}',
     '{"slack": {"workspace": "acme-corp", "channel": "#alerts"}, "github": {"username": "alicecodes"}}'
    ),
    ('bob@example.com', 'free',
     '{"theme": "light", "language": "fr", "notifications": {"email": true, "sms": true}}',
     '{}'
    ),
    ('carol@example.com', 'enterprise',
     '{"theme": "dark", "language": "de", "notifications": {"email": false, "sms": false}, "custom_domain": "carol.io"}',
     '{"slack": {"workspace": "bigcorp", "channel": "#ops"}}'
    );

-- ── UPDATING JSONB SAFELY: use jsonb_set, not full-document replace ──
-- BAD: rewrites the ENTIRE preferences blob even if 99% is unchanged
-- UPDATE io.thecodeforge.users.sso_profiles
-- SET preferences = '{"theme":"system", ...all other keys...}' WHERE email = 'alice@example.com';

-- GOOD: surgical update using jsonb_set
UPDATE io.thecodeforge.users.sso_profiles
SET preferences = jsonb_set(
    preferences,
    '{notifications, sms}',
    'true'::jsonb,
    false
)
WHERE email = 'alice@example.com';

-- ── QUERYING: mix relational and JSONB filters naturally ──
-- Find pro/enterprise users who prefer dark mode and have Slack connected
SELECT
    email,
    account_tier,
    preferences ->> 'theme'      AS theme,
    preferences ->> 'language'   AS language,
    integrations -> 'slack' ->> 'workspace' AS slack_workspace
FROM io.thecodeforge.users.sso_profiles
WHERE account_tier IN ('pro', 'enterprise')   -- hits B-tree index
  AND preferences @> '{"theme": "dark"}'       -- hits GIN index
  AND integrations ? 'slack'                   -- existence check
ORDER BY last_active_at DESC NULLS LAST;

-- ── BUILDING ANALYTICS: aggregate JSONB data across rows ──
-- Count users by preferred language
SELECT
    preferences ->> 'language'   AS language,
    COUNT(*)                      AS user_count,
    COUNT(*) FILTER (WHERE account_tier = 'pro') AS pro_count
FROM io.thecodeforge.users.sso_profiles
GROUP BY preferences ->> 'language'
ORDER BY user_count DESC;

-- ── PROMOTING A JSONB FIELD TO A REAL COLUMN ──
-- If 'theme' appears in WHERE on 3+ queries, promote it.
-- Step 1: Add the column
ALTER TABLE io.thecodeforge.users.sso_profiles
    ADD COLUMN theme TEXT
    GENERATED ALWAYS AS (preferences ->> 'theme') STORED;

-- Step 2: Index it
CREATE INDEX idx_sso_theme ON io.thecodeforge.users.sso_profiles (theme);

-- Step 3: Rewrite queries to use the column instead of JSONB extraction
-- BEFORE: WHERE preferences ->> 'theme' = 'dark'  (no index)
-- AFTER:  WHERE theme = 'dark'                     (hits B-tree)
SELECT email, theme
FROM io.thecodeforge.users.sso_profiles
WHERE theme = 'dark';

-- ── PHASE 1: Add shadow JSONB column ──
ALTER TABLE io.thecodeforge.legacy.events
    ADD COLUMN payload_jsonb JSONB;

-- Backfill in batches to avoid long-running locks
-- Run this in a loop, incrementing the offset
DO $$
DECLARE
    v_batch_size  INT := 10000;
    v_offset      INT := 0;
    v_updated     INT;
BEGIN
    LOOP
        UPDATE io.thecodeforge.legacy.events
        SET payload_jsonb = payload::JSONB
        WHERE event_id IN (
            SELECT event_id
            FROM io.thecodeforge.legacy.events
            WHERE payload_jsonb IS NULL
              AND payload IS NOT NULL
            ORDER BY event_id
            LIMIT v_batch_size
            OFFSET v_offset
        );

        GET DIAGNOSTICS v_updated = ROW_COUNT;
        EXIT WHEN v_updated = 0;

        COMMIT;
        PERFORM pg_sleep(0.1);  -- throttle to reduce lock contention
    END LOOP;
END $$;

-- Catch rows that failed to parse (malformed JSON)
INSERT INTO io.thecodeforge.legacy.migration_errors (
    table_name, record_id, error_message, raw_value
)
SELECT
    'events',
    event_id,
    'JSON parse failed',
    LEFT(payload, 500)  -- truncate for logging
FROM io.thecodeforge.legacy.events
WHERE payload_jsonb IS NULL
  AND payload IS NOT NULL;

-- ── PHASE 2: Dual-write trigger (application-level or DB trigger) ──
-- If you cannot modify the application, use a trigger:
CREATE OR REPLACE FUNCTION io.thecodeforge.legacy.events_dual_write()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.payload IS NOT NULL THEN
        BEGIN
            NEW.payload_jsonb := NEW.payload::JSONB;
        EXCEPTION
            WHEN OTHERS THEN
                -- Log the error but don't block the insert
                INSERT INTO io.thecodeforge.legacy.migration_errors
                    (table_name, record_id, error_message, raw_value)
                VALUES
                    ('events', NEW.event_id, SQLERRM, LEFT(NEW.payload, 500));
        END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_events_dual_write
    BEFORE INSERT OR UPDATE ON io.thecodeforge.legacy.events
    FOR EACH ROW
    EXECUTE FUNCTION io.thecodeforge.legacy.events_dual_write();

-- ── PHASE 3: Dual-read with fallback (example query) ──
SELECT
    event_id,
    COALESCE(
        payload_jsonb,                  -- prefer JSONB
        payload::JSONB                  -- fallback to TEXT conversion
    ) AS event_payload
FROM io.thecodeforge.legacy.events
WHERE event_id = 12345;

-- ── PHASE 4: Validate completeness before cut-over ──
-- This query should return ZERO rows before you proceed
SELECT COUNT(*) AS missing_jsonb
FROM io.thecodeforge.legacy.events
WHERE payload IS NOT NULL
  AND payload_jsonb IS NULL;

-- ── PHASE 4: Add GIN indexes after cut-over ──
CREATE INDEX idx_events_payload_gin
    ON io.thecodeforge.legacy.events
    USING GIN (payload_jsonb jsonb_path_ops);

-- ── PHASE 5: Drop the old column (IRREVERSIBLE — be certain) ──
-- ALTER TABLE io.thecodeforge.legacy.events DROP COLUMN payload;
-- ALTER TABLE io.thecodeforge.legacy.events RENAME COLUMN payload_jsonb TO payload;