Menu
Database schema reference for the hello-world app's PostgreSQL instance. Use when writing queries, creating migrations, or debugging data issues.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
| name | db-schema |
| description | Database schema reference for the hello-world app's PostgreSQL instance. Use when writing queries, creating migrations, or debugging data issues. |
| user-invokable | false |
Engine: PostgreSQL 17
Connection: postgres:5432/devdb (from within Docker network)
User: devuser / changeme
Single-row counter table for the hit counter.
| Column | Type | Notes |
|---|---|---|
| id | integer | PK, always 1 (CHECK constraint) |
| hits | bigint | Current hit count, default 0 |
Auto-created on app startup via the FastAPI lifespan handler.
-- Read current count
SELECT hits FROM hello_counter WHERE id = 1;
-- Increment
UPDATE hello_counter SET hits = hits + 1 WHERE id = 1 RETURNING hits;
-- Reset
UPDATE hello_counter SET hits = 0 WHERE id = 1;
When adding new tables, follow these patterns:
CREATE TABLE IF NOT EXISTS items (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
\di+ tablename# Parameterized queries — ALWAYS use $N placeholders
row = await pool.fetchrow("SELECT hits FROM hello_counter WHERE id = $1", 1)
# NEVER interpolate values into SQL
# BAD: f"SELECT * FROM items WHERE id = {user_input}"
# Fetch multiple rows
rows = await pool.fetch("SELECT * FROM items ORDER BY created_at DESC LIMIT $1", limit)
# Execute without return
await pool.execute("UPDATE hello_counter SET hits = 0 WHERE id = 1")
# Fetch with RETURNING
row = await pool.fetchrow(
"UPDATE hello_counter SET hits = hits + 1 WHERE id = 1 RETURNING hits"
)
Create a new database migration for PostgreSQL. Covers file naming, SQL patterns, running, and updating dependent code.
Baseline code quality enforcer — naming conventions, import hygiene, type safety, and style rules for a FastAPI + asyncpg + PostgreSQL Python backend.
Docker patterns for this project — DooD (Docker-outside-of-Docker), compose conventions, container lifecycle, and dev-box workflow.
Step-by-step checklist for adding a new API endpoint to a FastAPI + asyncpg app. Covers schema, query, route, and verification.
Project file tree and layout conventions. Use when navigating the codebase, finding where files belong, or understanding the project layout.
Test-driven development workflow for FastAPI + asyncpg apps. Write tests first, then implement, then refactor.