How to document your database so AI agents write better SQL

Why does documentation change the quality of the SQL?

A model that sees only table and column names has to guess. Is status a text field or a code? Does total include tax? Does created_at mean when the customer signed up or when the order was placed? Each wrong guess produces a query that runs fine and returns the wrong answer, with no error to warn you.

A short description removes most of that guessing. It also costs you almost nothing: the descriptions live next to the schema, and you write them once.

What should you document for AI agents, in order of impact?

• What each table is. One sentence: what a row represents.
• Columns with unclear names or codes. st, flag, type, val.
• Allowed values. For status and category columns, list them.
• Units and meaning. Currency, tax included or not, time zone, what a date refers to.
• Relationships. Which tables join, on which columns, and the cardinality.
• Business definitions. What "active customer" or "late order" means in your company.
• Example questions with correct SQL. The best few-shot material you can give.

How do you add table and column comments in Postgres?

Postgres has a built-in place for descriptions. Use it, so the documentation travels with the schema.

COMMENT ON TABLE public.orders IS
  'One row per customer order. Cancelled orders stay in this table.';

COMMENT ON COLUMN public.orders.status IS
  'Order state: pending, shipped, delivered, cancelled.';

COMMENT ON COLUMN public.orders.total IS
  'Order total in euros, tax included.';

COMMENT ON COLUMN public.customers.country IS
  'Two-letter ISO country code, for example ES or FR.';

Keep each comment to one or two sentences. Say what the thing means, not what its name already says. "The id column is the id" adds nothing, while "Order total in euros, tax included" removes a whole class of mistakes.

Comments are visible to anyone who can read the catalog, so don't put secrets, personal data or internal remarks in them.

How do you spell out allowed values for status and code columns?

Codes and statuses cause the most silent errors. In our own test, an agent wrote status = 'canceled' instead of 'cancelled'. The query was valid, returned no rows and raised no error. Listing the exact values in the comment prevents it.

If you can, use a Postgres enum type or a check constraint, which also protects your data. If the column is plain text, list the values in the comment. To find out which values exist, look at a low-cardinality column:

SELECT DISTINCT status FROM public.orders LIMIT 20;

Only do this for columns that aren't sensitive, and never copy free-text values (names, notes, messages) into your documentation.

How should you describe relationships for text-to-SQL?

Foreign keys already tell the agent which columns join. Add what they can't say: the cardinality and the meaning.

SELECT con.conrelid::regclass AS from_table, att.attname AS from_column,
       con.confrelid::regclass AS to_table,  fatt.attname AS to_column
FROM pg_constraint con
JOIN LATERAL unnest(con.conkey, con.confkey) AS k(attnum, fattnum) ON true
JOIN pg_attribute att  ON att.attrelid  = con.conrelid  AND att.attnum  = k.attnum
JOIN pg_attribute fatt ON fatt.attrelid = con.confrelid AND fatt.attnum = k.fattnum
WHERE con.contype = 'f' AND con.connamespace = 'public'::regnamespace;

This lists every single-column foreign key. For each one, write a sentence such as "many orders belong to one customer". Where a relationship exists but has no foreign key (common in older databases), write it down explicitly, because the agent can't discover it.

How do you define business terms for the agent?

The words people use in questions rarely match column names. Keep a short glossary and give it to the agent:

• Active customer: a customer with at least one order in the last 12 months.
• Late order: an order still pending 7 days after its creation date.
• Revenue: the sum of orders.total for orders that are not cancelled.

Without these, the agent invents its own definition, and two people asking the same question get different numbers.

Why add example questions with the correct SQL?

Three to ten verified examples often improve accuracy more than a long description. Choose the questions people really ask, including the tricky ones (joins, date ranges, exclusions):

-- Q: Which customers have a cancelled order?
SELECT DISTINCT c.name
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled';

These examples also become the start of your test set (see our guide to testing a text-to-SQL agent: /blog/how-to-test-a-text-to-sql-agent).

When should you simplify with views?

If answering a common question takes three joins and a filter, create a view that does it once, and document the view instead:

CREATE VIEW public.active_orders AS
  SELECT id, customer_id, status, total, created_at
  FROM public.orders
  WHERE status <> 'cancelled';

