AgentSkillsCN

pg-doc-schema-review

评审并修正包含PostgreSQL表DDL与API示例的PRD/BP/技术设计Markdown文档。遵循以下规范：禁止使用自增主键，避免外键关联，杜绝基于JOIN的设计，使用UNIQUE关键字定义唯一约束（而非CREATE UNIQUE INDEX），为表与列添加详尽的COMMENT注释，同时确保实体表与聚合根表中包含create_time与update_time字段。

SKILL.md
--- frontmatter
name: pg-doc-schema-review
description: Review and fix PRD/BP/tech design Markdown that includes PostgreSQL table DDL and API examples. Use to enforce these conventions: no auto-increment primary keys, no foreign keys, avoid join-based designs, unique constraints written with UNIQUE (not CREATE UNIQUE INDEX), detailed COMMENTs for tables/columns, and entity/aggregate-root tables include create_time and update_time.

pg-doc-schema-review

Follow this workflow when reviewing or editing PRD/BP Markdown that contains database schema / SQL examples.

Rules to enforce

IDs / primary keys

  • •Prefer domain-scoped globally unique string IDs as primary keys (PostgreSQL TEXT), with prefix = entity name or abbreviation (e.g., plan_..., sup_...), unique within the domain.
  • •Do not use auto-increment / identity as the primary key (avoid SERIAL, GENERATED ... AS IDENTITY, AUTO_INCREMENT).
  • •If an auto-increment numeric ID is explicitly required (rare), use BIGINT and do not expose it as the entity’s external identifier; keep the external identifier as the prefixed string ID.

Schema design

  • •Do not define foreign keys (FOREIGN KEY, REFERENCES).
  • •Avoid designs that require join queries for core read paths (“不要连表查询”): prefer denormalized fields, JSONB snapshots, or read-optimized tables.

Indexing

  • •Add indexes for the intended query paths.
  • •Unique requirements should be expressed using UNIQUE (e.g., email TEXT NOT NULL UNIQUE, or CONSTRAINT ux_xxx UNIQUE (...)), not CREATE UNIQUE INDEX.

Comments

  • •Every table must have COMMENT ON TABLE ....
  • •Every column must have COMMENT ON COLUMN ....

Timestamps

  • •Entity tables / aggregate root tables must include:
    • •create_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
    • •update_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
  • •If the doc claims DB triggers, state it explicitly; otherwise, assume application-level update of update_time.

Review checklist (what to look for)

  • •ID type mismatches between API examples and schema (*_id as string vs int).
  • •Use of CREATE UNIQUE INDEX (should be UNIQUE).
  • •Any FOREIGN KEY / REFERENCES / join-table patterns.
  • •Missing create_time/update_time on core tables.
  • •Missing COMMENT ON TABLE / COMMENT ON COLUMN.
  • •SQL dialect mismatch with PostgreSQL (e.g., MySQL-only syntax).

Tooling (optional)

Run the bundled linter to quickly flag common violations:

  • •python .project/ai/dev/DBA/skills/pg-doc-schema-review/scripts/lint_md_schema.py docs/path/or/file.md

It scans Markdown ```sql blocks and reports violations and missing comment/index sections.