skills.rest
PremModhaOfficial
PremModhaOfficialCOMMUNITY
Explore all skills by this author →
GitHub

pg-table-template

Generate PostgreSQL 18 table templates with tenant-scoped indexing strategies.

1|Updated Apr 17, 2026
Search Tags:#indexing#postgresql#partitioning#soft-delete#multitenancy#optimistic-locking#ddl-template
One-click install
npx skills add https://github.com/PremModhaOfficial/motadata-ai-pipeline --skill pg-table-template
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: pg-table-template
Source: https://github.com/PremModhaOfficial/motadata-ai-pipeline/tree/main/.claude/skills/pg-table-template
Command: npx skills add https://github.com/PremModhaOfficial/motadata-ai-pipeline --skill pg-table-template

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

PostgreSQL 18 standard table DDL template and indexing strategy for a schema-per-tenant SaaS platform. It provides a consistent base for creating domain tables, ensures mandatory auditing columns, soft delete, optimistic locking, and explicit schema-prefix DDL to support per-tenant migrations without cross-tenant FK constraints.

Core Features & Use Cases

  • Standard Table Template: base CREATE TABLE pattern with :schema placeholders, including common columns like id, created_at, updated_at, is_deleted, and version, plus guidance to avoid foreign keys enforced at the DB level.
  • Indexing Strategy: partial indexes (WHERE is_deleted = false), CONCURRENTLY index creation, B-tree indexes for typical filters, GIN indexes on JSONB, and support for generated full-text search vectors.
  • Partitioning & Auditing: guidance to partition large tables and maintain audit trails with predictable partition schemes.
  • Soft Delete & Concurrency: standardized soft-delete workflow and optimistic locking to prevent lost updates.

Quick Start

Use the Standard Table Template to create a new tenant table by replacing :schema with your tenant schema and providing a real table name.

Frequently Asked Questions about pg-table-template

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I standardize multi-tenant PostgreSQL table schemas for consistent auditing?▼

Standardize multi-tenant PostgreSQL schemas by applying a DDL template with mandatory auditing columns like created_at, updated_at, is_deleted, and version. This ensures consistent tenant-scoped tables and predictable migrations across schema-per-tenant databases without cross-tenant foreign key constraints.

What is the best way to implement soft delete and optimistic locking in a schema-per-tenant database?▼

Implement soft delete and optimistic locking by including standardized is_deleted and version columns in your table DDL. Use partial indexes where is_deleted = false and increment the version column to prevent lost updates during concurrent tenant transactions.

How do I create PostgreSQL indexes for multi-tenant tables without locking the database?▼

Create PostgreSQL indexes without locking by using the CONCURRENTLY index creation method. The template specifies B-tree indexes for typical filters, GIN indexes for JSONB, partial indexes excluding deleted rows, and generated full-text search vectors for optimized tenant queries.

When should I partition large tables in a multi-tenant PostgreSQL database?▼

Partition large tables in a multi-tenant PostgreSQL database when you need to maintain audit trails and improve query performance on high-volume tenant data. The template provides guidance on predictable partition schemes to manage domain tables efficiently.

Why avoid database-level foreign keys in a schema-per-tenant SaaS platform?▼

Avoid database-level foreign keys in a schema-per-tenant SaaS platform to support independent per-tenant migrations. Using explicit schema-prefix DDL with placeholders prevents cross-tenant FK constraints and allows isolated schema updates across tenants.

Related Skills

Explore More Software Engineering Skills→
dadbodgeoffdadbodgeoff
783

multi-tenancy

Implement multi-tenant architecture with tenant isolation and automated query scoping.

Community
Advanced
musher-devmusher-dev

governing-credential-schemas

Design, review, and audit multi-tenant API credential database schemas.

Official
Advanced
syncupsuitesyncupsuite
1

Neon Multi-Tenant Skill

Implement multi-tenant Neon PostgreSQL architectures with Drizzle ORM and Cloudflare Hyperdrive.

Official
Advanced
aleonsaaleonsa

multi-tenant-saas

Implement multi-tenant SaaS patterns with PostgreSQL RLS and SQLAlchemy.

Community
Advanced