AutoGineer
Generated banner for Schema Exploration
Generated logo for Schema Exploration

Schema Exploration

Timescale (TigerData)OfficialCreated by tigerdataDistributed by Timescale (TigerData)

  • Skill
  • Tool integration
  • Data and AI
  • Apache-2.0
  • No issues found
Download ZIP

From the author

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.
— the description by tigerdata

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a systematic approach for AI agents to investigate unfamiliar PostgreSQL databases. It emphasizes read-only catalog exploration to understand schema structure, business concepts, and object relationships. By prioritizing metadata analysis over direct data inspection, it ensures safe, authorized, and context-aware interactions with database environments, requiring explicit user approval before accessing data-derived statistics or row samples.

What it does

  • Inventory database schemas, tables, views, and routines using read-only catalog queries.
  • Corroborate business meaning through comments, keys, and dependency analysis.
  • Request and manage user approval for accessing data-derived statistics or row samples.
  • Validate query plans using EXPLAIN without executing data-modifying operations.
  • Identify objects owned by installed PostgreSQL extensions.
  • Report findings while clearly distinguishing between observed facts and inferences.

When to use it

  • When you need to write SQL for an unfamiliar database schema.
  • When a user asks for data-backed answers like counts or recent changes.
  • When you need to locate where specific business concepts reside in the database.
  • When you need to understand how specific tables, views, triggers, or RLS policies function.

Advantages

  • Minimizes risk by defaulting to read-only metadata exploration.
  • Prevents unauthorized data exposure by requiring explicit approval for statistics and row samples.
  • Provides a clear, repeatable process for mapping database structure to business logic.
  • Reduces errors in query generation by validating join keys and grains first.
  • Ensures transparency by reporting limitations and distinguishing observations from assumptions.

Usage examples

“How many active users are in the database?”
The agent will first identify the relevant user table, check its schema and constraints, and then request approval to query statistics or sample rows to provide an accurate count.
“Where is the order history stored?”
The agent will search the catalog for tables or views related to 'orders' or 'history', inspect their columns and foreign keys, and report the likely location with supporting evidence.

Install

  1. Download the skill as a ZIP:

    https://mp-staging.autogineer.com/agent-skills/timescale/schema-exploration/download

  2. Unzip it into the cross-client skills folder of your project:

    mkdir -p .agents/skills && unzip schema-exploration.zip -d .agents/skills

Documentation by tigerdata

Explore a PostgreSQL database

Start with the user's question, not a full-database audit unless that is the explicit ask. Use the database connection already available to you; if none is available, ask for access or work from provided schema files. Stay within the database and schemas the user has authorized. Catalog metadata can reveal sensitive names or source code: do not export it unnecessarily.

Workflow

  1. Establish the current database, server version, role, and scope. Use overview for a small inventory. Choose relevant schemas; do not assume public contains the application.

  2. Pick the most promising object(s) and read only the matching drill-down reference: tables, partitioning/inheritance, foreign tables, views, routines, triggers, security/RLS, or extensions. Follow cross-references only when the question requires them.

  3. Corroborate meaning with comments, definitions, keys, and known dependencies. Names are clues, not proof of business meaning. If data-derived values would help, use data-derived values only after obtaining approval for the specific columns and access method. Ask the user when semantics remain ambiguous.

  4. If the user wants SQL, follow query authoring: establish join keys and grain, then validate a vetted read-only query with plain EXPLAIN where authorized. Do not mistake a valid plan for proof of business semantics.

  5. Stop once you can answer. Report the specific schema-qualified objects and evidence, distinguish observations from inferences, and state limitations (permissions, stale statistics, missing dependencies, unknown application logic).

Example finding

Illustrative only; report facts verified in the target database:

  • Observed: sales.orders has a primary key on order_id and a foreign key from account_id to sales.accounts.account_id.

  • Inferred: sales.orders likely records one row per order; the keys support this, but do not establish what the business calls an “order.”

  • Unresolved: The catalog does not show whether canceled orders remain in this table. Confirm with the application owner before assuming they do.

Safety and execution

  • Prefer structural catalog queries; pg_stats is data-derived and requires approval too. Do not change schema, data, roles, or session-wide settings without authorization. Never call discovered functions or procedures, refresh materialized views, or run EXPLAIN ANALYZE on unknown queries. A function marked STABLE or IMMUTABLE is not a safety guarantee.

  • If your client supports transactions, use a read-only transaction and a reasonable statement timeout for exploration. Metadata queries are not a license to run full-table counts or unrestricted scans. Ask before sampling data; sample only when necessary, with explicit limits and a clear understanding of table size and access controls.

  • All reference queries are plain PostgreSQL SQL. Bind $1, $2, etc. as values using your client's API. psql users can follow the optional psql adapter. Never interpolate an untrusted object name as raw SQL; placeholders cannot replace SQL identifiers in data queries.

  • Queries target PostgreSQL 18. Each version-sensitive section notes alternatives for older majors where applicable. Check server_version_num first. If a query fails due to permissions or version differences, report the limitation instead of guessing.

Files

references/partitioning-and-inheritance.md

Partitioning and inheritance: traverse a table's tree

Use the four structural flags in tables to choose a traversal. Each query uses $1 = schema name, $2 = relation name and returns the selected relation at depth 0 plus its ancestors or descendants. A query for the wrong kind of relation returns no rows. Names are always schema-qualified in the result. Depth is the number of parent–child edges from the selected relation; a subpartition can be both a partition and a partitioned parent. Queries target PostgreSQL 18 and also run on PostgreSQL 14–17.

Contents

  • Partition ancestors (up)

  • Partition descendants (down)

  • Traditional inheritance ancestors (up)

  • Traditional inheritance descendants (down)

