Prompt Details
Model
Chat - (gpt-6-luna)
Token size
394
Example input
[Application Domain & Business Logic]: B2B Feature Voting & Product Roadmap SaaS (customer feedback boards, feature posts, user upvotes, roadmap stages, and threaded comments).
[Tenant Model & Data Isolation]: Multi-tenant shared database and shared schema isolated by tenant_id on all tables, enforced strictly via PostgreSQL Row-Level Security (RLS).
[High-Frequency Query Patterns]: 1. Fetching top-voted posts for a specific board filtered by status with pagination (LIMIT/OFFSET or cursor). 2. Checking if the authenticated user has upvoted a list of 20 posts. 3. Real-time post search by title and body keywords.
[Target Scale & Hardware Constraints]: 2,500 active company workspaces, 1.2M total posts and votes, running on AWS RDS PostgreSQL db.t4g.small (2 vCPU, 2GB RAM) using PgBouncer for transaction pooling.
Example output
# PostgreSQL Schema & Index Architecture Blueprint
## 1. Multi-Tenant Relational Schema Model
* **Tenants & Workspaces:** `tenants (id UUID PRIMARY KEY, name TEXT, slug TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT now())`.
* **Feedback Boards:** `boards (id UUID PRIMARY KEY, tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE, name TEXT, is_public BOOLEAN DEFAULT true)`.
* **Feedback Posts:** `posts (id UUID PRIMARY KEY, tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE, board_id UUID REFERENCES boards(id) ON DELETE CASCADE, title TEXT NOT NULL, body TEXT, status TEXT NOT NULL DEFAULT 'open', vote_count INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ DEFAULT now())`.
* **Post Votes:** `votes (id UUID PRIMARY KEY, tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE, post_id UUID REFERENCES posts(id) ON DELETE CASCADE, user_id UUID NOT NULL, created_at TIMESTAMPTZ DEFAULT now(), UNIQUE(post_id, user_id))`.
---
## 2. Row-Level Security (RLS) Isolation Framework
Enable RLS on all tenant-facing tables:
* `ALTER TABLE posts ENABLE ROW LEVEL SECURITY;`
* `CREATE POLICY tenant_isolation_posts ON posts USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID);`
* Session configuration at connection checkout: `SET LOCAL app.current_tenant_id = '018f...';`
---
## 3. Targeted Indexing & Query Optimization Matrix
| Target Table | Indexed Column(s) | Index Method | Targeted Query Pattern |
| :--- | :--- | :--- | :--- |
| `posts` | `(tenant_id, board_id, status, vote_count DESC)` | B-Tree (Composite) | Paginated top-voted roadmap queries |
| `posts` | `to_tsvector('english', title \|\| ' ' \|\| coalesce(body, ''))` | GIN (Full-Text) | Keyword feedback search |
| `votes` | `(tenant_id, user_id, post_id)` | B-Tree (Covering) | Batch verification of user vote states |
| `posts` | `(tenant_id, created_at DESC)` WHERE `status = 'open'` | Partial B-Tree | Recent open feedback dashboard triage |
---
## 4. Scaling, Connection & Maintenance Plan
* **Memory & Buffer Pool:** Set `shared_buffers = 512MB` and `work_mem = 8MB` to prevent disk spills on 2GB RAM constraints.
* **Autovacuum Tuning:** Adjust `autovacuum_vacuum_scale_factor = 0.05` on `votes` to reclaim dead tuples rapidly from frequent vote toggling.
* **Connection Pooling:** Configure PgBouncer in transaction mode with a pool size of 20 server connections to eliminate memory overhead from idle clients.
Get this prompt + 9 more with SelectFrom $14/mo · Cancel anytimeFrom $14/mo · Cancel anytime
View Select
Instant access
Commercial use
Money‑backBy purchasing this prompt, you agree to our terms of service
GPT-6-LUNA
Design high-performance PostgreSQL database schemas and optimized index architectures tailored for modern micro-SaaS applications. Built for solo founders, technical leads, and full-stack developers seeking scalable multi-tenant foundations without runtime database bottlenecks. Generates production-ready DDL schemas, row-level security isolation policies, composite index matrices, and maintenance blueprints.
...more
Added 5 hours ago
