JSONB and Structured Column Population Prompt Template

Full CREATE TABLE statement defining target columns, types, constraints, and indexes including the JSONB column definition

CREATE TABLE events (id UUID PRIMARY KEY, payload JSONB NOT NULL, created_at TIMESTAMPTZ DEFAULT now()); CREATE INDEX idx_payload ON events USING GIN (payload jsonb_path_ops);

Parse with a SQL parser to confirm valid DDL. Verify JSONB column exists. Check for GIN index definitions that constrain valid operators.

Unstructured or semi-structured input text containing the data to be mapped into JSONB columns

User signed up from mobile app. Name: Jane Doe. Preferences: dark mode enabled, notifications for weekly digest only. Referral code: FRIEND-2025.

Check for non-empty input. If input contains pre-existing JSON, validate it parses before passing to the prompt. Flag inputs over 8KB for chunking consideration.

Explicit mapping of which source fields go into which JSONB keys, including nesting paths and array indicators

name -> payload.user.name, preferences -> payload.settings, referral_code -> payload.attribution.code, signup_channel -> payload.attribution.source

Validate every target key path against [TABLE_SCHEMA] column names. Confirm no duplicate key assignments. Check for required NOT NULL field coverage.

Per-field type coercion instructions for JSONB value construction, distinguishing strings from numbers from booleans from nulls

payload.user.name: text, payload.settings.dark_mode: boolean, payload.attribution.code: text, payload.settings.digest_frequency: text

Verify boolean fields map to true/false not 'true'/'false' strings. Check numeric fields for precision requirements. Flag any field without an explicit cast rule.

Policy for handling missing, empty, or undefined source fields when constructing JSONB values

Missing fields: omit key entirely. Empty strings: store as JSON null. Undefined booleans: omit key. Explicit user nulls: store as JSON null.

Test with a deliberately sparse [SOURCE_RECORDS] to confirm null policy is applied. Check for NOT NULL column constraint violations when keys are omitted.

Instructions for handling special characters, Unicode, backslashes, and quotes within JSONB string values

Double-quote all string values. Escape internal double quotes with backslash. Preserve Unicode as-is. Escape backslashes. No HTML entity encoding.

Validate output with a JSON parser. Common failure: unescaped backslashes in Windows paths or regex patterns inside JSONB strings.

Specification of the exact SQL output structure including INSERT framing, quoting style, and any transaction wrapping

Single INSERT statement per row. Use dollar-quoting for JSONB literal: INSERT INTO events (id, payload) VALUES (gen_random_uuid(), $JSON${...}$JSON$);

Parse output with a SQL parser. Verify dollar-quote delimiters do not appear inside the JSONB content. Check that generated UUIDs or sequences are valid for the column type.

List of database constraints the generated INSERT must satisfy beyond basic JSONB validity

payload JSONB NOT NULL, GIN index on payload for @> operator, no top-level keys exceeding 32 characters, max nesting depth 4 levels

After generation, run EXPLAIN on the INSERT to confirm index usage. Check key length limits. Validate nesting depth with a recursive JSON walker. Flag any constraint violation before execution.

SQL string

Must start with INSERT INTO [TABLE_NAME] and end with a semicolon. Parse check: valid SQL syntax.

SQL identifier

Must match the provided target table name exactly. Case-sensitive check against input schema.

Comma-separated identifiers

Must include all NOT NULL columns from the provided schema. Schema check: no missing required columns, no extra columns not in schema.

Valid JSON string

Must be a properly escaped JSON string literal. Parse check: PostgreSQL JSONB parser accepts the value. Nested objects and arrays must be syntactically valid.

SQL typed literals

Each value must match the column's data type (integer, boolean, timestamp, etc.). Type check: no implicit coercion errors like string-to-integer.

NULL keyword or omitted

NULLABLE columns may use NULL or be omitted from the column list. NOT NULL columns must never receive NULL. Schema check: constraint violation detection.

SQL-standard escaping

Single quotes doubled for string literals. JSONB strings must not break the SQL string boundary. Parse check: no unescaped quotes inside values.

Output parses as valid JSON without syntax errors. All braces, brackets, and quotes are balanced and escaped correctly.

Parser throws a syntax error. Common causes: unescaped double quotes inside string values, trailing commas, or single quotes used for keys.

Parse the extracted JSONB value with a standard JSON parser (e.g., json.loads in Python). Test must pass without exceptions.

The JSONB value is of the expected top-level type (object or array) as defined in the target column's schema constraint.

A JSON object is generated when an array is required, or vice-versa. This breaks downstream queries that use JSONB operators expecting a specific type.

Check the root type of the parsed JSON value against the schema definition. Use isinstance(data, dict) or isinstance(data, list).

All fields marked as required in the [OUTPUT_SCHEMA] are present in the generated JSONB object. No required keys are missing.

A required key is absent. Downstream application code that directly accesses the key will fail with a KeyError or undefined value.

Iterate over the list of required fields from the schema and assert each is a key in the generated object. Use a strict set comparison.

Each field's value matches its specified type (e.g., string, integer, boolean) and format (e.g., ISO 8601 date, email). No type coercion errors.

A numeric field contains a string ('123'), a boolean is 'true' (string), or a date is in an ambiguous format (01/02/2025).

Validate each field's value against its schema definition using a library like jsonschema or manual isinstance checks. Test date strings with a strict regex.

Nested objects within the JSONB value are complete and correctly structured. No truncated or empty objects where data is expected.

A nested object like address is {} or null when the source text clearly contained address information. Indicates the model lost context or gave up on complexity.

Assert that nested objects are not empty if the corresponding [INPUT] text contains relevant data. Check for minimum key counts in known complex objects.

Arrays are valid JSON arrays containing the correct number of elements. Elements within arrays conform to their item schema.

An array is serialized as a string ('["a", "b"]'), contains a single string with comma-separated values, or has missing/null elements.

Assert the value is a list type. Check len(array) against the expected number of elements from the input. Validate each element's type.

Fields restricted to an enum or controlled vocabulary contain only allowed values. No out-of-vocabulary or hallucinated status codes.

A status field contains 'In Progress' when the allowed enum is ['open', 'closed', 'pending']. Breaks application logic and database check constraints.

Maintain a set of allowed values for each enum field. Assert value in ALLOWED_VALUES. Flag any value not in the set for human review.

The full output is a syntactically correct SQL INSERT statement. The JSONB value is properly cast and escaped for PostgreSQL.

The INSERT statement is missing a closing parenthesis, the JSONB value is not wrapped in single quotes, or the cast ::jsonb is missing.

Execute the statement in a dry-run mode (e.g., PostgreSQL EXPLAIN) or validate with a SQL parser. Check for balanced parentheses and correct quoting.