Partition ancestors (up)

Run when is_partition is true. Each ancestor row identifies the child through which it was reached. The selected partition's bounds and each ancestor's partition key are shown. pg_inherits edges are followed only when the child is a declarative partition.

with recursive walk as (
  select
    c.oid as relid
  , null::oid as via_child_oid
  , 0 as depth
  , array[c.oid] as path
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  where n.nspname = $1
    and c.relname = $2
    and c.relispartition
  union all
  select
    parent.oid
  , child.oid
  , w.depth + 1
  , w.path || parent.oid
  from walk w
  join pg_catalog.pg_class child on child.oid = w.relid and child.relispartition
  join pg_catalog.pg_inherits i on i.inhrelid = child.oid
  join pg_catalog.pg_class parent on parent.oid = i.inhparent
  where not parent.oid = any(w.path)
)
select
  w.depth
, n.nspname as schema_name
, c.relname as relation_name
, cn.nspname as via_child_schema
, child.relname as via_child_name
, pg_catalog.pg_get_partkeydef(c.oid) as partition_key
, pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_bounds
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class child on child.oid = w.via_child_oid
left join pg_catalog.pg_namespace cn on cn.oid = child.relnamespace
order by w.depth
;

Partition descendants (down)

Run when is_partitioned_parent is true. PostgreSQL's pg_partition_tree() already walks the declarative partition tree, so no custom recursive CTE is needed here. Each result row identifies its direct parent, whether it is a leaf, and its partition bounds. The root has no parent and depth (level) 0. This function has been available since PostgreSQL 12.

with root as materialized (
  select c.oid
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  where n.nspname = $1
    and c.relname = $2
    and c.relkind = 'p'
)
select
  t.level as depth
, n.nspname as schema_name
, c.relname as relation_name
, pn.nspname as parent_schema
, parent.relname as parent_name
, t.isleaf as is_leaf
, pg_catalog.pg_get_partkeydef(c.oid) as partition_key
, pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_bounds
from root
cross join lateral pg_catalog.pg_partition_tree(root.oid) t
join pg_catalog.pg_class c on c.oid = t.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class parent on parent.oid = t.parentrelid
left join pg_catalog.pg_namespace pn on pn.oid = parent.relnamespace
order by t.level, n.nspname, c.relname
;

Traditional inheritance ancestors (up)

Run when has_inheritance_parent is true. Follow only edges whose child is not a declarative partition. Each ancestor row identifies the child through which it was reached. Multiple inheritance can produce more than one path to a relation; paths are not collapsed.

with recursive walk as (
  select
    c.oid as relid
  , null::oid as via_child_oid
  , 0 as depth
  , array[c.oid] as path
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  where n.nspname = $1
    and c.relname = $2
    and not c.relispartition
    and exists (select 1 from pg_catalog.pg_inherits i where i.inhrelid = c.oid)
  union all
  select
    parent.oid
  , child.oid
  , w.depth + 1
  , w.path || parent.oid
  from walk w
  join pg_catalog.pg_class child on child.oid = w.relid and not child.relispartition
  join pg_catalog.pg_inherits i on i.inhrelid = child.oid
  join pg_catalog.pg_class parent on parent.oid = i.inhparent
  where not parent.oid = any(w.path)
)
select
  w.depth
, n.nspname as schema_name
, c.relname as relation_name
, cn.nspname as via_child_schema
, child.relname as via_child_name
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class child on child.oid = w.via_child_oid
left join pg_catalog.pg_namespace cn on cn.oid = child.relnamespace
order by w.depth, n.nspname, c.relname
;

Traditional inheritance descendants (down)

Run when has_inheritance_children is true. Each descendant row names its direct parent; the starting relation has no parent in this traversal. Multiple inheritance can yield more than one path to a descendant.

with recursive walk as (
  select
    c.oid as relid
  , null::oid as parent_oid
  , 0 as depth
  , array[c.oid] as path
  from pg_catalog.pg_class c
  join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  where n.nspname = $1
    and c.relname = $2
    and exists (
      select 1
      from pg_catalog.pg_inherits i
      join pg_catalog.pg_class child on child.oid = i.inhrelid
      where i.inhparent = c.oid
        and not child.relispartition
    )
  union all
  select
    child.oid
  , w.relid
  , w.depth + 1
  , w.path || child.oid
  from walk w
  join pg_catalog.pg_inherits i on i.inhparent = w.relid
  join pg_catalog.pg_class child on child.oid = i.inhrelid and not child.relispartition
  where not child.oid = any(w.path)
)
select
  w.depth
, n.nspname as schema_name
, c.relname as relation_name
, pn.nspname as parent_schema
, parent.relname as parent_name
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class parent on parent.oid = w.parent_oid
left join pg_catalog.pg_namespace pn on pn.oid = parent.relnamespace
order by w.depth, n.nspname, c.relname
;

These trees describe catalog structure, not whether a query scans every child or where every row is stored. Ordinary inheritance and declarative partitioning have different query and constraint semantics. TimescaleDB hypertables require extension-specific interpretation; see extensions.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Created by
tigerdata
Publisher
Timescale (TigerData) (Official)
Distributed by
Timescale (TigerData)
Source
timescale/pg-aiguide · skills/schema-exploration · commit c39008a
Licence
Apache License 2.0 (redistribution allowed)
  • LICENSE (added by AutoGineer from LICENSE)
  • NOTICE (added by AutoGineer from NOTICE)
  • ATTRIBUTION.md (written by AutoGineer)
Synced

AutoGineer is not affiliated with or endorsed by tigerdata or Timescale (TigerData).

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download schema-exploration.zip