Database Design

Conventions

PostgreSQL Schema

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- Trigger updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

Relations

-- One-to-Many
CREATE TABLE posts (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL
);

-- Many-to-Many
CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id),
    role_id UUID REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

Indexes

Optimization

-- Analyze a query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Missing indexes
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

See also

MongoDB publishes their own official agent skills at mongodb/agent-skills (102★, last commit 2026-05-04). The repo covers schema design heuristics, indexing strategies, query patterns, and operational safeguards specific to MongoDB.

For PostgreSQL, the dev-supabase skill already points to supabase/agent-skills which includes a supabase-postgres-best-practices skill (30 rules across 8 categories) — useful for any Postgres project, not just Supabase-managed.

When working on a project using one of these databases, install the relevant vendor skill alongside this one. This skill captures the stack-neutral conventions (naming, soft-delete patterns, updated_at triggers, partitioning strategy); the vendor skills capture the canonical operational patterns specific to MongoDB or Postgres.

Vendor-neutrality: MongoDB Inc. is independent. Supabase is independent. Both pass the vendor-neutrality filter.

Install command and full list of validated vendor skills: docs/recipes/recommended-vendor-skills.md. Audit pilot trace: specs/marketplace-audit/ops-skills-pilot-2026-05-06.md.