postgresql-table-design

Overview of postgresql-table-design skill

What this skill does

The postgresql-table-design skill helps an agent design or review PostgreSQL schemas with PostgreSQL-specific rules, not generic SQL advice. It focuses on the decisions that usually affect correctness and long-term performance most: primary keys, normalization, nullability, defaults, data types, foreign keys, indexing, and PostgreSQL edge cases.

Who should use it

This skill is best for developers, data engineers, and database engineers who need a practical postgresql-table-design guide for creating new tables, reviewing an existing schema, or translating product requirements into a PostgreSQL-first design.

Real job to be done

Most users do not need a theory lesson. They need an agent to turn a rough domain model into tables, constraints, and indexes that are idiomatic for PostgreSQL and safe to ship. That is where this skill is useful: it reduces avoidable mistakes like missing foreign-key indexes, overusing UUID, picking weak data types, or denormalizing too early.

What makes it different from a generic schema prompt

The main differentiator is its PostgreSQL-specific stance. The source guidance explicitly pushes:

• normalization first, denormalization only after measured need
• BIGINT GENERATED ALWAYS AS IDENTITY as the default PK choice
• TIMESTAMPTZ, NUMERIC, TEXT, and BIGINT as preferred defaults in common cases
• explicit indexing of foreign keys
• awareness of PostgreSQL behaviors such as unquoted lowercase identifiers and UNIQUE with nullable columns

Best-fit and misfit cases

Use this skill when you want a practical postgresql-table-design for Database Engineering workflow. It is a strong fit for OLTP-style application schemas, relational data modeling, and schema review. It is less suited when your main problem is ETL orchestration, analytics modeling, or operational DBA work unrelated to table design.

How to Use postgresql-table-design skill

Install context for postgresql-table-design install

This skill lives in wshobson/agents under plugins/database-design/skills/postgresql. If your agent platform supports GitHub-hosted skills, add the repository and select the postgresql skill. A common install pattern is:

npx skills add https://github.com/wshobson/agents --skill postgresql

If your environment uses a different skill loader, point it at:
https://github.com/wshobson/agents/tree/main/plugins/database-design/skills/postgresql

Read this file first

Start with:

• SKILL.md

This skill is unusually concentrated in one file. There are no visible helper scripts or reference folders in this skill path, so most of the useful guidance is in the main document. That is good for fast adoption: low repo-reading overhead, but you should expect fewer worked examples than in heavier skills.

What input the skill needs

The skill works best when you give it concrete schema intent, not just “design my database.” Strong inputs usually include:

• entities and relationships
• expected write and read patterns
• uniqueness rules
• nullable vs required fields
• money, time, and identifier semantics
• scale expectations
• update/delete behavior for related rows

Without this, the agent can still draft a schema, but the indexing and constraint choices will be generic.

Turn a rough goal into a strong prompt

Weak prompt:

• “Design PostgreSQL tables for an ecommerce app.”

Stronger prompt:

• “Use the postgresql-table-design skill to design PostgreSQL tables for an ecommerce app. Entities: users, products, carts, orders, order_items, payments. Expected queries: list orders by user and date, fetch open cart by user, filter products by category and price. Money must be exact. All event times should preserve timezone. Users may have multiple addresses. Orders are immutable after payment except status fields. Recommend PKs, FKs, nullability, defaults, unique constraints, and indexes, then explain any denormalization you reject.”

That stronger prompt gives the skill enough signal to apply its core rules well.

What the skill is likely to optimize for

Based on the source guidance, the skill will generally steer toward:

• normalized schemas first
• surrogate integer PKs unless there is a real reason for UUID
• explicit FK indexes
• exact numeric handling for money
• timezone-aware timestamps
• conservative, maintainable defaults over clever one-off designs

If your system needs the opposite tradeoff, say so explicitly.

Practical workflow for postgresql-table-design usage

A good postgresql-table-design usage flow is:

1. Describe the domain and main queries.
2. Ask for an initial schema with tables, columns, constraints, and indexes.
3. Review the output specifically for PK choice, FK indexing, nullability, and data types.
4. Ask for DDL.
5. Ask for a second pass focused on query patterns and migration risks.

This sequence gets more value from the skill than asking for SQL immediately.

Repository cues that matter in practice

The source file includes explicit “gotchas,” which is important because many generic prompts miss PostgreSQL-specific behavior. In particular, pay attention when the agent discusses:

• lowercase identifier behavior for unquoted names
• UNIQUE plus nullable columns
• precision and length overflow behavior
• foreign keys not being auto-indexed

These are the kinds of details that change real production outcomes.

Strong prompt ingredients that improve output quality

Include these if relevant:

