-------------------------------------------------------------------------- -- COMMENT ON template for MCP / LLM-readable schemas -- -- Guiding rule: comment what the schema cannot say for itself. -- -- An MCP client already sees names, data types, nullability, defaults, -- foreign keys, and CHECK constraints. Restating those wastes context and -- teaches the model nothing. Comment the things that are invisible from -- the catalog: grain, units, encodings, NULL semantics, which of several -- similar objects to use, and which columns are dead. -- -- Format: first line is a plain sentence (this is what shows, truncated, -- in \d+ and in most MCP schema dumps). Optional tagged lines follow, -- drawn from this fixed vocabulary so they stay greppable: -- -- Grain: what exactly one row represents -- Scope: what is included / deliberately excluded -- Units: unit and scale of a numeric value -- Values: meaning of opaque codes not enforced by a type or CHECK -- NULL: what NULL means here (absent? unknown? not-applicable?) -- Join: relationships the FKs don't declare -- Use: when to prefer this object over a similar one -- Caveat: traps that produce wrong-but-plausible queries -- Deprecated: do not use, and what to use instead -- -- Budget: an MCP client may inject every comment in the schema into the -- context window at once. Keep table comments under ~5 lines and column -- comments to 1-2. Terse and true beats complete. -------------------------------------------------------------------------- -------------------------------------------------------------------------- -- 1. WORKED EXAMPLE -------------------------------------------------------------------------- -- TABLE: lead with grain. Ambiguous grain is the single largest cause of -- silently wrong aggregates in generated SQL. COMMENT ON TABLE shipment_leg IS $$A single point-to-point movement within a shipment; multi-stop shipments have several. Grain: one row per (shipment_id, leg_seq). Scope: completed legs only; legs still in transit are in shipment_leg_active. Volume: ~40M rows, ~1M/month. Unfiltered scans are slow; filter on departed_at. Join: shipment_id -> shipment.id, carrier_id -> carrier.id.$$; -- Disambiguation between near-identical tables. Worth more than any other -- single comment when the schema has grown organically. COMMENT ON TABLE shipment_leg_active IS $$Legs currently in transit. Rows move to shipment_leg on arrival. Use: for "where is X now" questions. For historical or volume analysis use shipment_leg. Caveat: a leg exists in exactly one of the two tables, never both. Answering "all legs ever" requires a UNION ALL of the two.$$; -- COLUMN: units and scale. A model that assumes dollars will be off by 100x -- and the result will look entirely reasonable. COMMENT ON COLUMN shipment_leg.freight_charge IS $$Amount billed to the shipper, in USD cents. Divide by 100 to report dollars.$$; COMMENT ON COLUMN shipment_leg.distance IS $$Road distance actually driven, in kilometres (not the great-circle distance).$$; -- COLUMN: which timestamp means what. Schemas routinely carry three or four -- and the names rarely distinguish event time from record time. COMMENT ON COLUMN shipment_leg.departed_at IS $$When the vehicle actually left, in UTC. This is the event time; use it for time-series and date-range questions.$$; COMMENT ON COLUMN shipment_leg.created_at IS $$When our system recorded the row, in UTC. Lags departed_at by minutes to days after a connectivity outage. Do not use for date-range questions.$$; -- COLUMN: opaque codes with no enum type or CHECK to reveal them. COMMENT ON COLUMN shipment_leg.status IS $$Values: D=delivered, R=refused at destination, X=cancelled before departure, P=partially delivered. Only D and P represent revenue.$$; -- COLUMN: NULL semantics. "Unknown" and "not applicable" and "none yet" all -- look identical in the catalog and lead to different correct queries. COMMENT ON COLUMN shipment_leg.pod_signed_at IS $$When proof of delivery was signed, in UTC. NULL: no signature was collected, which is normal for contactless drops. It does not mean the leg is undelivered; check status for that.$$; -- COLUMN: the natural key humans actually use, versus the surrogate PK. COMMENT ON COLUMN shipment.reference IS $$Customer-facing shipment reference, e.g. 'SHP-2026-0041823'. This is what users mean when they quote "the shipment number"; shipment.id is internal only. Caveat: unique per customer, not globally. Filter with customer_id.$$; -- COLUMN: free text. Tells the model not to GROUP BY it or trust exact matches. COMMENT ON COLUMN shipment_leg.delay_reason IS $$Free text typed by the driver, unvalidated and inconsistently capitalised. Caveat: not a controlled vocabulary. Use ILIKE for matching; do not GROUP BY.$$; -- COLUMN: deprecation. A single line here removes a whole class of wrong query. COMMENT ON COLUMN shipment_leg.zone_code IS $$Deprecated: frozen since 2024-03 and no longer maintained. Use destination_region_id instead.$$; -- VIEW: say when to prefer it, or the model will keep rebuilding it by hand. COMMENT ON VIEW shipment_daily_revenue IS $$Revenue per customer per calendar day, already excluding cancelled and refused legs. Use: for any revenue-over-time question. Prefer this over aggregating shipment_leg directly, which requires the status filter to be correct. Caveat: bucketed in UTC, not customer-local time.$$; -- FUNCTION: signature is visible; behaviour and cost are not. COMMENT ON FUNCTION current_transit_estimate(bigint) IS $$Estimated remaining transit time for an in-flight shipment. Caveat: calls an external routing service; slow and unsuitable for use in a query over many rows. Call for a single shipment at a time.$$; -------------------------------------------------------------------------- -- 2. BLANK SKELETON -------------------------------------------------------------------------- COMMENT ON TABLE IS $$ Grain: one row per . Scope: . Join: .$$; COMMENT ON COLUMN
. IS $$$$; -------------------------------------------------------------------------- -- 3. ANTI-PATTERNS -------------------------------------------------------------------------- -- -- 'The customer id' on customer_id -- restates the name -- 'Foreign key to customer' on customer_id -- already in the catalog -- 'varchar(50), not null' on anything -- already in the catalog -- 'Added by Dave, ticket 4471, see the design doc on Confluence' -- -- history, not semantics; -- belongs in the migration -- A three-paragraph narrative -- burns context, buries -- the one useful sentence -- -- Comments are dumped by pg_dump and restored with the schema, so they belong -- in version-controlled migrations alongside the DDL, not applied by hand to -- production. Applying COMMENT ON requires ownership of the object. -------------------------------------------------------------------------- -- 4. COVERAGE: find what is still undocumented -------------------------------------------------------------------------- -- Tables and views with no comment SELECT c.relnamespace::regnamespace AS schema, c.relname, c.relkind FROM pg_class c WHERE c.relkind IN ('r','p','v','m','f') AND c.relnamespace::regnamespace::text NOT IN ('pg_catalog','information_schema') AND obj_description(c.oid, 'pg_class') IS NULL ORDER BY 1, 2; -- Columns with no comment, worst-covered tables first SELECT c.relnamespace::regnamespace AS schema, c.relname, a.attname FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped WHERE c.relkind IN ('r','p','v','m','f') AND c.relnamespace::regnamespace::text NOT IN ('pg_catalog','information_schema') AND col_description(c.oid, a.attnum) IS NULL ORDER BY 1, 2, a.attnum; -- Read comments back the way an MCP client sees them -- \d+ shipment_leg -- SELECT obj_description('shipment_leg'::regclass, 'pg_class');