schema-exploration/references/query-authoring.md

Author and validate a query

Use this only when the user asks for SQL against an explored schema. Follow the user's authorization for data access; writing SQL does not require reading application rows.

1. Establish the result's grain and parameters. Read the relevant table columns, constraints, and foreign keys, or view definitions. Include schemas in relation names. For a composite foreign key, join on every key column; names alone do not prove a relationship. Check whether defaults or generated columns affect the proposed join.
2. Build the query around that grain. Decide whether missing related rows should be retained (left join), where predicates belong so the outer join remains outer, and whether aggregates count rows or non-null identifiers. A subquery in from cannot reference an earlier from item unless it is lateral; a correlated scalar subquery has different scoping rules. Avoid inferring effective permissions, tenant mapping, or other business semantics from a syntactically valid join.
3. If authorized, validate the final, parameterized select with plain explain (costs off) in a read-only transaction with a reasonable statement timeout. This parses, binds, and plans the statement without running its executor; inspect the plan for the intended joins, filters, and grouping, and fix any SQL errors. Use your client's parameter binding (or the psql adapter). Do not run explain analyze, which executes the query. Do not execute the final query merely to check whether it works.
4. Planning is not a universal no-side-effect sandbox: foreign-data wrappers may contact remote systems, and planner hooks, support functions, or user-defined functions can run during planning. Inspect unfamiliar relations and expressions first. If planning might have such effects, or the role lacks the necessary privileges, do not bypass restrictions; say that validation was unavailable and provide the SQL as unvalidated.
5. A successful explain establishes mechanical validity under the current role and schema, not correct results or business meaning. State assumptions and any unverified semantics with the final SQL.