• “Use snake_case identifiers only.”
• “Prefer BIGINT GENERATED ALWAYS AS IDENTITY unless you justify UUID.”
• “Index all FK columns unless there is a clear exception.”
• “Use TIMESTAMPTZ for event times.”
• “Use NUMERIC for monetary values.”
• “Call out where NOT NULL and DEFAULT should be applied.”

These align with the skill’s native guidance, so the agent is more likely to produce a clean first draft.

Constraints and tradeoffs to decide early

Before relying on the output, decide:

• Do you need opaque/global IDs or simple local PKs?
• Are you optimizing for write simplicity or read speed?
• Is denormalization allowed at all?
• Do nullable fields mean “unknown,” “not applicable,” or “not yet collected”?
• Do you need exact decimal math?

These choices directly affect whether the postgresql-table-design guide produces a schema that fits your system rather than just a correct-looking one.

When to ask for review instead of design

This skill is also useful as a reviewer. If you already have DDL, ask the agent to:

• identify missing FK indexes
• flag poor type choices
• check nullability and defaults
• challenge premature denormalization
• note PostgreSQL-specific correctness risks

For adoption decisions, this is often the fastest way to validate the skill in your own codebase.

postgresql-table-design skill FAQ

Is this skill only for new schemas?

No. It is useful for both greenfield design and schema review. In mature systems, the highest-value use is often finding missing indexes, weak constraints, and questionable type choices.

How is this better than an ordinary prompt?

A normal prompt may produce plausible SQL, but often misses PostgreSQL-specific behaviors and defaults. The postgresql-table-design skill gives the agent a clearer stance on normalization, PK selection, FK indexing, money and time types, and common PostgreSQL gotchas.

Is postgresql-table-design good for beginners?

Yes, if you already understand basic relational concepts. The skill is practical and opinionated enough to guide beginners away from common mistakes, but it does not replace learning how constraints, indexes, and query patterns interact.

Does it generate migration-ready SQL by itself?

Not necessarily. The source is more design-oriented than migration-framework-specific. It can help draft DDL, but you may still need to adapt the output for tools like Prisma, Drizzle, Rails migrations, Django migrations, or plain SQL workflows.

When should I not use this skill?

Skip it if your problem is mainly:

• warehouse/star-schema modeling
• ORM-specific code generation details
• database administration unrelated to table design
• partitioning-heavy or extension-heavy architecture where broader PostgreSQL operations matter more than table modeling

Does it cover advanced PostgreSQL behavior?

It covers some advanced, practical details, but its strongest value is disciplined table design rather than exhaustive PostgreSQL internals. Think of it as a focused postgresql-table-design guide, not a full database architecture framework.

How to Improve postgresql-table-design skill

Give query patterns, not just entity names

The fastest way to improve output is to provide the reads and writes that matter. “Users and orders” is weak. “Fetch recent orders by user, join order items, filter unpaid orders by status and created_at” is much better because it drives index and constraint decisions.

Be explicit about identifier strategy

The skill has a clear default preference for integer identity keys. If your system needs UUID for public exposure, distributed creation, or merge safety, say that up front. Otherwise the agent may correctly push back toward BIGINT, which may not match your architecture.

Tell the agent where exactness matters

If you omit money and time semantics, weaker outputs often follow. Say:

• “All prices require exact decimal arithmetic.”
• “Audit and event timestamps must preserve timezone.”
  That nudges the skill toward NUMERIC and TIMESTAMPTZ, which is one of its strongest practical defaults.

Ask for constraint reasoning, not just a schema dump

A better prompt is:

• “Design the schema, then justify each PK, FK, unique constraint, NOT NULL, and index.”

This exposes whether the agent is actually using the skill’s design logic or merely outputting generic table definitions.

Watch for common failure modes

Even with this skill, review for:

• missing FK indexes
• overuse of nullable columns
• text fields where more precise types are better
• unjustified denormalization
• UUID chosen by habit rather than need
• uniqueness rules that break down with nullable columns

These are the places where first-pass output most often needs correction.

Iterate after the first draft

After you get an initial design, ask follow-ups such as:

• “What query paths are still under-indexed?”
• “Which columns should be NOT NULL but are not?”
• “Where would this schema create update anomalies?”
• “Which denormalizations should wait until measured performance data exists?”

That second pass usually raises the quality more than asking for a bigger first prompt.

Improve postgresql-table-design for Database Engineering teams

For team use, standardize the prompt inputs. Require every request to include:

• domain entities
• cardinality and lifecycle rules
• critical queries
• retention needs
• ID policy
• exactness requirements for money and time
• expected row growth

This makes the postgresql-table-design skill much more consistent across reviewers and projects.

Use the skill to review existing DDL before adoption

If you are deciding whether to adopt the skill, test it on one real schema review. Give it a production-like table set and ask it to flag PostgreSQL-specific issues only. If it catches design problems your generic prompts missed, that is the clearest sign the skill is worth installing.