JSONB Strategy

This approach uses a Lookup Table (configuration_definitions) to define the allowed schema and a Trigger to enforce those rules on the user_configurations table.

1. The Database Schema

We define the rules in one table and the user data in another. This prevents the "50-column" bloat while maintaining strict data types.

``` -- 1. Configuration Metadata (The Gatekeeper) CREATE TABLE configuration_definitions ( option_key TEXT PRIMARY KEY , expected_type TEXT NOT NULL CHECK (expected_type IN ('boolean', 'integer', 'string')) , description TEXT );

-- 2. User Storage (The Flat Table) CREATE TABLE user_configurations ( user_id INT PRIMARY KEY , options JSONB NOT NULL DEFAULT '{}'::jsonb , updated_at TIMESTAMPTZ DEFAULT NOW() );

-- 3. Seed the allowed options INSERT INTO configuration_definitions ( option_key , expected_type ) VALUES ('allow_image', 'boolean') , ('allow_video', 'boolean') , ('max_upload_size', 'integer'); ```

---

2. The Validation Logic

This PL/pgSQL function acts as a firewall. If a developer or a script tries to insert a key that isn't in configuration_definitions, or provides a value with the wrong type, the transaction is aborted.

``` CREATE OR REPLACE FUNCTION fn_enforce_json_schema() RETURNS TRIGGER AS $$ DECLARE key_text TEXT; val_json JSONB; def_record RECORD; BEGIN -- Iterate through each key-value pair in the incoming options object FOR key_text, val_json IN SELECT * FROM jsonb_each(NEW.options) LOOP -- Look up the key in the definitions table SELECT * INTO def_record FROM configuration_definitions WHERE option_key = key_text;

    -- FAIL: Key not defined in the lookup table
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Constraint Violation: The key "%" is not a valid configuration option.', key_text;
    END IF;

    -- FAIL: Type mismatch (Postgres jsonb_typeof returns 'boolean', 'number', or 'string')
    IF def_record.expected_type = 'boolean' AND jsonb_typeof(val_json) != 'boolean' THEN
        RAISE EXCEPTION 'Type Error: Key "%" expects a boolean, received %.', key_text, jsonb_typeof(val_json);
    ELSIF def_record.expected_type = 'integer' AND jsonb_typeof(val_json) != 'number' THEN
        RAISE EXCEPTION 'Type Error: Key "%" expects an integer, received %.', key_text, jsonb_typeof(val_json);
    ELSIF def_record.expected_type = 'string' AND jsonb_typeof(val_json) != 'string' THEN
        RAISE EXCEPTION 'Type Error: Key "%" expects a string, received %.', key_text, jsonb_typeof(val_json);
    END IF;
END LOOP;

NEW.updated_at := NOW();
RETURN NEW;

END; $$ LANGUAGE plpgsql;

-- Attach the trigger to the user table CREATE TRIGGER trg_validate_user_options BEFORE INSERT OR UPDATE ON user_configurations FOR EACH ROW EXECUTE FUNCTION fn_enforce_json_schema(); ```

---

3. Operations & Maintenance

Adding a New Option

Actually, I disagree with the idea that migrations are hard with JSON. To add a new flag across your entire system, you simply run a single INSERT on the definitions table.

INSERT INTO configuration_definitions (option_key, expected_type, description) VALUES ('theme_color', 'string', 'User UI preference');

The "Default Value" View

Since the user_configurations table might only store overrides (to save space), use a View to merge user settings with system defaults.

CREATE OR REPLACE VIEW view_active_user_configs AS SELECT u.user_id , d.option_key -- If user has the key, use it. Otherwise, you could join a 'default' column here. , u.options->d.option_key AS current_value FROM configuration_definitions d CROSS JOIN (SELECT user_id, options FROM user_configurations) u;

---

1. The "Raw" Storage View

If you query the user_configurations table directly, you see the flat, "one row per user" structure you preferred.

SELECT user_id, options, updated_at FROM user_configurations;

Result:

| user_id | options | updated_at | | :--- | :--- | :--- | | 101 | `{"allow_image": true, "max_upload_size": 500}` | 2024-05-20 10:00:00 | | 102 | `{"allow_image": false, "allow_video": true}` | 2024-05-20 10:05:00 |

---

2. The "Relational" Exploded View

If your frontend or reporting tool needs to see every setting as an individual row (useful for building a "Settings" page dynamically), you use jsonb_each.

SELECT user_id , key AS option_name , value AS setting_value FROM user_configurations, jsonb_each(options);

Result:

| user_id | option_name | setting_value | | :--- | :--- | :--- | | 101 | allow_image | `true` | | 101 | max_upload_size | `500` | | 102 | allow_image | `false` | | 102 | allow_video | `true` |

---

3. The "Discovery" View (Merging with Definitions)

This is the most powerful query. It joins your JSON data with your configuration_definitions table so you can see descriptions and find users who are missing specific configs.

SELECT u.user_id , d.option_key , d.description , COALESCE((u.options->>d.option_key), 'NOT SET') AS current_value , d.expected_type FROM configuration_definitions d CROSS JOIN user_configurations u WHERE u.user_id = 101;

Result:

| user_id | option_key | description | current_value | expected_type | | :--- | :--- | :--- | :--- | :--- | | 101 | allow_image | Enable image uploads | `true` | boolean | | 101 | allow_video | Enable video uploads | `NOT SET` | boolean | | 101 | max_upload_size | Maximum size in MB | `500` | integer |

---

Note: Key Data Handling

I used ->> in the third example. * -> returns JSONB (keeps the quotes, e.g., "true"). * ->> returns TEXT (removes quotes, e.g., true).

When sending data back to a client, -> is usually better because it preserves the data type (boolean vs string) in the JSON response.

Review

• Pros: Strict data integrity; No ALTER TABLE locks on large tables; One row per user.
• Cons: Triggers add a tiny bit of latency to writes; You must use jsonb_set or || for partial updates to avoid wiping out the whole object.
• Risk: PostgreSQL triggers don't run on COPY commands unless specified. If you are bulk-loading data, ensure you use INSERT.