Skip to content

timescale/pg-aiguide / skills/schema-exploration

schema-exploration

Verified

Explore an existing PostgreSQL database before answering questions about its data or writing SQL. Use this skill whenever a user asks for a query or a data-backed answer against an unfamiliar schema (counts, missing or failed records, recent changes), asks where a business concept lives, or asks how tables, joins, views, routines, triggers, RLS, or extensions work. Find the relevant objects with read-only pg_catalog queries, then request approval before inspecting data-derived statistics or rows. Not a schema-design or migration guide.

references/query-authoring.md

View on GitHub
# 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](tables.md), or [view definitions](views.md). 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](psql.md)). 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.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall