Skip to content

henriqueatila/golang-gin-best-practices / skills/golang-gin-psql-dba

golang-gin-psql-dba

Flagged

PostgreSQL DBA for Go Gin APIs. Use when designing schemas, analyzing migrations, choosing indexes, optimizing queries, or selecting extensions (pgvector, PostGIS, TimescaleDB).

FileKindSize
SKILL.mdskill md9.9 KB
metadata.jsonconfig668 B
references/backup-and-recovery-managed-and-schedules.mdmarkdown4.3 KB
references/backup-and-recovery-strategies.mdmarkdown4.8 KB
references/backup-and-recovery-wal-and-validation.mdmarkdown2.7 KB
references/extensions-toolkit-crypto-and-uuid.mdmarkdown4.1 KB
references/extensions-toolkit-partman-and-trgm.mdmarkdown4.5 KB
references/extensions-toolkit-pgaudit-and-stats.mdmarkdown4.3 KB
references/extensions-toolkit-setup-and-cron.mdmarkdown3.8 KB
references/index-strategy-maintenance-and-explain.mdmarkdown4.4 KB
references/index-strategy-types-and-variants.mdmarkdown3.8 KB
references/migration-impact-backfill-and-lock-timeout.mdmarkdown3.5 KB
references/migration-impact-lock-timeout.mdmarkdown4.6 KB
references/migration-impact-locks-and-operations.mdmarkdown6.4 KB
references/paradedb-advanced-search-and-hybrid.mdmarkdown4.7 KB
references/paradedb-go-integration-and-tips.mdmarkdown4.9 KB
references/paradedb-setup-and-basic-search.mdmarkdown4.5 KB
references/pgvector-capacity-and-performance.mdmarkdown4.3 KB
references/pgvector-queries-and-go-integration.mdmarkdown4.4 KB
references/pgvector-setup-and-schema.mdmarkdown4.9 KB
references/postgis-go-integration-and-performance.mdmarkdown3.8 KB
references/postgis-setup-types-and-indexes.mdmarkdown4.3 KB
references/query-performance-pool-and-replicas.mdmarkdown4.6 KB
references/query-performance-stats-and-vacuum.mdmarkdown4.2 KB
references/replication-and-ha-clusters.mdmarkdown3.4 KB
references/replication-and-ha-go-readwrite.mdmarkdown4.0 KB
references/replication-and-ha-go-resilience.mdmarkdown4.8 KB
references/replication-and-ha-lag-monitoring.mdmarkdown3.3 KB
references/replication-and-ha-logical.mdmarkdown2.2 KB
references/replication-and-ha-managed-services.mdmarkdown2.0 KB
references/replication-and-ha-overview.mdmarkdown3.1 KB
references/replication-and-ha-streaming-setup.mdmarkdown3.6 KB
references/row-level-security-go-integration-and-pitfalls.mdmarkdown5.5 KB
references/row-level-security-setup-and-policies.mdmarkdown5.8 KB
references/schema-design-complete-example.mdmarkdown6.9 KB
references/schema-design-constraints-and-normalization.mdmarkdown4.2 KB
references/schema-design-naming-and-types.mdmarkdown3.9 KB
references/schema-design-soft-delete-and-tenancy.mdmarkdown5.1 KB
references/timescaledb-aggregates-compression-retention.mdmarkdown5.7 KB
references/timescaledb-go-integration.mdmarkdown3.5 KB
references/timescaledb-setup-and-hypertables.mdmarkdown4.8 KB

references/schema-design-complete-example.md

View on GitHub
# Schema Design — Complete E-Commerce Schema Example

Full DDL for tenants, users, products, orders, order_items with all conventions applied: naming, constraints, indexes, triggers, soft delete, and audit logging.

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

-- Tenants
CREATE TABLE tenants (
    id         UUID        NOT NULL,
    name       TEXT        NOT NULL,
    slug       TEXT        NOT NULL,
    active     BOOLEAN     NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT tenants_pk_id       PRIMARY KEY (id),
    CONSTRAINT tenants_ck_name_len CHECK (char_length(name) BETWEEN 1 AND 200),
    CONSTRAINT tenants_ck_slug_fmt CHECK (slug ~ '^[a-z0-9-]+$')
);
CREATE UNIQUE INDEX tenants_uq_slug_active ON tenants (slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_active            ON tenants (id)   WHERE deleted_at IS NULL;
CREATE TRIGGER tenants_update_updated_at
    BEFORE UPDATE ON tenants FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Users
CREATE TABLE users (
    id            UUID        NOT NULL,
    tenant_id     UUID        NOT NULL,
    email         TEXT        NOT NULL,
    name          TEXT        NOT NULL,
    password_hash TEXT        NOT NULL,
    role          TEXT        NOT NULL DEFAULT 'user',
    verified      BOOLEAN     NOT NULL DEFAULT false,
    preferences   JSONB       NOT NULL DEFAULT '{}',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at    TIMESTAMPTZ,
    CONSTRAINT users_pk_id        PRIMARY KEY (id),
    CONSTRAINT users_fk_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE RESTRICT,
    CONSTRAINT users_ck_role      CHECK (role IN ('admin', 'user', 'viewer')),
    CONSTRAINT users_ck_name_len  CHECK (char_length(name) BETWEEN 1 AND 200),
    CONSTRAINT users_ck_email_fmt CHECK (char_length(email) BETWEEN 3 AND 255)
);
CREATE UNIQUE INDEX users_uq_tenant_email_active ON users (tenant_id, email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_tenant_id                 ON users (tenant_id)        WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at                ON users (created_at DESC);
CREATE TRIGGER users_update_updated_at
    BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Products
CREATE TABLE products (
    id          UUID          NOT NULL,
    tenant_id   UUID          NOT NULL,
    name        TEXT          NOT NULL,
    slug        TEXT          NOT NULL,
    description TEXT,
    price       NUMERIC(19,4) NOT NULL DEFAULT 0,
    stock       INT           NOT NULL DEFAULT 0,
    tags        TEXT[]        NOT NULL DEFAULT '{}',
    metadata    JSONB         NOT NULL DEFAULT '{}',
    active      BOOLEAN       NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ,
    CONSTRAINT products_pk_id             PRIMARY KEY (id),
    CONSTRAINT products_fk_tenant_id      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE RESTRICT,
    CONSTRAINT products_ck_price_positive CHECK (price >= 0),
    CONSTRAINT products_ck_stock_positive CHECK (stock >= 0),
    CONSTRAINT products_ck_name_len       CHECK (char_length(name) BETWEEN 1 AND 300),
    CONSTRAINT products_ck_slug_fmt       CHECK (slug ~ '^[a-z0-9-]+$')
);
CREATE UNIQUE INDEX products_uq_tenant_slug_active ON products (tenant_id, slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_tenant_active            ON products (tenant_id)       WHERE deleted_at IS NULL;
CREATE INDEX idx_products_tags                     ON products USING gin (tags);
CREATE INDEX idx_products_metadata                 ON products USING gin (metadata jsonb_path_ops);
CREATE TRIGGER products_update_updated_at
    BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Orders
CREATE TABLE orders (
    id         UUID          NOT NULL,
    tenant_id  UUID          NOT NULL,
    user_id    UUID          NOT NULL,
    status     TEXT          NOT NULL DEFAULT 'pending',
    total      NUMERIC(19,4) NOT NULL DEFAULT 0,
    coupon_id  UUID,
    notes      TEXT,
    created_at TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ   NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT orders_pk_id        PRIMARY KEY (id),
    CONSTRAINT orders_fk_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE RESTRICT,
    CONSTRAINT orders_fk_user_id   FOREIGN KEY (user_id)   REFERENCES users   (id) ON DELETE RESTRICT,
    CONSTRAINT orders_fk_coupon_id FOREIGN KEY (coupon_id) REFERENCES coupons (id) ON DELETE SET NULL,
    CONSTRAINT orders_ck_status    CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')),
    CONSTRAINT orders_ck_total_pos CHECK (total >= 0)
);
CREATE INDEX idx_orders_tenant_user   ON orders (tenant_id, user_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status)  WHERE deleted_at IS NULL;
CREATE INDEX idx_orders_created_at    ON orders (created_at DESC);
CREATE TRIGGER orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION record_audit_log();
CREATE TRIGGER orders_update_updated_at
    BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Order Items
CREATE TABLE order_items (
    id         UUID          NOT NULL,
    order_id   UUID          NOT NULL,
    product_id UUID          NOT NULL,
    quantity   INT           NOT NULL,
    unit_price NUMERIC(19,4) NOT NULL,  -- snapshotted at purchase time
    CONSTRAINT order_items_pk_id           PRIMARY KEY (id),
    CONSTRAINT order_items_fk_order_id     FOREIGN KEY (order_id)   REFERENCES orders   (id) ON DELETE CASCADE,
    CONSTRAINT order_items_fk_product_id   FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
    CONSTRAINT order_items_ck_qty_positive CHECK (quantity > 0),
    CONSTRAINT order_items_ck_price_pos    CHECK (unit_price >= 0)
);
CREATE INDEX idx_order_items_order_id   ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
```

**What this demonstrates:**
- All naming conventions applied consistently
- `TIMESTAMPTZ` everywhere, never `TIMESTAMP`
- `NUMERIC(19,4)` for all money columns
- Partial unique indexes for soft-delete-aware uniqueness
- `ON DELETE` clause on every foreign key — intent explicit
- Snapshotted `unit_price` in `order_items` — intentional denormalization
- `JSONB` with GIN index for flexible metadata and preferences
- Triggers wired for `updated_at` and audit logging
- `tags TEXT[]` with GIN index for array containment queries
- RLS-ready `tenant_id` column on every tenant-scoped table
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall