aboio-labsaboio-labs/aboio-skills/skills/pg-gleam/SKILL.md

pg-gleam

Skill3 starsChanged 9 months agoMITView on GitHub

Postgres performance optimization and best practices for a Gleam + Squirrel/Parrot + POG + Cigogne stack. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.

In this repository7 other files it gives its agents.
- skills/pg-gleam/CLAUDE.mdCLAUDE.md
- api-gleamSkill
- frontend-testingSkill
- gleam-backendSkill
- gleamSkill
- lustreSkill
- tddSkill

The file

We split this skill into the rules it gives an agent

4 rules. About 0 are common, 1 is distinctive, and 3 are very specific to this project.

≈0
Common · estimate
Many files like this have a rule of this kind.
≈1
Distinctive · estimate
This author's own choices. Worth a look.
3
Very specific
Tied to their project's names, files or tools. Probably skip.

Common
Distinctive
Very specific

Very specific:
Follow references/token-efficiency.md rules: When reviewing or writing database code:
line 84
Very specific:
Use git diff --name-only to scope reviews to changed .sql files only
line 87
Distinctive:
Read targeted line ranges around Grep matches, not entire migrations
line 88
Very specific:
Read individual rule files in references/ for detailed explanations and SQL examples.
line 92

≈ “Common” is an estimate: at least 1 in 5 of the 350,495 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.

Files

SKILL.md

Postgres Best Practices (Gleam Stack)

Performance optimization and schema design guide for Postgres, aligned with our Gleam backend stack.

Stack Context

Layer	Tool	Role
Language	Gleam	Backend application code
SQL codegen	Squirrel / Parrot	Generates type-safe Gleam from `.sql` files
DB driver	POG	Connection pooling, binary protocol
Migrations	Cigogne	Schema migrations
Extensions	pg_uuidv7	UUIDv7 primary key generation

Our Conventions

Primary keys: uuid via uuid_generate_v7() (pg_uuidv7 extension)
Timestamps: timestamp (not timestamptz); timezone stored in core.tenant.timezone
Money: bigint with scale factor 10,000 (maps to Gleam Int, exact arithmetic)
Enums: CREATE TYPE with prefixed values (os_pending, ps_paid) for Gleam uniqueness
Constraints: Named with pk_, fk_, uq_, chk_, idx_ prefixes
Schemas: core, tenant, shared, extensions separation
Tables: Singular names (order not orders)
Soft deletes: deleted_at timestamp + deleted_by uuid + partial indexes
Audit fields: created_at, updated_at, created_by, updated_by via triggers
RLS: Session variables via core.current_tenant_id() / core.current_user_id()

Gleam Type Mapping (via Squirrel/POG)

Postgres Type	Gleam Type	Notes
`uuid`	`String`	UUIDv7 for PKs
`text`	`String`	Prefer over `varchar(n)`
`boolean`	`Bool`
`integer`	`Int`
`bigint`	`Int`	Use for money (exact)
`numeric`	`Float`	Lossy! Avoid for money
`timestamp`	`pog.Timestamp`	No `timestamptz` support
`date`	`pog.Date`
`jsonb`	`String` (raw JSON)	Decode in Gleam
`bytea`	`BitArray`
`enum`	Generated variant type	See `schema-enums.md`

When to Apply

Reference these guidelines when:

Writing SQL queries or designing schemas
Implementing indexes or query optimization
Reviewing database performance issues
Configuring POG connection pooling
Working with Row-Level Security (RLS)
Creating Squirrel or Parrot .sql query files
Writing Cigogne migrations

Rule Categories by Priority

Priority	Category	Impact	Prefix
1	Query Performance	CRITICAL	`query-`
2	Connection Management	CRITICAL	`conn-`
3	Security & RLS	CRITICAL	`security-`
4	Schema Design	HIGH	`schema-`
5	Concurrency & Locking	MEDIUM-HIGH	`lock-`
6	Data Access Patterns	MEDIUM	`data-`
7	Monitoring & Diagnostics	LOW-MEDIUM	`monitor-`
8	Advanced Features	LOW	`advanced-`

Token Efficiency

Follow references/token-efficiency.md rules. When reviewing or writing database code:

Grep for specific SQL patterns (e.g., pog.transaction, set_config, FORCE ROW LEVEL) instead of reading all migration files
Use git diff --name-only to scope reviews to changed .sql files only
Read targeted line ranges around Grep matches, not entire migrations

How to Use

Read individual rule files in references/ for detailed explanations and SQL examples. Each rule file contains:

Brief explanation of why it matters
Incorrect SQL example with explanation
Correct SQL example with explanation
Optional EXPLAIN output or metrics
Gleam/POG-specific notes where applicable

Reference routing — by task

Task	References to read
Designing a new table	`schema-primary-keys.md` + `schema-data-types.md` + `schema-naming-conventions.md`
Adding Enum columns	`schema-enums.md`
Adding Audit / Soft Deletes	`schema-audit-fields.md` + `schema-soft-deletes.md`
Implementing RLS (Tenant Isolation)	`security-rls-basics.md` + `security-session-variables.md`
RLS on Child Tables	`security-rls-child-tables.md` + `security-rls-performance.md`
Optimizing slow queries	`query-missing-indexes.md` + `monitor-explain-analyze.md`
Creating indexes	`schema-foreign-key-indexes.md` + `query-composite-indexes.md`
Writing batch inserts	`data-batch-inserts.md`
Implementing pagination	`data-pagination.md`
Handling concurrent updates	`lock-short-transactions.md` + `lock-skip-locked.md`
Configuring POG connections	`conn-pooling.md` + `conn-limits.md`

Reference routing — by topic

Topic	Reference
Postgres Data Types to Gleam mapping	`schema-data-types.md`
UUIDv7 generation	`schema-primary-keys.md`
Row Level Security (RLS) performance	`security-rls-performance.md`
Partial & Covering Indexes	`query-partial-indexes.md` + `query-covering-indexes.md`
JSONB Indexing	`advanced-jsonb-indexing.md`
Full Text Search	`advanced-full-text-search.md`
Deadlock prevention	`lock-deadlock-prevention.md`
N+1 Query prevention	`data-n-plus-one.md`
Upserts (`ON CONFLICT`)	`data-upsert.md`
Connection Pooling & Idle timeouts	`conn-pooling.md` + `conn-idle-timeout.md`
Prepared Statements	`conn-prepared-statements.md`

References

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

It worked
Partly
It didn't

What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

Check your setup, free