COMMENT ON VIEW public.active_orders IS
  'Orders that are not cancelled. Use this for revenue and order counts.';

A view hides the awkward logic from the model, and the logic is written once, by you, and not guessed each time.

How do you turn the schema into text for the prompt?

Put it all together as a compact description the agent receives. The script below reads the catalog and builds the text. Run it with the same role your agent uses, so it describes only what the agent can see.

import psycopg

COLUMNS = """
SELECT c.table_name, c.column_name, c.data_type,
       col_description((quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass,
                       c.ordinal_position)
FROM information_schema.columns c
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position
"""

TABLES = """
SELECT t.table_name,
       obj_description((quote_ident(t.table_schema) || '.' || quote_ident(t.table_name))::regclass, 'pg_class')
FROM information_schema.tables t
WHERE t.table_schema = 'public'
"""

FKS = """
SELECT con.conrelid::regclass::text, att.attname, con.confrelid::regclass::text, fatt.attname
FROM pg_constraint con
JOIN LATERAL unnest(con.conkey, con.confkey) AS k(attnum, fattnum) ON true
JOIN pg_attribute att  ON att.attrelid  = con.conrelid  AND att.attnum  = k.attnum
JOIN pg_attribute fatt ON fatt.attrelid = con.confrelid AND fatt.attnum = k.fattnum
WHERE con.contype = 'f' AND con.connamespace = 'public'::regnamespace
"""

def build_schema_text(dsn: str) -> str:
    with psycopg.connect(dsn) as conn:
        tables = dict(conn.execute(TABLES).fetchall())
        columns = conn.execute(COLUMNS).fetchall()
        fks = conn.execute(FKS).fetchall()

    visible = set(tables)
    lines = []
    for table, desc in sorted(tables.items()):
        lines.append(f"Table {table}: {desc or 'no description'}")
        for t, col, dtype, comment in columns:
            if t == table:
                lines.append(f"  {col} ({dtype}): {comment or ''}".rstrip(': '))
    for ft, fc, tt, tc in fks:
        if ft in visible and tt in visible:  # skip links to tables the role can't see
            lines.append(f"Relationship: {ft}.{fc} -> {tt}.{tc} (many {ft} rows per {tt} row)")
    return "\n".join(lines)

On a test database where the agent's role had access to only some columns, the output was:

Table customers: One row per customer.
  id (integer)
  name (text)
  country (text): Two-letter ISO country code, for example ES or FR.
  created_at (date)
Table orders: One row per customer order. Cancelled orders stay in this table.
  id (integer)
  customer_id (integer)
  status (text): Order state: pending, shipped, delivered, cancelled.
  total (numeric): Order total in euros, tax included.
  created_at (date)
Relationship: orders.customer_id -> customers.id (many orders rows per customers row)

The email column, the internal_notes column and a secrets table that the role couldn't read are all absent. That is the point of running it with the agent's own role: you can't accidentally describe something the agent shouldn't use. Add your glossary and your example questions after this text.

What are common mistakes when documenting a database for AI agents?

• Describing everything. A prompt with 200 tables confuses the model and costs more. Document and expose only what the agent needs.
• Repeating the name. "user_id: the id of the user" helps no one.
• Leaving codes unexplained. Status values, flags and abbreviations are where most silent errors come from.
• Letting descriptions go stale. When a column changes, its comment must change too. Treat comments as part of the migration.
• Documenting data you want kept private. Comments aren't a safe place for sensitive facts.
• Skipping the test. You only know a description helps by running questions before and after (see /blog/how-to-test-a-text-to-sql-agent).

How do you know the documentation worked?

Write 20 or so real questions with known answers and run them before and after you add the descriptions. If accuracy goes up, keep going. If a particular question still fails, look at the query: it usually points to a missing definition, an undocumented value or a missing relationship.

Where Claix fits

Claix reads your database's structure and drafts a Data Map for you: descriptions for each table, the relationships it finds and the ones it suggests. You review and edit it, choose which tables and columns each route can use, and Claix gives the agent that description. You can also start from the comments you already have in the database. See Claix Retrieve (https://www.claix.dev/), and next: how to test a text-to-SQL agent (/blog/how-to-test-a-text-to-sql-agent).