Two files, one repository

davila7/claude-code-templates ships 2 formats across 5 indexed files. The question worth asking is whether the second one says anything the first does not.

CompareCLAUDE.md ↔ AGENTS.md

A · cli-tool/components/skills/database/supabase-postgres-best-practices/AGENTS.md · 5896 words B · CLAUDE.md · 3389 words

What each file covers, counted
Dimension	Shared	Only in A	Only in B	Overlap
Sections	0	43	60	0%
Commands	0	0	34	0%
Section tags	6	1	11	33%

What each file covers

Sections

0 shared · 43 only in A · 60 only in B

− Postgres Best Practices
− Abstract
− Table of Contents
− 1. Query Performance
− 1.1 Add Indexes on WHERE and JOIN Columns
− 1.2 Choose the Right Index Type for Your Data
− 1.3 Create Composite Indexes for Multi-Column Queries
− 1.4 Use Covering Indexes to Avoid Table Lookups
− 1.5 Use Partial Indexes for Filtered Queries
− 2. Connection Management
− 2.1 Configure Idle Connection Timeouts
− pgbouncer.ini
− 2.2 Set Appropriate Connection Limits
− 2.3 Use Connection Pooling for All Applications
− 2.4 Use Prepared Statements Correctly with Pooling
− 3. Security & RLS
− 3.1 Apply Principle of Least Privilege
− 3.2 Enable Row Level Security for Multi-Tenant Data
− 3.3 Optimize RLS Policies for Performance
− 4. Schema Design
− 4.1 Choose Appropriate Data Types
− 4.2 Index Foreign Key Columns
− 4.3 Partition Large Tables for Better Performance
− 4.4 Select Optimal Primary Key Strategy
− 4.5 Use Lowercase Identifiers for Compatibility
− 5. Concurrency & Locking
− 5.1 Keep Transactions Short to Reduce Lock Contention
− 5.2 Prevent Deadlocks with Consistent Lock Ordering
− 5.3 Use Advisory Locks for Application-Level Locking
− 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing
− 6. Data Access Patterns
− 6.1 Batch INSERT Statements for Bulk Data
− 6.2 Eliminate N+1 Queries with Batch Loading
− 6.3 Use Cursor-Based Pagination Instead of OFFSET
− 6.4 Use UPSERT for Insert-or-Update Operations
− 7. Monitoring & Diagnostics
− 7.1 Enable pg_stat_statements for Query Analysis
− 7.2 Maintain Table Statistics with VACUUM and ANALYZE
− 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries
− 8. Advanced Features
− 8.1 Index JSONB Columns for Efficient Querying
− 8.2 Use tsvector for Full-Text Search
− References
+ CLAUDE.md
+ Project Overview
+ Essential Commands
+ Development
+ Component catalog
+ Dashboard + API (Astro on Cloudflare Pages)
+ Security Guidelines
+ ⛔ CRITICAL: NEVER Hardcode Secrets or IDs
+ Component System
+ Component Types
+ Installation Patterns
+ Single component
+ Batch installation
+ Interactive mode
+ Component Development
+ After creating a new agent
+ Before committing hook changes
+ For PR reviews with multiple components
+ Publishing Workflow
+ 1. Update component catalog
+ 2. Run tests
+ 3. Check current npm version and align local version
+ Edit package.json version to be one patch above the registry version
+ 4. Commit version bump and push
+ 5. Publish to npm (requires granular access token with "Bypass 2FA" enabled)
+ 6. Tag the release
+ 7. Deploy website (dashboard on Cloudflare Pages)
+ Automatic on push to main (GitHub Actions). Manual: from dashboard/ run `npm run deploy`
+ API Architecture
+ Critical Endpoints
+ Shared API Libraries
+ Emergency Rollback
+ List recent Pages deployments
+ Roll back to a previous deployment
+ Cloudflare Workers
+ crons
+ docs-monitor (DECOMMISSIONED 2026-07)
+ pulse (Weekly KPI Report)
+ Manual trigger
+ Test single source
+ Dry run (no Telegram)
+ newsletter (Weekly Community Components Email)
+ Preview content without sending (repeat to see the copy rotate)
+ Real send: creates + sends a Broadcast to the segment in RESEND_SEGMENT_ID
+ Error Tracking (Sentry)
+ Dashboard (www.aitmpl.com)
+ Architecture
+ Featured Pages (`/featured/[slug]`)
+ Cloudflare Pages Project Setup
+ Deployment
+ Environment Variables (Cloudflare)
+ Clerk
+ Data
+ GitHub OAuth
+ Supabase (download tracking)
+ Neon Database
+ Discord
+ Known Issues & Solutions
+ Local Development
+ Data Files

Commands

0 shared · 0 only in A · 34 only in B

+ npm install
+ npm test
+ npm version patch|minor|major
+ npm publish
+ python scripts/generate_components_json.py
+ npm run deploy
+ npx claude-code-templates@latest --agent frontend-developer
+ npx claude-code-templates@latest --command setup-testing
+ npx claude-code-templates@latest --hook automation/simple-notifications
+ npx claude-code-templates@latest --loop engineering/docs-sweep-loop
+ npx claude-code-templates@latest --agent security-auditor --command security-audit --setting read-only-mode
+ npx claude-code-templates@latest
+ npm view claude-code-templates version
+ git add package.json && git commit -m "chore: Bump version to X.Y.Z"
+ git push origin main
+ npm config set //registry.npmjs.org/:_authToken=YOUR_GRANULAR_TOKEN
+ npm config delete //registry.npmjs.org/:_authToken
+ git tag vX.Y.Z && git push origin vX.Y.Z
+ npx wrangler pages deployment list --project-name=aitmpl-dashboard
+ npx wrangler pages deployment rollback <deployment-id> --project-name=aitmpl-dashboard
+ npm run dev
+ npx wrangler deploy
+ npm run deploy:dashboard
+ npx astro dev --port 4321
+ npm run test:watch
+ npm run test:coverage
+ git diff
+ pip install git+https://github.com/NVIDIA/skillspector.git@main
+ npm config delete
+ node:fs
+ node:path
+ node:url
+ node:stream
+ npx wrangler pages deployment tail --project-name=aitmpl-dashboard

Section tags

6 shared · 1 only in A · 11 only in B

− performance
+ setup
+ build
+ test
+ architecture
+ testing-strategy
+ git-pr
+ dependencies
+ api
+ ui
+ deployment
+ docs
code-style
types
security
database
do-not
agent-behaviour

Line diff

+401 added−1318 removed173 unchanged11.6% identical

davila7/claude-code-templates · cli-tool/components/skills/database/supabase-postgres-best-practices/AGENTS.md

@@ −1 @@

1# Postgres Best Practices

3**Version 1.0.0**

4Supabase

5January 2026

7> This document is optimized for AI agents and LLMs. Rules are prioritized by performance impact.

9---

11## Abstract

13Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.

15---

17## Table of Contents

191. [Query Performance](#query-performance) - **CRITICAL**

20 - 1.1 [Add Indexes on WHERE and JOIN Columns](#11-add-indexes-on-where-and-join-columns)

21 - 1.2 [Choose the Right Index Type for Your Data](#12-choose-the-right-index-type-for-your-data)

22 - 1.3 [Create Composite Indexes for Multi-Column Queries](#13-create-composite-indexes-for-multi-column-queries)

23 - 1.4 [Use Covering Indexes to Avoid Table Lookups](#14-use-covering-indexes-to-avoid-table-lookups)

24 - 1.5 [Use Partial Indexes for Filtered Queries](#15-use-partial-indexes-for-filtered-queries)

262. [Connection Management](#connection-management) - **CRITICAL**

27 - 2.1 [Configure Idle Connection Timeouts](#21-configure-idle-connection-timeouts)

28 - 2.2 [Set Appropriate Connection Limits](#22-set-appropriate-connection-limits)

29 - 2.3 [Use Connection Pooling for All Applications](#23-use-connection-pooling-for-all-applications)

30 - 2.4 [Use Prepared Statements Correctly with Pooling](#24-use-prepared-statements-correctly-with-pooling)

323. [Security & RLS](#security-rls) - **CRITICAL**

33 - 3.1 [Apply Principle of Least Privilege](#31-apply-principle-of-least-privilege)

34 - 3.2 [Enable Row Level Security for Multi-Tenant Data](#32-enable-row-level-security-for-multi-tenant-data)

35 - 3.3 [Optimize RLS Policies for Performance](#33-optimize-rls-policies-for-performance)

374. [Schema Design](#schema-design) - **HIGH**

38 - 4.1 [Choose Appropriate Data Types](#41-choose-appropriate-data-types)

39 - 4.2 [Index Foreign Key Columns](#42-index-foreign-key-columns)

40 - 4.3 [Partition Large Tables for Better Performance](#43-partition-large-tables-for-better-performance)

41 - 4.4 [Select Optimal Primary Key Strategy](#44-select-optimal-primary-key-strategy)

42 - 4.5 [Use Lowercase Identifiers for Compatibility](#45-use-lowercase-identifiers-for-compatibility)

445. [Concurrency & Locking](#concurrency-locking) - **MEDIUM-HIGH**

45 - 5.1 [Keep Transactions Short to Reduce Lock Contention](#51-keep-transactions-short-to-reduce-lock-contention)

46 - 5.2 [Prevent Deadlocks with Consistent Lock Ordering](#52-prevent-deadlocks-with-consistent-lock-ordering)

47 - 5.3 [Use Advisory Locks for Application-Level Locking](#53-use-advisory-locks-for-application-level-locking)

48 - 5.4 [Use SKIP LOCKED for Non-Blocking Queue Processing](#54-use-skip-locked-for-non-blocking-queue-processing)

506. [Data Access Patterns](#data-access-patterns) - **MEDIUM**

51 - 6.1 [Batch INSERT Statements for Bulk Data](#61-batch-insert-statements-for-bulk-data)

52 - 6.2 [Eliminate N+1 Queries with Batch Loading](#62-eliminate-n1-queries-with-batch-loading)

53 - 6.3 [Use Cursor-Based Pagination Instead of OFFSET](#63-use-cursor-based-pagination-instead-of-offset)

54 - 6.4 [Use UPSERT for Insert-or-Update Operations](#64-use-upsert-for-insert-or-update-operations)

567. [Monitoring & Diagnostics](#monitoring-diagnostics) - **LOW-MEDIUM**

57 - 7.1 [Enable pg_stat_statements for Query Analysis](#71-enable-pgstatstatements-for-query-analysis)

58 - 7.2 [Maintain Table Statistics with VACUUM and ANALYZE](#72-maintain-table-statistics-with-vacuum-and-analyze)

59 - 7.3 [Use EXPLAIN ANALYZE to Diagnose Slow Queries](#73-use-explain-analyze-to-diagnose-slow-queries)

618. [Advanced Features](#advanced-features) - **LOW**

62 - 8.1 [Index JSONB Columns for Efficient Querying](#81-index-jsonb-columns-for-efficient-querying)

63 - 8.2 [Use tsvector for Full-Text Search](#82-use-tsvector-for-full-text-search)

65---

67## 1. Query Performance

69**Impact: CRITICAL**

71Slow queries, missing indexes, inefficient query plans. The most common source of Postgres performance issues.

73### 1.1 Add Indexes on WHERE and JOIN Columns

75**Impact: CRITICAL (100-1000x faster queries on large tables)**

77Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.

79**Incorrect (sequential scan on large table):**

81```sql

82-- No index on customer_id causes full table scan

83select * from orders where customer_id = 123;

85-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)

86```

88**Correct (index scan):**

90```sql

91-- Create index on frequently filtered column

92create index orders_customer_id_idx on orders (customer_id);

94select * from orders where customer_id = 123;

96-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)

97-- Index the referencing column

98create index orders_customer_id_idx on orders (customer_id);

100select c.name, o.total

101from customers c

102join orders o on o.customer_id = c.id;

103```

104

105For JOIN columns, always index the foreign key side:

106

107Reference: https://supabase.com/docs/guides/database/query-optimization

108

109---

110

111### 1.2 Choose the Right Index Type for Your Data

112

113**Impact: HIGH (10-100x improvement with correct index type)**

114

115Different index types excel at different query patterns. The default B-tree isn't always optimal.

116

117**Incorrect (B-tree for JSONB containment):**

118

119```sql

120-- B-tree cannot optimize containment operators

121create index products_attrs_idx on products (attributes);

122select * from products where attributes @> '{"color": "red"}';

123-- Full table scan - B-tree doesn't support @> operator

124```

125

126**Correct (GIN for JSONB):**

127

128```sql

129-- GIN supports @>, ?, ?&, ?| operators

130create index products_attrs_idx on products using gin (attributes);

131select * from products where attributes @> '{"color": "red"}';

132-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL

133create index users_created_idx on users (created_at);

134

135-- GIN: arrays, JSONB, full-text search

136create index posts_tags_idx on posts using gin (tags);

137

138-- BRIN: large time-series tables (10-100x smaller)

139create index events_time_idx on events using brin (created_at);

140

141-- Hash: equality-only (slightly faster than B-tree for =)

142create index sessions_token_idx on sessions using hash (token);

143```

144

145Index type guide:

146

147Reference: https://www.postgresql.org/docs/current/indexes-types.html

148

149---

150

151### 1.3 Create Composite Indexes for Multi-Column Queries

152

153**Impact: HIGH (5-10x faster multi-column queries)**

154

155When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.

156

157**Incorrect (separate indexes require bitmap scan):**

158

159```sql

160-- Two separate indexes

161create index orders_status_idx on orders (status);

162create index orders_created_idx on orders (created_at);

163

164-- Query must combine both indexes (slower)

165select * from orders where status = 'pending' and created_at > '2024-01-01';

166```

167

168**Correct (composite index):**

169

170```sql

171-- Single composite index (leftmost column first for equality checks)

172create index orders_status_created_idx on orders (status, created_at);

173

174-- Query uses one efficient index scan

175select * from orders where status = 'pending' and created_at > '2024-01-01';

176-- Good: status (=) before created_at (>)

177create index idx on orders (status, created_at);

178

179-- Works for: WHERE status = 'pending'

180-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

181-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)

182```

183

184**Column order matters** - place equality columns first, range columns last:

185

186Reference: https://www.postgresql.org/docs/current/indexes-multicolumn.html

187

188---

189

190### 1.4 Use Covering Indexes to Avoid Table Lookups

191

192**Impact: MEDIUM-HIGH (2-5x faster queries by eliminating heap fetches)**

193

194Covering indexes include all columns needed by a query, enabling index-only scans that skip the table entirely.

195

196**Incorrect (index scan + heap fetch):**

197

198```sql

199create index users_email_idx on users (email);

200

201-- Must fetch name and created_at from table heap

202select email, name, created_at from users where email = 'user@example.com';

203```

204

205**Correct (index-only scan with INCLUDE):**

206

207```sql

208-- Include non-searchable columns in the index

209create index users_email_idx on users (email) include (name, created_at);

210

211-- All columns served from index, no table access needed

212select email, name, created_at from users where email = 'user@example.com';

213-- Searching by status, but also need customer_id and total

214create index orders_status_idx on orders (status) include (customer_id, total);

215

216select status, customer_id, total from orders where status = 'shipped';

217```

218

219Use INCLUDE for columns you SELECT but don't filter on:

220

221Reference: https://www.postgresql.org/docs/current/indexes-index-only-scans.html

222

223---

224

225### 1.5 Use Partial Indexes for Filtered Queries

226

227**Impact: HIGH (5-20x smaller indexes, faster writes and queries)**

228

229Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition.

230

231**Incorrect (full index includes irrelevant rows):**

232

233```sql

234-- Index includes all rows, even soft-deleted ones

235create index users_email_idx on users (email);

236

237-- Query always filters active users

238select * from users where email = 'user@example.com' and deleted_at is null;

239```

240

241**Correct (partial index matches query filter):**

242

243```sql

244-- Index only includes active users

245create index users_active_email_idx on users (email)

246where deleted_at is null;

247

248-- Query uses the smaller, faster index

249select * from users where email = 'user@example.com' and deleted_at is null;

250-- Only pending orders (status rarely changes once completed)

251create index orders_pending_idx on orders (created_at)

252where status = 'pending';

253

254-- Only non-null values

255create index products_sku_idx on products (sku)

256where sku is not null;

257```

258

259Common use cases for partial indexes:

260

261Reference: https://www.postgresql.org/docs/current/indexes-partial.html

262

263---

264

265## 2. Connection Management

266

267**Impact: CRITICAL**

268

269Connection pooling, limits, and serverless strategies. Critical for applications with high concurrency or serverless deployments.

270

271### 2.1 Configure Idle Connection Timeouts

272

273**Impact: HIGH (Reclaim 30-50% of connection slots from idle clients)**

274

275Idle connections waste resources. Configure timeouts to automatically reclaim them.

276

277**Incorrect (connections held indefinitely):**

278

279```sql

280-- No timeout configured

281show idle_in_transaction_session_timeout; -- 0 (disabled)

282

283-- Connections stay open forever, even when idle

284select pid, state, state_change, query

285from pg_stat_activity

286where state = 'idle in transaction';

287-- Shows transactions idle for hours, holding locks

288```

289

290**Correct (automatic cleanup of idle connections):**

291

292```ini

293-- Terminate connections idle in transaction after 30 seconds

294alter system set idle_in_transaction_session_timeout = '30s';

295

296-- Terminate completely idle connections after 10 minutes

297alter system set idle_session_timeout = '10min';

298

299-- Reload configuration

300select pg_reload_conf();

301# pgbouncer.ini

302server_idle_timeout = 60

303client_idle_timeout = 300

304```

305

306For pooled connections, configure at the pooler level:

307

308Reference: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT

309

310---

311

312### 2.2 Set Appropriate Connection Limits

313

314**Impact: CRITICAL (Prevent database crashes and memory exhaustion)**

315

316Too many connections exhaust memory and degrade performance. Set limits based on available resources.

317

318**Incorrect (unlimited or excessive connections):**

319

320```sql

321-- Default max_connections = 100, but often increased blindly

322show max_connections; -- 500 (way too high for 4GB RAM)

323

324-- Each connection uses 1-3MB RAM

325-- 500 connections * 2MB = 1GB just for connections!

326-- Out of memory errors under load

327```

328

329**Correct (calculate based on resources):**

330

331```sql

332-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved

333-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max

334-- But practically, 100-200 is better for query performance

335

336-- Recommended settings for 4GB RAM

337alter system set max_connections = 100;

338

339-- Also set work_mem appropriately

340-- work_mem * max_connections should not exceed 25% of RAM

341alter system set work_mem = '8MB'; -- 8MB * 100 = 800MB max

342select count(*), state from pg_stat_activity group by state;

343```

344

345Monitor connection usage:

346

347Reference: https://supabase.com/docs/guides/platform/performance#connection-management

348

349---

350

351### 2.3 Use Connection Pooling for All Applications

352

353**Impact: CRITICAL (Handle 10-100x more concurrent users)**

354

355Postgres connections are expensive (1-3MB RAM each). Without pooling, applications exhaust connections under load.

356

357**Incorrect (new connection per request):**

358

359```sql

360-- Each request creates a new connection

361-- Application code: db.connect() per request

362-- Result: 500 concurrent users = 500 connections = crashed database

363

364-- Check current connections

365select count(*) from pg_stat_activity; -- 487 connections!

366```

367

368**Correct (connection pooling):**

369

370```sql

371-- Use a pooler like PgBouncer between app and database

372-- Application connects to pooler, pooler reuses a small pool to Postgres

373

374-- Configure pool_size based on: (CPU cores * 2) + spindle_count

375-- Example for 4 cores: pool_size = 10

376

377-- Result: 500 concurrent users share 10 actual connections

378select count(*) from pg_stat_activity; -- 10 connections

379```

380

381Pool modes:

382- **Transaction mode**: connection returned after each transaction (best for most apps)

383- **Session mode**: connection held for entire session (needed for prepared statements, temp tables)

384

385Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pooler

386

387---

388

389### 2.4 Use Prepared Statements Correctly with Pooling

390

391**Impact: HIGH (Avoid prepared statement conflicts in pooled environments)**

392

393Prepared statements are tied to individual database connections. In transaction-mode pooling, connections are shared, causing conflicts.

394

395**Incorrect (named prepared statements with transaction pooling):**

396

397```sql

398-- Named prepared statement

399prepare get_user as select * from users where id = $1;

400

401-- In transaction mode pooling, next request may get different connection

402execute get_user(123);

403-- ERROR: prepared statement "get_user" does not exist

404```

405

406**Correct (use unnamed statements or session mode):**

407

408```sql

409-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)

410-- The query is prepared and executed in a single protocol message

411

412-- Option 2: Deallocate after use in transaction mode

413prepare get_user as select * from users where id = $1;

414execute get_user(123);

415deallocate get_user;

416

417-- Option 3: Use session mode pooling (port 5432 vs 6543)

418-- Connection is held for entire session, prepared statements persist

419-- Many drivers use prepared statements by default

420-- Node.js pg: { prepare: false } to disable

421-- JDBC: prepareThreshold=0 to disable

422```

423

424Check your driver settings:

425

426Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pool-modes

427

428---

429

430## 3. Security & RLS

431

432**Impact: CRITICAL**

433

434Row-Level Security policies, privilege management, and authentication patterns.

435

436### 3.1 Apply Principle of Least Privilege

437

438**Impact: MEDIUM (Reduced attack surface, better audit trail)**

439

440Grant only the minimum permissions required. Never use superuser for application queries.

441

442**Incorrect (overly broad permissions):**

443

444```sql

445-- Application uses superuser connection

446-- Or grants ALL to application role

447grant all privileges on all tables in schema public to app_user;

448grant all privileges on all sequences in schema public to app_user;

449

450-- Any SQL injection becomes catastrophic

451-- drop table users; cascades to everything

452```

453

454**Correct (minimal, specific grants):**

455

456```sql

457-- Create role with no default privileges

458create role app_readonly nologin;

459

460-- Grant only SELECT on specific tables

461grant usage on schema public to app_readonly;

462grant select on public.products, public.categories to app_readonly;

463

464-- Create role for writes with limited scope

465create role app_writer nologin;

466grant usage on schema public to app_writer;

467grant select, insert, update on public.orders to app_writer;

468grant usage on sequence orders_id_seq to app_writer;

469-- No DELETE permission

470

471-- Login role inherits from these

472create role app_user login password 'xxx';

473grant app_writer to app_user;

474-- Revoke default public access

475revoke all on schema public from public;

476revoke all on all tables in schema public from public;

477```

478

479Revoke public defaults:

480

481Reference: https://supabase.com/blog/postgres-roles-and-privileges

482

483---

484

485### 3.2 Enable Row Level Security for Multi-Tenant Data

486

487**Impact: CRITICAL (Database-enforced tenant isolation, prevent data leaks)**

488

489Row Level Security (RLS) enforces data access at the database level, ensuring users only see their own data.

490

491**Incorrect (application-level filtering only):**

492

493```sql

494-- Relying only on application to filter

495select * from orders where user_id = $current_user_id;

496

497-- Bug or bypass means all data is exposed!

498select * from orders; -- Returns ALL orders

499```

500

501**Correct (database-enforced RLS):**

502

503```sql

504-- Enable RLS on the table

505alter table orders enable row level security;

506

507-- Create policy for users to see only their orders

508create policy orders_user_policy on orders

509 for all

510 using (user_id = current_setting('app.current_user_id')::bigint);

511

512-- Force RLS even for table owners

513alter table orders force row level security;

514

515-- Set user context and query

516set app.current_user_id = '123';

517select * from orders; -- Only returns orders for user 123

518create policy orders_user_policy on orders

519 for all

520 to authenticated

521 using (user_id = auth.uid());

522```

523

524Policy for authenticated role:

525

526Reference: https://supabase.com/docs/guides/database/postgres/row-level-security

527

528---

529

530### 3.3 Optimize RLS Policies for Performance

531

532**Impact: HIGH (5-10x faster RLS queries with proper patterns)**

533

534Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.

535

536**Incorrect (function called for every row):**

537

538```sql

539create policy orders_policy on orders

540 using (auth.uid() = user_id); -- auth.uid() called per row!

541

542-- With 1M rows, auth.uid() is called 1M times

543```

544

545**Correct (wrap functions in SELECT):**

546

547```sql

548create policy orders_policy on orders

549 using ((select auth.uid()) = user_id); -- Called once, cached

550

551-- 100x+ faster on large tables

552-- Create helper function (runs as definer, bypasses RLS)

553create or replace function is_team_member(team_id bigint)

554returns boolean

555language sql

556security definer

557set search_path = ''

558as $$

559 select exists (

560 select 1 from public.team_members

561 where team_id = $1 and user_id = (select auth.uid())

562 );

563$$;

564

565-- Use in policy (indexed lookup, not per-row check)

566create policy team_orders_policy on orders

567 using ((select is_team_member(team_id)));

568create index orders_user_id_idx on orders (user_id);

569```

570

571Use security definer functions for complex checks:

572Always add indexes on columns used in RLS policies:

573

574Reference: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations

575

576---

577

578## 4. Schema Design

579

580**Impact: HIGH**

581

582Table design, index strategies, partitioning, and data type selection. Foundation for long-term performance.

583

584### 4.1 Choose Appropriate Data Types

585

586**Impact: HIGH (50% storage reduction, faster comparisons)**

587

588Using the right data types reduces storage, improves query performance, and prevents bugs.

589

590**Incorrect (wrong data types):**

591

592```sql

593create table users (

594 id int, -- Will overflow at 2.1 billion

595 email varchar(255), -- Unnecessary length limit

596 created_at timestamp, -- Missing timezone info

597 is_active varchar(5), -- String for boolean

598 price varchar(20) -- String for numeric

599);

600```

601

602**Correct (appropriate data types):**

603

604```sql

605create table users (

606 id bigint generated always as identity primary key, -- 9 quintillion max

607 email text, -- No artificial limit, same performance as varchar

608 created_at timestamptz, -- Always store timezone-aware timestamps

609 is_active boolean default true, -- 1 byte vs variable string length

610 price numeric(10,2) -- Exact decimal arithmetic

611);

612-- IDs: use bigint, not int (future-proofing)

613-- Strings: use text, not varchar(n) unless constraint needed

614-- Time: use timestamptz, not timestamp

615-- Money: use numeric, not float (precision matters)

616-- Enums: use text with check constraint or create enum type

617```

618

619Key guidelines:

620

621Reference: https://www.postgresql.org/docs/current/datatype.html

622

623---

624

625### 4.2 Index Foreign Key Columns

626

627**Impact: HIGH (10-100x faster JOINs and CASCADE operations)**

628

629Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.

630

631**Incorrect (unindexed foreign key):**

632

633```sql

634create table orders (

635 id bigint generated always as identity primary key,

636 customer_id bigint references customers(id) on delete cascade,

637 total numeric(10,2)

638);

639

640-- No index on customer_id!

641-- JOINs and ON DELETE CASCADE both require full table scan

642select * from orders where customer_id = 123; -- Seq Scan

643delete from customers where id = 123; -- Locks table, scans all orders

644```

645

646**Correct (indexed foreign key):**

647

648```sql

649create table orders (

650 id bigint generated always as identity primary key,

651 customer_id bigint references customers(id) on delete cascade,

652 total numeric(10,2)

653);

654

655-- Always index the FK column

656create index orders_customer_id_idx on orders (customer_id);

657

658-- Now JOINs and cascades are fast

659select * from orders where customer_id = 123; -- Index Scan

660delete from customers where id = 123; -- Uses index, fast cascade

661select

662 conrelid::regclass as table_name,

663 a.attname as fk_column

664from pg_constraint c

665join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)

666where c.contype = 'f'

667 and not exists (

668 select 1 from pg_index i

669 where i.indrelid = c.conrelid and a.attnum = any(i.indkey)

670 );

671```

672

673Find missing FK indexes:

674

675Reference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

676

677---

678

679### 4.3 Partition Large Tables for Better Performance

680

681**Impact: MEDIUM-HIGH (5-20x faster queries and maintenance on large tables)**

682

683Partitioning splits a large table into smaller pieces, improving query performance and maintenance operations.

684

685**Incorrect (single large table):**

686

687```sql

688create table events (

689 id bigint generated always as identity,

690 created_at timestamptz,

691 data jsonb

692);

693

694-- 500M rows, queries scan everything

695select * from events where created_at > '2024-01-01'; -- Slow

696vacuum events; -- Takes hours, locks table

697```

698

699**Correct (partitioned by time range):**

700

701```sql

702create table events (

703 id bigint generated always as identity,

704 created_at timestamptz not null,

705 data jsonb

706) partition by range (created_at);

707

708-- Create partitions for each month

709create table events_2024_01 partition of events

710 for values from ('2024-01-01') to ('2024-02-01');

711

712create table events_2024_02 partition of events

713 for values from ('2024-02-01') to ('2024-03-01');

714

715-- Queries only scan relevant partitions

716select * from events where created_at > '2024-01-15'; -- Only scans events_2024_01+

717

718-- Drop old data instantly

719drop table events_2023_01; -- Instant vs DELETE taking hours

720```

721

722When to partition:

723- Tables > 100M rows

724- Time-series data with date-based queries

725- Need to efficiently drop old data

726

727Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html

728

729---

730

731### 4.4 Select Optimal Primary Key Strategy

732

733**Impact: HIGH (Better index locality, reduced fragmentation)**

734

735Primary key choice affects insert performance, index size, and replication

736efficiency.

737

738**Incorrect (problematic PK choices):**

739

740```sql

741-- identity is the SQL-standard approach

742create table users (

743 id serial primary key -- Works, but IDENTITY is recommended

744);

745

746-- Random UUIDs (v4) cause index fragmentation

747create table orders (

748 id uuid default gen_random_uuid() primary key -- UUIDv4 = random = scattered inserts

749);

750```

751

752**Correct (optimal PK strategies):**

753

754```sql

755-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)

756create table users (

757 id bigint generated always as identity primary key

758);

759

760-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)

761-- Requires pg_uuidv7 extension: create extension pg_uuidv7;

762create table orders (

763 id uuid default uuid_generate_v7() primary key -- Time-ordered, no fragmentation

764);

765

766-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)

767create table events (

768 id text default concat(

769 to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),

770 gen_random_uuid()::text

771 ) primary key

772);

773```

774

775Guidelines:

776- Single database: `bigint identity` (sequential, 8 bytes, SQL-standard)

777- Distributed/exposed IDs: UUIDv7 (requires pg_uuidv7) or ULID (time-ordered, no

778 fragmentation)

779- `serial` works but `identity` is SQL-standard and preferred for new

780 applications

781- Avoid random UUIDs (v4) as primary keys on large tables (causes index

782 fragmentation)

783[Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY)

784

785---

786

787### 4.5 Use Lowercase Identifiers for Compatibility

788

789**Impact: MEDIUM (Avoid case-sensitivity bugs with tools, ORMs, and AI assistants)**

790

791PostgreSQL folds unquoted identifiers to lowercase. Quoted mixed-case identifiers require quotes forever and cause issues with tools, ORMs, and AI assistants that may not recognize them.

792

793**Incorrect (mixed-case identifiers):**

794

795```sql

796-- Quoted identifiers preserve case but require quotes everywhere

797CREATE TABLE "Users" (

798 "userId" bigint PRIMARY KEY,

799 "firstName" text,

800 "lastName" text

801);

802

803-- Must always quote or queries fail

804SELECT "firstName" FROM "Users" WHERE "userId" = 1;

805

806-- This fails - Users becomes users without quotes

807SELECT firstName FROM Users;

808-- ERROR: relation "users" does not exist

809```

810

811**Correct (lowercase snake_case):**

812

813```sql

814-- Unquoted lowercase identifiers are portable and tool-friendly

815CREATE TABLE users (

816 user_id bigint PRIMARY KEY,

817 first_name text,

818 last_name text

819);

820

821-- Works without quotes, recognized by all tools

822SELECT first_name FROM users WHERE user_id = 1;

823-- ORMs often generate quoted camelCase - configure them to use snake_case

824-- Migrations from other databases may preserve original casing

825-- Some GUI tools quote identifiers by default - disable this

826

827-- If stuck with mixed-case, create views as a compatibility layer

828CREATE VIEW users AS SELECT "userId" AS user_id, "firstName" AS first_name FROM "Users";

829```

830

831Common sources of mixed-case identifiers:

832

833Reference: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

834

835---

836

837## 5. Concurrency & Locking

838

839**Impact: MEDIUM-HIGH**

840

841Transaction management, isolation levels, deadlock prevention, and lock contention patterns.

842

843### 5.1 Keep Transactions Short to Reduce Lock Contention

844

845**Impact: MEDIUM-HIGH (3-5x throughput improvement, fewer deadlocks)**

846

847Long-running transactions hold locks that block other queries. Keep transactions as short as possible.

848

849**Incorrect (long transaction with external calls):**

850

851```sql

852begin;

853select * from orders where id = 1 for update; -- Lock acquired

854

855-- Application makes HTTP call to payment API (2-5 seconds)

856-- Other queries on this row are blocked!

857

858update orders set status = 'paid' where id = 1;

859commit; -- Lock held for entire duration

860```

861

862**Correct (minimal transaction scope):**

863

864```sql

865-- Validate data and call APIs outside transaction

866-- Application: response = await paymentAPI.charge(...)

867

868-- Only hold lock for the actual update

869begin;

870update orders

871set status = 'paid', payment_id = $1

872where id = $2 and status = 'pending'

873returning *;

874commit; -- Lock held for milliseconds

875-- Abort queries running longer than 30 seconds

876set statement_timeout = '30s';

877

878-- Or per-session

879set local statement_timeout = '5s';

880```

881

882Use `statement_timeout` to prevent runaway transactions:

883

884Reference: https://www.postgresql.org/docs/current/tutorial-transactions.html

885

886---

887

888### 5.2 Prevent Deadlocks with Consistent Lock Ordering

889

890**Impact: MEDIUM-HIGH (Eliminate deadlock errors, improve reliability)**

891

892Deadlocks occur when transactions lock resources in different orders. Always

893acquire locks in a consistent order.

894

895**Incorrect (inconsistent lock ordering):**

896

897```sql

898-- Transaction A -- Transaction B

899begin; begin;

900update accounts update accounts

901set balance = balance - 100 set balance = balance - 50

902where id = 1; where id = 2; -- B locks row 2

903

904update accounts update accounts

905set balance = balance + 100 set balance = balance + 50

906where id = 2; -- A waits for B where id = 1; -- B waits for A

907

908-- DEADLOCK! Both waiting for each other

909```

910

911**Correct (lock rows in consistent order first):**

912

913```sql

914-- Explicitly acquire locks in ID order before updating

915begin;

916select * from accounts where id in (1, 2) order by id for update;

917

918-- Now perform updates in any order - locks already held

919update accounts set balance = balance - 100 where id = 1;

920update accounts set balance = balance + 100 where id = 2;

921commit;

922-- Single statement acquires all locks atomically

923begin;

924update accounts

925set balance = balance + case id

926 when 1 then -100

927 when 2 then 100

928end

929where id in (1, 2);

930commit;

931-- Check for recent deadlocks

932select * from pg_stat_database where deadlocks > 0;

933

934-- Enable deadlock logging

935set log_lock_waits = on;

936set deadlock_timeout = '1s';

937```

938

939Alternative: use a single statement to update atomically:

940Detect deadlocks in logs:

941[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)

942

943---

944

945### 5.3 Use Advisory Locks for Application-Level Locking

946

947**Impact: MEDIUM (Efficient coordination without row-level lock overhead)**

948

949Advisory locks provide application-level coordination without requiring database rows to lock.

950

951**Incorrect (creating rows just for locking):**

952

953```sql

954-- Creating dummy rows to lock on

955create table resource_locks (

956 resource_name text primary key

957);

958

959insert into resource_locks values ('report_generator');

960

961-- Lock by selecting the row

962select * from resource_locks where resource_name = 'report_generator' for update;

963```

964

965**Correct (advisory locks):**

966

967```sql

968-- Session-level advisory lock (released on disconnect or unlock)

969select pg_advisory_lock(hashtext('report_generator'));

970-- ... do exclusive work ...

971select pg_advisory_unlock(hashtext('report_generator'));

972

973-- Transaction-level lock (released on commit/rollback)

974begin;

975select pg_advisory_xact_lock(hashtext('daily_report'));

976-- ... do work ...

977commit; -- Lock automatically released

978-- Returns immediately with true/false instead of waiting

979select pg_try_advisory_lock(hashtext('resource_name'));

980

981-- Use in application

982if (acquired) {

983 -- Do work

984 select pg_advisory_unlock(hashtext('resource_name'));

985} else {

986 -- Skip or retry later

987}

988```

989

990Try-lock for non-blocking operations:

991

992Reference: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

993

994---

995

996### 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing

997

998**Impact: MEDIUM-HIGH (10x throughput for worker queues)**

999

1000When multiple workers process a queue, SKIP LOCKED allows workers to process different rows without waiting.

1001

1002**Incorrect (workers block each other):**

1003

1004```sql

1005-- Worker 1 and Worker 2 both try to get next job

1006begin;

1007select * from jobs where status = 'pending' order by created_at limit 1 for update;

1008-- Worker 2 waits for Worker 1's lock to release!

1009```

1010

1011**Correct (SKIP LOCKED for parallel processing):**

1012

1013```sql

1014-- Each worker skips locked rows and gets the next available

1015begin;

1016select * from jobs

1017where status = 'pending'

1018order by created_at

1019limit 1

1020for update skip locked;

1021

1022-- Worker 1 gets job 1, Worker 2 gets job 2 (no waiting)

1023

1024update jobs set status = 'processing' where id = $1;

1025commit;

1026-- Atomic claim-and-update in one statement

1027update jobs

1028set status = 'processing', worker_id = $1, started_at = now()

1029where id = (

1030 select id from jobs

1031 where status = 'pending'

1032 order by created_at

1033 limit 1

1034 for update skip locked

1035)

1036returning *;

1037```

1038

1039Complete queue pattern:

1040

1041Reference: https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

1042

1043---

1044

1045## 6. Data Access Patterns

1046

1047**Impact: MEDIUM**

1048

1049N+1 query elimination, batch operations, cursor-based pagination, and efficient data fetching.

1050

1051### 6.1 Batch INSERT Statements for Bulk Data

1052

1053**Impact: MEDIUM (10-50x faster bulk inserts)**

1054

1055Individual INSERT statements have high overhead. Batch multiple rows in single statements or use COPY.

1056

1057**Incorrect (individual inserts):**

1058

1059```sql

1060-- Each insert is a separate transaction and round trip

1061insert into events (user_id, action) values (1, 'click');

1062insert into events (user_id, action) values (1, 'view');

1063insert into events (user_id, action) values (2, 'click');

1064-- ... 1000 more individual inserts

1065

1066-- 1000 inserts = 1000 round trips = slow

1067```

1068

1069**Correct (batch insert):**

1070

1071```sql

1072-- Multiple rows in single statement

1073insert into events (user_id, action) values

1074 (1, 'click'),

1075 (1, 'view'),

1076 (2, 'click'),

1077 -- ... up to ~1000 rows per batch

1078 (999, 'view');

1079

1080-- One round trip for 1000 rows

1081-- COPY is fastest for bulk loading

1082copy events (user_id, action, created_at)

1083from '/path/to/data.csv'

1084with (format csv, header true);

1085

1086-- Or from stdin in application

1087copy events (user_id, action) from stdin with (format csv);

10881,click

10891,view

10902,click

1091\.

1092```

1093

1094For large imports, use COPY:

1095

1096Reference: https://www.postgresql.org/docs/current/sql-copy.html

1097

1098---

1099

1100### 6.2 Eliminate N+1 Queries with Batch Loading

1101

1102**Impact: MEDIUM-HIGH (10-100x fewer database round trips)**

1103

1104N+1 queries execute one query per item in a loop. Batch them into a single query using arrays or JOINs.

1105

1106**Incorrect (N+1 queries):**

1107

1108```sql

1109-- First query: get all users

1110select id from users where active = true; -- Returns 100 IDs

1111

1112-- Then N queries, one per user

1113select * from orders where user_id = 1;

1114select * from orders where user_id = 2;

1115select * from orders where user_id = 3;

1116-- ... 97 more queries!

1117

1118-- Total: 101 round trips to database

1119```

1120

1121**Correct (single batch query):**

1122

1123```sql

1124-- Collect IDs and query once with ANY

1125select * from orders where user_id = any(array[1, 2, 3, ...]);

1126

1127-- Or use JOIN instead of loop

1128select u.id, u.name, o.*

1129from users u

1130left join orders o on o.user_id = u.id

1131where u.active = true;

1132

1133-- Total: 1 round trip

1134-- Instead of looping in application code:

1135-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)

1136

1137-- Pass array parameter:

1138select * from orders where user_id = any($1::bigint[]);

1139-- Application passes: [1, 2, 3, 4, 5, ...]

1140```

1141

1142Application pattern:

1143

1144Reference: https://supabase.com/docs/guides/database/query-optimization

1145

1146---

1147

1148### 6.3 Use Cursor-Based Pagination Instead of OFFSET

1149

1150**Impact: MEDIUM-HIGH (Consistent O(1) performance regardless of page depth)**

1151

1152OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).

1153

1154**Incorrect (OFFSET pagination):**

1155

1156```sql

1157-- Page 1: scans 20 rows

1158select * from products order by id limit 20 offset 0;

1159

1160-- Page 100: scans 2000 rows to skip 1980

1161select * from products order by id limit 20 offset 1980;

1162

1163-- Page 10000: scans 200,000 rows!

1164select * from products order by id limit 20 offset 199980;

1165```

1166

1167**Correct (cursor/keyset pagination):**

1168

1169```sql

1170-- Page 1: get first 20

1171select * from products order by id limit 20;

1172-- Application stores last_id = 20

1173

1174-- Page 2: start after last ID

1175select * from products where id > 20 order by id limit 20;

1176-- Uses index, always fast regardless of page depth

1177

1178-- Page 10000: same speed as page 1

1179select * from products where id > 199980 order by id limit 20;

1180-- Cursor must include all sort columns

1181select * from products

1182where (created_at, id) > ('2024-01-15 10:00:00', 12345)

1183order by created_at, id

1184limit 20;

1185```

1186

1187For multi-column sorting:

1188

1189Reference: https://supabase.com/docs/guides/database/pagination

1190

1191---

1192

1193### 6.4 Use UPSERT for Insert-or-Update Operations

1194

1195**Impact: MEDIUM (Atomic operation, eliminates race conditions)**

1196

1197Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.

1198

1199**Incorrect (check-then-insert race condition):**

1200

1201```sql

1202-- Race condition: two requests check simultaneously

1203select * from settings where user_id = 123 and key = 'theme';

1204-- Both find nothing

1205

1206-- Both try to insert

1207insert into settings (user_id, key, value) values (123, 'theme', 'dark');

1208-- One succeeds, one fails with duplicate key error!

1209```

1210

1211**Correct (atomic UPSERT):**

1212

1213```sql

1214-- Single atomic operation

1215insert into settings (user_id, key, value)

1216values (123, 'theme', 'dark')

1217on conflict (user_id, key)

1218do update set value = excluded.value, updated_at = now();

1219

1220-- Returns the inserted/updated row

1221insert into settings (user_id, key, value)

1222values (123, 'theme', 'dark')

1223on conflict (user_id, key)

1224do update set value = excluded.value

1225returning *;

1226-- Insert only if not exists (no update)

1227insert into page_views (page_id, user_id)

1228values (1, 123)

1229on conflict (page_id, user_id) do nothing;

1230```

1231

1232Insert-or-ignore pattern:

1233

1234Reference: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

1235

1236---

1237

1238## 7. Monitoring & Diagnostics

1239

1240**Impact: LOW-MEDIUM**

1241

1242Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection, and performance diagnostics.

1243

1244### 7.1 Enable pg_stat_statements for Query Analysis

1245

1246**Impact: LOW-MEDIUM (Identify top resource-consuming queries)**

1247

1248pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.

1249

1250**Incorrect (no visibility into query patterns):**

1251

1252```sql

1253-- Database is slow, but which queries are the problem?

1254-- No way to know without pg_stat_statements

1255```

1256

1257**Correct (enable and query pg_stat_statements):**

1258

1259```sql

1260-- Enable the extension

1261create extension if not exists pg_stat_statements;

1262

1263-- Find slowest queries by total time

1264select

1265 calls,

1266 round(total_exec_time::numeric, 2) as total_time_ms,

1267 round(mean_exec_time::numeric, 2) as mean_time_ms,

1268 query

1269from pg_stat_statements

1270order by total_exec_time desc

1271limit 10;

1272

1273-- Find most frequent queries

1274select calls, query

1275from pg_stat_statements

1276order by calls desc

1277limit 10;

1278

1279-- Reset statistics after optimization

1280select pg_stat_statements_reset();

1281-- Queries with high mean time (candidates for optimization)

1282select query, mean_exec_time, calls

1283from pg_stat_statements

1284where mean_exec_time > 100 -- > 100ms average

1285order by mean_exec_time desc;

1286```

1287

1288Key metrics to monitor:

1289

1290Reference: https://supabase.com/docs/guides/database/extensions/pg_stat_statements

1291

1292---

1293

1294### 7.2 Maintain Table Statistics with VACUUM and ANALYZE

1295

1296**Impact: MEDIUM (2-10x better query plans with accurate statistics)**

1297

1298Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.

1299

1300**Incorrect (stale statistics):**

1301

1302```sql

1303-- Table has 1M rows but stats say 1000

1304-- Query planner chooses wrong strategy

1305explain select * from orders where status = 'pending';

1306-- Shows: Seq Scan (because stats show small table)

1307-- Actually: Index Scan would be much faster

1308```

1309

1310**Correct (maintain fresh statistics):**

1311

1312```sql

1313-- Manually analyze after large data changes

1314analyze orders;

1315

1316-- Analyze specific columns used in WHERE clauses

1317analyze orders (status, created_at);

1318

1319-- Check when tables were last analyzed

1320select

1321 relname,

1322 last_vacuum,

1323 last_autovacuum,

1324 last_analyze,

1325 last_autoanalyze

1326from pg_stat_user_tables

1327order by last_analyze nulls first;

1328-- Increase frequency for high-churn tables

1329alter table orders set (

1330 autovacuum_vacuum_scale_factor = 0.05, -- Vacuum at 5% dead tuples (default 20%)

1331 autovacuum_analyze_scale_factor = 0.02 -- Analyze at 2% changes (default 10%)

1332);

1333

1334-- Check autovacuum status

1335select * from pg_stat_progress_vacuum;

1336```

1337

1338Autovacuum tuning for busy tables:

1339

1340Reference: https://supabase.com/docs/guides/database/database-size#vacuum-operations

1341

1342---

1343

1344### 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries

1345

1346**Impact: LOW-MEDIUM (Identify exact bottlenecks in query execution)**

1347

1348EXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.

1349

1350**Incorrect (guessing at performance issues):**

1351

1352```sql

1353-- Query is slow, but why?

1354select * from orders where customer_id = 123 and status = 'pending';

1355-- "It must be missing an index" - but which one?

1356```

1357

1358**Correct (use EXPLAIN ANALYZE):**

1359

1360```sql

1361explain (analyze, buffers, format text)

1362select * from orders where customer_id = 123 and status = 'pending';

1363

1364-- Output reveals the issue:

1365-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=100) (actual time=0.015..450.123 rows=50 loops=1)

1366-- Filter: ((customer_id = 123) AND (status = 'pending'::text))

1367-- Rows Removed by Filter: 999950

1368-- Buffers: shared hit=5000 read=15000

1369-- Planning Time: 0.150 ms

1370-- Execution Time: 450.500 ms

1371-- Seq Scan on large tables = missing index

1372-- Rows Removed by Filter = poor selectivity or missing index

1373-- Buffers: read >> hit = data not cached, needs more memory

1374-- Nested Loop with high loops = consider different join strategy

1375-- Sort Method: external merge = work_mem too low

1376```

1377

1378Key things to look for:

1379

1380Reference: https://supabase.com/docs/guides/database/inspect

1381

1382---

1383

1384## 8. Advanced Features

1385

1386**Impact: LOW**

1387

1388Full-text search, JSONB optimization, PostGIS, extensions, and advanced Postgres features.

1389

1390### 8.1 Index JSONB Columns for Efficient Querying

1391

1392**Impact: MEDIUM (10-100x faster JSONB queries with proper indexing)**

1393

1394JSONB queries without indexes scan the entire table. Use GIN indexes for containment queries.

1395

1396**Incorrect (no index on JSONB):**

1397

1398```sql

1399create table products (

1400 id bigint primary key,

1401 attributes jsonb

1402);

1403

1404-- Full table scan for every query

1405select * from products where attributes @> '{"color": "red"}';

1406select * from products where attributes->>'brand' = 'Nike';

1407```

1408

1409**Correct (GIN index for JSONB):**

1410

1411```sql

1412-- GIN index for containment operators (@>, ?, ?&, ?|)

1413create index products_attrs_gin on products using gin (attributes);

1414

1415-- Now containment queries use the index

1416select * from products where attributes @> '{"color": "red"}';

1417

1418-- For specific key lookups, use expression index

1419create index products_brand_idx on products ((attributes->>'brand'));

1420select * from products where attributes->>'brand' = 'Nike';

1421-- jsonb_ops (default): supports all operators, larger index

1422create index idx1 on products using gin (attributes);

1423

1424-- jsonb_path_ops: only @> operator, but 2-3x smaller index

1425create index idx2 on products using gin (attributes jsonb_path_ops);

1426```

1427

1428Choose the right operator class:

1429

1430Reference: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

1431

1432---

1433

1434### 8.2 Use tsvector for Full-Text Search

1435

1436**Impact: MEDIUM (100x faster than LIKE, with ranking support)**

1437

1438LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.

1439

1440**Incorrect (LIKE pattern matching):**

1441

1442```sql

1443-- Cannot use index, scans all rows

1444select * from articles where content like '%postgresql%';

1445

1446-- Case-insensitive makes it worse

1447select * from articles where lower(content) like '%postgresql%';

1448```

1449

1450**Correct (full-text search with tsvector):**

1451

1452```sql

1453-- Add tsvector column and index

1454alter table articles add column search_vector tsvector

1455 generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;

1456

1457create index articles_search_idx on articles using gin (search_vector);

1458

1459-- Fast full-text search

1460select * from articles

1461where search_vector @@ to_tsquery('english', 'postgresql & performance');

1462

1463-- With ranking

1464select *, ts_rank(search_vector, query) as rank

1465from articles, to_tsquery('english', 'postgresql') query

1466where search_vector @@ query

1467order by rank desc;

1468-- AND: both terms required

1469to_tsquery('postgresql & performance')

1470

1471-- OR: either term

1472to_tsquery('postgresql | mysql')

1473

1474-- Prefix matching

1475to_tsquery('post:*')

1476```

1477

1478Search multiple terms:

1479

1480Reference: https://supabase.com/docs/guides/database/full-text-search

1481

1482---

1483

1484## References

1485

1486- https://www.postgresql.org/docs/current/

1487- https://supabase.com/docs

1488- https://wiki.postgresql.org/wiki/Performance_Optimization

1489- https://supabase.com/docs/guides/database/overview

1490- https://supabase.com/docs/guides/auth/row-level-security

1491

davila7/claude-code-templates · CLAUDE.md

@@ +1 @@

1# CLAUDE.md

3This file provides guidance to Claude Code when working with this repository.

5## Project Overview

7Node.js CLI tool for managing Claude Code components (agents, commands, MCPs, hooks, settings) with a static website for browsing and installing components. The dashboard and its API routes are deployed on Cloudflare Pages, with supporting cron and monitoring tasks running as Cloudflare Workers.

9## Essential Commands

11```bash

12# Development

13npm install # Install dependencies

14npm test # Run tests

15npm version patch|minor|major # Bump version

16npm publish # Publish to npm

18# Component catalog

19python scripts/generate_components_json.py # Update docs/components.json

21# Dashboard + API (Astro on Cloudflare Pages)

22cd dashboard && npm run build # Build before deploy

23npm run deploy # Deploy www + app.aitmpl.com via wrangler

24```

26> Deploys to production happen automatically via GitHub Actions on push to `main`

27> (changes in `dashboard/**`). Manual deploy uses `wrangler pages deploy`, not Vercel.

29## Security Guidelines

31### ⛔ CRITICAL: NEVER Hardcode Secrets or IDs

33**NEVER write API keys, tokens, passwords, project IDs, org IDs, or any identifier in code.** This includes Cloudflare account/project IDs, Supabase URLs, Discord IDs, database connection strings, and any other infrastructure identifier. ALL must go in `.env` (or Cloudflare secrets via `wrangler secret put`).

35```javascript

36// ❌ WRONG

37const API_KEY = "AIzaSy...";

39// ✅ CORRECT

40const API_KEY = process.env.GOOGLE_API_KEY;

41```

43**When creating scripts with API keys:**

441. Use `process.env` (Node.js) or `os.environ.get()` (Python)

452. Load from `.env` file using `dotenv`

463. Add variable to `.env.example` with placeholder

474. Verify `.env` is in `.gitignore`

49**If you accidentally commit a secret:**

501. Revoke the key IMMEDIATELY

512. Generate new key

523. Update `.env`

534. Old key is compromised forever (git history)

55## Component System

57### Component Types

59**Agents** (600+) - AI specialists for development tasks

60**Commands** (200+) - Custom slash commands for workflows

61**MCPs** (55+) - External service integrations

62**Settings** (60+) - Claude Code configuration files

63**Hooks** (39+) - Automation triggers

64**Loops** (18+) - Autonomous agentic workflows (goal + interval + stop condition) that reference other components

65**Templates** (14+) - Complete project configurations

67### Installation Patterns

69```bash

70# Single component

71npx claude-code-templates@latest --agent frontend-developer

72npx claude-code-templates@latest --command setup-testing

73npx claude-code-templates@latest --hook automation/simple-notifications

74npx claude-code-templates@latest --loop engineering/docs-sweep-loop # also installs the loop's referenced components

76# Batch installation

77npx claude-code-templates@latest --agent security-auditor --command security-audit --setting read-only-mode

79# Interactive mode

80npx claude-code-templates@latest

81```

83### Component Development

85#### Adding New Components

87**CRITICAL: Use the component-reviewer agent for ALL component changes**

89When adding or modifying components, you MUST use the `component-reviewer` subagent to validate the component before committing:

91```

92Use the component-reviewer agent to review [component-path]

93```

95**Component Creation Workflow:**

971. Create component file in `cli-tool/components/{type}/{category}/{name}.md`

982. Use descriptive hyphenated names (kebab-case)

993. Include clear descriptions and usage examples

1004. **REVIEW with component-reviewer agent** (validates format, security, naming)

1015. Fix any issues identified by the reviewer

1026. **TEST before generating/publishing**: ask the human in the session whether

103 they want to test the newly created/modified component(s) first. Do NOT run

104 `generate_components_json.py`, commit, or publish until testing is confirmed

105 or explicitly skipped by the user.

1067. Run `python scripts/generate_components_json.py` to update catalog

107

108**The component-reviewer agent checks:**

109- ✅ Valid YAML frontmatter and required fields

110- ✅ Proper kebab-case naming conventions

111- ✅ No hardcoded secrets (API keys, tokens, passwords)

112- ✅ Relative paths only (no absolute paths)

113- ✅ Supporting files exist (for hooks with scripts)

114- ✅ Clear, specific descriptions

115- ✅ Correct category placement

116- ✅ Security best practices

117

118**Example Usage:**

119```

120# After creating a new agent

121Use the component-reviewer agent to review cli-tool/components/agents/development-team/react-expert.md

122

123# Before committing hook changes

124Use the component-reviewer agent to review cli-tool/components/hooks/git/prevent-force-push.json

125

126# For PR reviews with multiple components

127Use the component-reviewer agent to review all modified components in cli-tool/components/

128```

129

130The agent will provide prioritized feedback:

131- **❌ Critical Issues**: Must fix before merge (security, missing fields)

132- **⚠️ Warnings**: Should fix (clarity, best practices)

133- **📋 Suggestions**: Nice to have improvements

134

135#### Skill Security Scanning (SkillSpector)

136

137Skills under `cli-tool/components/skills/**` are scanned for security

138vulnerabilities by [SkillSpector](https://github.com/NVIDIA/skillspector)

139(NVIDIA, Apache-2.0) — a static analyzer with 64 vulnerability patterns

140(prompt injection, data exfiltration, supply chain, dangerous code/AST, taint

141tracking, YARA signatures, etc.). It runs in static-only mode (`--no-llm`), so

142no API key or secret is required.

143

144Two GitHub Actions drive it, both via the batch orchestrator

145`scripts/skillspector_scan.py`:

146

147- **`.github/workflows/skill-security-scan.yml`** (PR) — scans only the skills

148 changed in the PR (`git diff`), posts an idempotent report comment, and

149 **blocks** the check if any changed skill scores HIGH/CRITICAL (risk score

150 > 50). Uploads an aggregated SARIF to the Security tab.

151- **`.github/workflows/skill-security-scan-all.yml`** (weekly + manual) — scans

152 all skills, reports to the run summary and SARIF, and **never blocks**.

153

154SkillSpector requires Python 3.12+ and is installed from NVIDIA's `main`

155branch (`pip install git+https://github.com/NVIDIA/skillspector.git@main`); it

156is not published to PyPI. Risk bands: 0-20 LOW, 21-50 MEDIUM, 51-80 HIGH,

15781-100 CRITICAL.

158

159#### Statuslines with Python Scripts

160

161Statuslines can reference Python scripts that are auto-downloaded to `.claude/scripts/`:

162

163```javascript

164// In src/index.js:installIndividualSetting()

165if (settingName.includes('statusline/')) {

166 const pythonFileName = settingName.split('/')[1] + '.py';

167 const pythonUrl = githubUrl.replace('.json', '.py');

168 additionalFiles['.claude/scripts/' + pythonFileName] = {

169 content: pythonContent,

170 executable: true

171 };

172}

173```

174

175### Publishing Workflow

176

177```bash

178# 1. Update component catalog

179python scripts/generate_components_json.py

180

181# 2. Run tests

182npm test

183

184# 3. Check current npm version and align local version

185npm view claude-code-templates version # check latest on registry

186# Edit package.json version to be one patch above the registry version

187

188# 4. Commit version bump and push

189git add package.json && git commit -m "chore: Bump version to X.Y.Z"

190git push origin main

191

192# 5. Publish to npm (requires granular access token with "Bypass 2FA" enabled)

193npm config set //registry.npmjs.org/:_authToken=YOUR_GRANULAR_TOKEN

194npm publish

195npm config delete //registry.npmjs.org/:_authToken # always clean up after

196

197# 6. Tag the release

198git tag vX.Y.Z && git push origin vX.Y.Z

199

200# 7. Deploy website (dashboard on Cloudflare Pages)

201# Automatic on push to main (GitHub Actions). Manual: from dashboard/ run `npm run deploy`

202```

203

204**npm Publishing Notes:**

205- Classic npm tokens were revoked Dec 2025. Use **granular access tokens** from [npmjs.com/settings/~/tokens](https://www.npmjs.com/settings/~/tokens)

206- The token must have **Read and Write** permissions for `claude-code-templates` and **"Bypass 2FA"** enabled

207- Always remove the token from npm config after publishing (`npm config delete`)

208- The local `package.json` version may drift from npm if published from CI — always check `npm view claude-code-templates version` first

209- Never hardcode or commit tokens

210

211## API Architecture

212

213### Critical Endpoints

214

215API endpoints live as Astro API routes in `dashboard/src/pages/api/`:

216

217**`/api/track-download-supabase`** (CRITICAL)

218- Tracks component downloads for analytics

219- Used by CLI on every installation

220- Database: Supabase (component_downloads table)

221

222**`/api/discord/interactions`**

223- Discord bot slash commands

224- Features: /search, /info, /install, /popular

225

226**`/api/claude-code-check`**

227- Monitors Claude Code releases

228- Triggered every 30 minutes by the `cloudflare-workers/crons` Worker (not a Vercel cron)

229- Database: Neon (claude_code_versions, claude_code_changes, discord_notifications_log, monitoring_metadata tables)

230

231### Shared API Libraries

232

233- `dashboard/src/lib/api/cors.ts` — CORS headers, `corsResponse()`, `jsonResponse()`

234- `dashboard/src/lib/api/neon.ts` — Neon client factory

235- `dashboard/src/lib/api/auth.ts` — Clerk JWT verification

236- `dashboard/src/lib/api/changelog-parser.ts` — Claude Code changelog parser

237

238### Emergency Rollback

239

240```bash

241# List recent Pages deployments

242npx wrangler pages deployment list --project-name=aitmpl-dashboard

243# Roll back to a previous deployment

244npx wrangler pages deployment rollback <deployment-id> --project-name=aitmpl-dashboard

245```

246

247## Cloudflare Workers

248

249The `cloudflare-workers/` directory contains Cloudflare Worker projects that run independently from the dashboard Pages project.

250

251### crons

252

253Replaces the old Vercel cron jobs. On a schedule it calls the dashboard API endpoints (which stay on Cloudflare Pages) with a shared `TRIGGER_SECRET`.

254

255- `*/30 * * * *` → `/api/claude-code-check` (monitors Claude Code npm releases)

256- `0 * * * *` → `/api/health-check` (hourly; was every 15 min on Vercel, reduced to save invocations)

257

258Errors and cron check-ins are reported to Sentry (`sentry.js`, DSN from the `aitmpl-workers` project — see Error Tracking below).

259

260```bash

261cd cloudflare-workers/crons

262npm run dev # Local dev

263npx wrangler deploy # Deploy

264```

265

266**Secrets (Cloudflare):** `DASHBOARD_URL` (e.g. `https://www.aitmpl.com`), `TRIGGER_SECRET`, `SENTRY_DSN` (optional).

267

268### docs-monitor (DECOMMISSIONED 2026-07)

269

270Monitored https://code.claude.com/docs hourly with Telegram notifications. **Deleted from Cloudflare** to free a cron-trigger slot for the newsletter worker (the account's free plan allows 5 cron triggers total). The code remains in `cloudflare-workers/docs-monitor/` and can be redeployed if a slot frees up (`npx wrangler deploy`).

271

272### pulse (Weekly KPI Report)

273

274Collects metrics from GitHub, Discord, Supabase, npm, and Google Analytics every Sunday at 14:00 UTC and sends a consolidated report via Telegram.

275

276**Architecture:** Single `index.js` file (no npm dependencies at runtime). All source collectors, formatter, and Telegram sender in one file.

277

278**Cron:** `0 14 * * 0` (Sundays 14:00 UTC / 11:00 AM Chile)

279

280```bash

281cd cloudflare-workers/pulse

282npm run dev # Local dev

283npx wrangler deploy # Deploy

284

285# Manual trigger

286curl -X POST https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger \

287 -H "Authorization: Bearer $TRIGGER_SECRET"

288

289# Test single source

290curl -X POST "https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger?source=github" \

291 -H "Authorization: Bearer $TRIGGER_SECRET"

292

293# Dry run (no Telegram)

294curl -X POST "https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger?send=false" \

295 -H "Authorization: Bearer $TRIGGER_SECRET"

296```

297

298**Secrets (Cloudflare):**

299```bash

300TELEGRAM_BOT_TOKEN # Shared with docs-monitor

301TELEGRAM_CHAT_ID # Shared with docs-monitor

302GITHUB_TOKEN # GitHub PAT (public_repo scope)

303SUPABASE_URL # Supabase project URL

304SUPABASE_SERVICE_ROLE_KEY # Supabase service role key

305DISCORD_BOT_TOKEN # Discord bot token

306DISCORD_GUILD_ID # Discord server ID

307TRIGGER_SECRET # For manual /trigger endpoint

308GA_PROPERTY_ID # GA4 property ID (optional)

309GA_SERVICE_ACCOUNT_JSON # Base64 service account (optional)

310```

311

312**Graceful degradation:** Each source catches its own errors. Missing secrets or API failures show `⚠️ Unavailable` instead of crashing the report. Failed collectors are also reported to Sentry via `sentry.js` (see Error Tracking below). The Vercel collector was removed (2026-07) since the dashboard no longer deploys to Vercel.

313

314### newsletter (Weekly Community Components Email)

315

316Composes and sends a simple weekly email via Resend featuring trending components (one Skill, Agent, MCP, Hook and Setting per send, in that fixed order). Selection is weighted-random by recent downloads and the copy (subject, catalog intro, per-component sentences, stats cited, closer) rotates from pools so no two emails read the same. Body is plain text plus a minimal HTML version (bold + underlined component titles, clickable component links). Data comes from the live `trending-data.json` + `components.json`.

317

318**Delivery:** Resend **Broadcast** targeting the segment in `RESEND_SEGMENT_ID` — Resend injects the per-recipient unsubscribe link (`{{{RESEND_UNSUBSCRIBE_URL}}}` placeholder in the body) and manages the suppression list automatically. Replies go to `NEWSLETTER_REPLY_TO`. The segment is the safety gate: point it at a pilot segment for tests or the full-audience segment for community-wide sends. Open/click tracking is enabled on the `aitmpl.com` domain with tracking subdomain `track.aitmpl.com` (metrics per broadcast at resend.com/broadcasts). Cron: Sundays 16:00 UTC (slot freed by decommissioning docs-monitor). `GET /preview?format=text` composes without sending; `POST /trigger` sends (`?send=false` for dry run).

319

320```bash

321cd cloudflare-workers/newsletter

322npm run dev # Local dev

323npx wrangler deploy # Deploy

324

325# Preview content without sending (repeat to see the copy rotate)

326curl "https://aitmpl-newsletter.SUBDOMAIN.workers.dev/preview?format=text" \

327 -H "Authorization: Bearer $TRIGGER_SECRET"

328

329# Real send: creates + sends a Broadcast to the segment in RESEND_SEGMENT_ID

330curl -X POST "https://aitmpl-newsletter.SUBDOMAIN.workers.dev/trigger" \

331 -H "Authorization: Bearer $TRIGGER_SECRET"

332```

333

334**Secrets (Cloudflare):** `RESEND_API_KEY` (full access — broadcasts/segments), `RESEND_SEGMENT_ID`, `NEWSLETTER_REPLY_TO`, `TRIGGER_SECRET`, `SENTRY_DSN` (optional). Public vars in `wrangler.toml [vars]`: `DASHBOARD_URL`, `RESEND_FROM_EMAIL` (`daniel.avila@aitmpl.com`).

335

336## Error Tracking (Sentry)

337

338Free-tier Sentry, added to close the gap where automated cron/worker failures

339were previously invisible. No official `@sentry/*` SDK is used anywhere —

340every surface has its own tiny dependency-free client that posts directly to

341the Sentry envelope API via `fetch()`, matching this repo's zero-dependency

342worker style and avoiding Cloudflare Pages SSR friction with `@sentry/astro`.

343

344**Status as of 2026-07-04: all 3 projects live and verified end-to-end** (each

345confirmed with a manual test event returning HTTP 200 from Sentry and

346appearing in its Issues dashboard).

347

348- ✅ **Cloudflare Workers** (Sentry project `aitmpl-workers`) — `SENTRY_DSN`

349 secret set on all 3 workers (`aitmpl-crons`, `pulse-weekly-report`,

350 `claude-docs-monitor`) via `wrangler secret put SENTRY_DSN`.

351- ✅ **Dashboard** (Sentry project `aitmpl-dashboard`) — `SENTRY_DSN` set as a

352 Cloudflare Pages secret (`wrangler pages secret put SENTRY_DSN

353 --project-name=aitmpl-dashboard`). Wired into `captureApiError()` calls in

354 `claude-code-check`, `health-check`, and the three `track-*` endpoints.

355- ✅ **CLI** (Sentry project `aitmpl-cli`) — the DSN is public by design

356 (send-only, not a secret) and ships **hardcoded as the default** in

357 `cli-tool/src/error-reporting.js` (`DEFAULT_SENTRY_DSN`, overridable via

358 `CCT_SENTRY_DSN` for testing against a different project). Reporting

359 itself stays **opt-in**: requires the end user to set

360 `CCT_ERROR_REPORTING=true`, and always defers to the existing

361 `CCT_NO_TRACKING`/`CCT_NO_ANALYTICS`/`CI` opt-outs.

362

363**Not yet configured (any surface):** Sentry alert rules to Discord/Telegram,

364and Cron Monitors dashboards for the workers' scheduled check-ins (the

365`checkIn()` calls already send `in_progress`/`ok`/`error` events — a Monitor

366just needs to be created in the Sentry UI with matching slugs:

367`claude-code-check`, `health-check`, `pulse-weekly-report`, `docs-monitor`).

368

369**Files:** `cloudflare-workers/{crons,pulse,docs-monitor}/sentry.js` (workers),

370`dashboard/src/lib/api/error-tracking.ts` (dashboard), `cli-tool/src/error-reporting.js` (CLI).

371

372## Dashboard (www.aitmpl.com)

373

374Astro + React + Tailwind dashboard serving both `www.aitmpl.com` and `app.aitmpl.com`. Clerk auth for user collections. Source lives in `dashboard/`. All API endpoints are Astro API routes in the same project.

375

376### Architecture

377

378- **Framework**: Astro 5 with React islands, Tailwind v4, `output: 'server'`, `@astrojs/cloudflare` adapter (`mode: 'directory'`)

379- **Hosting**: Cloudflare Pages (project `aitmpl-dashboard`), SSR on Workers runtime

380- **Auth**: Clerk (`window.Clerk` global, no ClerkProvider per island)

381- **Data**: `components.json` and `trending-data.json` served from `dashboard/public/` (same-origin)

382- **APIs**: All endpoints in `dashboard/src/pages/api/` (Astro API routes, no separate serverless project)

383

384### Featured Pages (`/featured/[slug]`)

385

386Featured partner integrations shown on the dashboard homepage. Two files to edit:

387

388**`dashboard/src/lib/constants.ts`** — `FEATURED_ITEMS` array. Each entry has:

389- `name`, `description`, `logo`, `url` (`/featured/slug`), `tag`, `tagColor`, `category`

390- `ctaLabel`, `ctaUrl`, `websiteUrl`

391- `installCommand` — shown in the sidebar Quick Install box

392- `metadata` — key/value pairs shown in the Details sidebar (e.g. `Components: '8'`)

393- `links` — sidebar links list

394

395**`dashboard/src/pages/featured/[slug].astro`** — Content for each slug rendered via `{slug === 'brightdata' && (...)}` blocks. Each block contains the full HTML content for that partner page.

396

397**When adding a skill to a featured page:**

3981. Add a new card `<div class="flex gap-3 ...">` inside the Skills Layer section of the relevant `{slug === '...'}` block

3992. Update `installCommand` in `constants.ts` to include the new skill

4003. Increment `metadata.Components` count in `constants.ts`

401

402Current featured slugs: `brightdata`, `neon-instagres`, `claudekit`, `braingrid`

403

404### Cloudflare Pages Project Setup

405

406A single Cloudflare Pages project (`aitmpl-dashboard`) serves all domains. Config lives in `dashboard/wrangler.toml`:

407

409|---------|---------|----------------|--------------|

411

412`wrangler.toml` sets `pages_build_output_dir = "./dist"`, `compatibility_flags = ["nodejs_compat"]`, and the `PUBLIC_*` build-time vars in `[vars]`. Secrets are set via the Cloudflare Dashboard or `wrangler pages secret put`.

413

414### Deployment

415

416**ALWAYS use the deployer agent (`.claude/agents/deployer.md`) for all deployments.** It runs pre-deploy checks (auth, git status, build) and handles the full pipeline safely. Never deploy manually.

417

418```bash

419npm run deploy # Build + `wrangler pages deploy dist` for www + app.aitmpl.com

420npm run deploy:dashboard # Same as above

421```

422

423**CI/CD**: Pushes to `main` auto-deploy via GitHub Actions (`.github/workflows/deploy.yml`):

424- Changes in `dashboard/**` trigger a build and `wrangler pages deploy dist --project-name=aitmpl-dashboard`

425

426**Required GitHub Secrets** (Settings > Secrets > Actions):

427- `CLOUDFLARE_API_TOKEN` — Cloudflare API token with Pages edit permission

428- `CLOUDFLARE_ACCOUNT_ID` — Cloudflare account ID

429

430### Environment Variables (Cloudflare)

431

432`PUBLIC_*` vars are build-time and live in `dashboard/wrangler.toml` `[vars]` (and are also passed to the GitHub Actions build step). Everything else is a Cloudflare secret (`wrangler pages secret put <NAME>` or the Pages dashboard):

433

434```bash

435# Clerk

436PUBLIC_CLERK_PUBLISHABLE_KEY=xxx # [vars] — build-time

437CLERK_SECRET_KEY=xxx # secret

438

439# Data

440PUBLIC_COMPONENTS_JSON_URL=/components.json # [vars] — build-time

441

442# GitHub OAuth

443PUBLIC_GITHUB_CLIENT_ID=xxx # [vars] — build-time

444GITHUB_CLIENT_SECRET=xxx # secret

445

446# Supabase (download tracking)

447SUPABASE_URL=https://xxx.supabase.co # secret

448SUPABASE_SERVICE_ROLE_KEY=xxx # secret

449

450# Neon Database

451NEON_DATABASE_URL=postgresql://user:pass@host/db?sslmode=require # secret

452

453# Discord

454DISCORD_APP_ID=xxx # secret

455DISCORD_BOT_TOKEN=xxx # secret

456DISCORD_PUBLIC_KEY=xxx # secret

457DISCORD_WEBHOOK_URL_CHANGELOG=https://discord.com/api/webhooks/xxx # secret

458```

459

460### Known Issues & Solutions

461

462**Node built-ins in SSR**

463- The Cloudflare Workers runtime does not expose Node's `fs`/`path`/etc. by default. `astro.config.mjs` enables `nodejs_compat` (via `wrangler.toml`) and externalizes `node:fs`, `node:path`, `node:url`, `node:stream` in SSR. Avoid adding new hard dependencies on Node-only APIs in server code.

464

465**`react-dom/server` on Cloudflare**

466- `astro.config.mjs` aliases `react-dom/server` to `react-dom/server.node` and marks `react-dom` as `noExternal` at build time so React SSR works on the Workers runtime. Don't remove this alias.

467

468### Local Development

469

470```bash

471cd dashboard

472npm install

473npx astro dev --port 4321 # Dashboard + APIs at http://localhost:4321

474```

475

476## Data Files

477

478### Component Catalog

479

480- `docs/components.json` — Full generated catalog (source of truth), keeps `content` and `security` fields (needed by the legacy static site)

481- `dashboard/public/components.json` — Dashboard copy, **without** `content`/`security` (lighter payload; dashboard doesn't need them)

482- `dashboard/public/counts.json` — Per-type counts only (e.g. `{"agents": 421, ...}`), used by the sidebar/plugins pages instead of loading the full catalog

483- `dashboard/public/components/{type}.json` — One file per component type (agents.json, commands.json, etc.), loaded on demand by `ComponentGrid.tsx` for the active tab

484- `dashboard/public/search-index.json` — Flat array for `SearchModal.tsx`

485- `dashboard/public/component-content/{type}/{slug}.json` — Full per-component content (incl. markdown body), fetched on demand when a component's detail view or PR flow needs it

486- `dashboard/public/trending-data.json` — Trending/download stats

487

488All of the above are served as static Cloudflare Pages assets with

489`cache-control: public, max-age=86400, stale-while-revalidate=3600` (see

490`dashboard/public/_headers`).

491

492### Data Flow

493

4941. `scripts/generate_components_json.py` scans `cli-tool/components/`

4952. Generates `docs/components.json` (full, with `content`/`security`) and the split dashboard artifacts (`dashboard/public/components.json`, `counts.json`, `components/{type}.json`, `search-index.json`, `component-content/{type}/{slug}.json`) — these two writes are decoupled, so the dashboard payload stays lean without touching the legacy catalog

4963. Dashboard islands (`ComponentGrid.tsx`, `SearchModal.tsx`, `Sidebar.astro`, `SendToRepoModal.tsx`) load the split artifacts instead of the full catalog

4974. Download tracking via `/api/track-download-supabase`

498

499### Plugins & Marketplaces Catalog

500

501- `scripts/generate_plugins_json.py` — scans the repos listed in `REPOS` via the `gh` CLI (needs `gh auth login`) and writes `dashboard/public/plugins.json`. This is a **manual, offline step** — it does not run during `npm run build` or CI/CD, so re-running it never affects deploy time.

502- For each marketplace it records `plugins_list[].components` (counts per type) and `plugins_list[].components_items` (`{name, description}` per command/agent/skill/hook/mcp/lsp, description parsed from the item's frontmatter). The dashboard's `/plugins/[slug].astro` page renders this through `MarketplacePluginsList.tsx`, which shows a search box and a "view details" modal per plugin.

503- **`max_local_scans = 50`** in `extract_marketplace_plugins_detail()` caps how many *locally-sourced* plugins (i.e. `source: "./plugins/..."` within the marketplace's own repo) get scanned for real component names/descriptions, per marketplace, to bound GitHub API calls. Plugins beyond that cap (or plugins hosted in an external repo, which are never scanned) fall back to showing only tag badges in the modal, with no itemized breakdown — this is a graceful degradation, not an error.

504 - As of 2026-07-11, `anthropics/claude-plugins-official` alone has 51 locally-sourced plugins (out of 255 total), i.e. already at the edge of this cap. Bump `max_local_scans` if more complete coverage is needed — GitHub's rate limit (5000 req/hour authenticated) is not the constraint, wall-clock run time is (each item now costs 1 extra API call to fetch its file content for the description).

505

506### Legacy Static Site (docs/)

507

508The `docs/` directory contains the old static HTML site (no longer deployed to www). Blog articles in `docs/blog/` are still referenced externally.

509

510### Blog Article Creation

511

512Use the CLI skill to create blog articles:

513

514```bash

515/create-blog-article @cli-tool/components/{type}/{category}/{name}.json

516```

517

518This automatically:

5191. Generates AI cover image

5202. Creates HTML with SEO optimization

5213. Updates `docs/blog/blog-articles.json`

522

523## Code Standards

524

525### Path Handling

526- Use relative paths: `.claude/scripts/`, `.claude/hooks/`

527- Never hardcode absolute paths or home directories

528- Use `path.join()` for cross-platform compatibility

529

530### Naming Conventions

531- Files: `kebab-case.js`, `PascalCase.js` (for classes)

532- Functions/Variables: `camelCase`

533- Constants: `UPPER_SNAKE_CASE`

534- Components: `hyphenated-names`

535

536### Error Handling

537- Use try/catch for async operations

538- Provide helpful error messages

539- Log errors with context

540- Implement fallback mechanisms

541

542## Testing

543

544```bash

545npm test # Run all tests

546npm run test:watch # Watch mode

547npm run test:coverage # Coverage report

548```

549

550Aim for 70%+ test coverage. Test critical paths and error handling.

551

552## Common Issues

553

554**API endpoint returns 404 after deploy**

555- API routes must be in `dashboard/src/pages/api/` as Astro API routes

556- Export named HTTP methods: `export const POST: APIRoute`, `export const GET: APIRoute`

557

558**Download tracking not working**

559- Check Cloudflare Pages logs: `npx wrangler pages deployment tail --project-name=aitmpl-dashboard`

560- Verify environment variables / secrets in the Cloudflare Pages dashboard

561- Test endpoint manually with curl

562

563**Components not updating on website**

564- Run `python scripts/generate_components_json.py` (writes both `docs/components.json` and the split `dashboard/public/` artifacts directly — no manual copy step)

565- Deploy and clear browser cache (artifacts are cached 24h at the edge, see `dashboard/public/_headers`)

566

567## Important Notes

568

569- **Component catalog**: Always regenerate after adding/modifying components

570- **API tests**: Required before production deploy (breaks download tracking)

571- **Secrets**: Never commit API keys (use environment variables)

572- **Paths**: Use relative paths for all project files

573- **Backwards compatibility**: Don't break existing component installations

574

@@ −1 +1 @@

1−# Postgres Best Practices

1+# CLAUDE.md

3−**Version 1.0.0**

4−Supabase

5−January 2026

3+This file provides guidance to Claude Code when working with this repository.

7−> This document is optimized for AI agents and LLMs. Rules are prioritized by performance impact.

5+## Project Overview

9−---

7+Node.js CLI tool for managing Claude Code components (agents, commands, MCPs, hooks, settings) with a static website for browsing and installing components. The dashboard and its API routes are deployed on Cloudflare Pages, with supporting cron and monitoring tasks running as Cloudflare Workers.

108

11−## Abstract

9+## Essential Commands

1210

13−Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.

11+```bash

12+# Development

13+npm install # Install dependencies

14+npm test # Run tests

15+npm version patch|minor|major # Bump version

16+npm publish # Publish to npm

1417

15−---

18+# Component catalog

19+python scripts/generate_components_json.py # Update docs/components.json

1620

17−## Table of Contents

18−

19−1. [Query Performance](#query-performance) - **CRITICAL**

20− - 1.1 [Add Indexes on WHERE and JOIN Columns](#11-add-indexes-on-where-and-join-columns)

21− - 1.2 [Choose the Right Index Type for Your Data](#12-choose-the-right-index-type-for-your-data)

22− - 1.3 [Create Composite Indexes for Multi-Column Queries](#13-create-composite-indexes-for-multi-column-queries)

23− - 1.4 [Use Covering Indexes to Avoid Table Lookups](#14-use-covering-indexes-to-avoid-table-lookups)

24− - 1.5 [Use Partial Indexes for Filtered Queries](#15-use-partial-indexes-for-filtered-queries)

25−

26−2. [Connection Management](#connection-management) - **CRITICAL**

27− - 2.1 [Configure Idle Connection Timeouts](#21-configure-idle-connection-timeouts)

28− - 2.2 [Set Appropriate Connection Limits](#22-set-appropriate-connection-limits)

29− - 2.3 [Use Connection Pooling for All Applications](#23-use-connection-pooling-for-all-applications)

30− - 2.4 [Use Prepared Statements Correctly with Pooling](#24-use-prepared-statements-correctly-with-pooling)

31−

32−3. [Security & RLS](#security-rls) - **CRITICAL**

33− - 3.1 [Apply Principle of Least Privilege](#31-apply-principle-of-least-privilege)

34− - 3.2 [Enable Row Level Security for Multi-Tenant Data](#32-enable-row-level-security-for-multi-tenant-data)

35− - 3.3 [Optimize RLS Policies for Performance](#33-optimize-rls-policies-for-performance)

36−

37−4. [Schema Design](#schema-design) - **HIGH**

38− - 4.1 [Choose Appropriate Data Types](#41-choose-appropriate-data-types)

39− - 4.2 [Index Foreign Key Columns](#42-index-foreign-key-columns)

40− - 4.3 [Partition Large Tables for Better Performance](#43-partition-large-tables-for-better-performance)

41− - 4.4 [Select Optimal Primary Key Strategy](#44-select-optimal-primary-key-strategy)

42− - 4.5 [Use Lowercase Identifiers for Compatibility](#45-use-lowercase-identifiers-for-compatibility)

43−

44−5. [Concurrency & Locking](#concurrency-locking) - **MEDIUM-HIGH**

45− - 5.1 [Keep Transactions Short to Reduce Lock Contention](#51-keep-transactions-short-to-reduce-lock-contention)

46− - 5.2 [Prevent Deadlocks with Consistent Lock Ordering](#52-prevent-deadlocks-with-consistent-lock-ordering)

47− - 5.3 [Use Advisory Locks for Application-Level Locking](#53-use-advisory-locks-for-application-level-locking)

48− - 5.4 [Use SKIP LOCKED for Non-Blocking Queue Processing](#54-use-skip-locked-for-non-blocking-queue-processing)

49−

50−6. [Data Access Patterns](#data-access-patterns) - **MEDIUM**

51− - 6.1 [Batch INSERT Statements for Bulk Data](#61-batch-insert-statements-for-bulk-data)

52− - 6.2 [Eliminate N+1 Queries with Batch Loading](#62-eliminate-n1-queries-with-batch-loading)

53− - 6.3 [Use Cursor-Based Pagination Instead of OFFSET](#63-use-cursor-based-pagination-instead-of-offset)

54− - 6.4 [Use UPSERT for Insert-or-Update Operations](#64-use-upsert-for-insert-or-update-operations)

55−

56−7. [Monitoring & Diagnostics](#monitoring-diagnostics) - **LOW-MEDIUM**

57− - 7.1 [Enable pg_stat_statements for Query Analysis](#71-enable-pgstatstatements-for-query-analysis)

58− - 7.2 [Maintain Table Statistics with VACUUM and ANALYZE](#72-maintain-table-statistics-with-vacuum-and-analyze)

59− - 7.3 [Use EXPLAIN ANALYZE to Diagnose Slow Queries](#73-use-explain-analyze-to-diagnose-slow-queries)

60−

61−8. [Advanced Features](#advanced-features) - **LOW**

62− - 8.1 [Index JSONB Columns for Efficient Querying](#81-index-jsonb-columns-for-efficient-querying)

63− - 8.2 [Use tsvector for Full-Text Search](#82-use-tsvector-for-full-text-search)

64−

65−---

66−

67−## 1. Query Performance

68−

69−**Impact: CRITICAL**

70−

71−Slow queries, missing indexes, inefficient query plans. The most common source of Postgres performance issues.

72−

73−### 1.1 Add Indexes on WHERE and JOIN Columns

74−

75−**Impact: CRITICAL (100-1000x faster queries on large tables)**

76−

77−Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.

78−

79−**Incorrect (sequential scan on large table):**

80−

81−```sql

82−-- No index on customer_id causes full table scan

83−select * from orders where customer_id = 123;

84−

85−-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)

21+# Dashboard + API (Astro on Cloudflare Pages)

22+cd dashboard && npm run build # Build before deploy

23+npm run deploy # Deploy www + app.aitmpl.com via wrangler

8624 ```

8725

88−**Correct (index scan):**

26+> Deploys to production happen automatically via GitHub Actions on push to `main`

27+> (changes in `dashboard/**`). Manual deploy uses `wrangler pages deploy`, not Vercel.

8928

90−```sql

91−-- Create index on frequently filtered column

92−create index orders_customer_id_idx on orders (customer_id);

29+## Security Guidelines

9330

94−select * from orders where customer_id = 123;

31+### ⛔ CRITICAL: NEVER Hardcode Secrets or IDs

9532

96−-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)

97−-- Index the referencing column

98−create index orders_customer_id_idx on orders (customer_id);

33+**NEVER write API keys, tokens, passwords, project IDs, org IDs, or any identifier in code.** This includes Cloudflare account/project IDs, Supabase URLs, Discord IDs, database connection strings, and any other infrastructure identifier. ALL must go in `.env` (or Cloudflare secrets via `wrangler secret put`).

9934

100−select c.name, o.total

101−from customers c

102−join orders o on o.customer_id = c.id;

103−```

35+```javascript

36+// ❌ WRONG

37+const API_KEY = "AIzaSy...";

10438

105−For JOIN columns, always index the foreign key side:

106−

107−Reference: https://supabase.com/docs/guides/database/query-optimization

108−

109−---

110−

111−### 1.2 Choose the Right Index Type for Your Data

112−

113−**Impact: HIGH (10-100x improvement with correct index type)**

114−

115−Different index types excel at different query patterns. The default B-tree isn't always optimal.

116−

117−**Incorrect (B-tree for JSONB containment):**

118−

119−```sql

120−-- B-tree cannot optimize containment operators

121−create index products_attrs_idx on products (attributes);

122−select * from products where attributes @> '{"color": "red"}';

123−-- Full table scan - B-tree doesn't support @> operator

39+// ✅ CORRECT

40+const API_KEY = process.env.GOOGLE_API_KEY;

12441 ```

12542

126−**Correct (GIN for JSONB):**

43+**When creating scripts with API keys:**

44+1. Use `process.env` (Node.js) or `os.environ.get()` (Python)

45+2. Load from `.env` file using `dotenv`

46+3. Add variable to `.env.example` with placeholder

47+4. Verify `.env` is in `.gitignore`

12748

128−```sql

129−-- GIN supports @>, ?, ?&, ?| operators

130−create index products_attrs_idx on products using gin (attributes);

131−select * from products where attributes @> '{"color": "red"}';

132−-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL

133−create index users_created_idx on users (created_at);

49+**If you accidentally commit a secret:**

50+1. Revoke the key IMMEDIATELY

51+2. Generate new key

52+3. Update `.env`

53+4. Old key is compromised forever (git history)

13454

135−-- GIN: arrays, JSONB, full-text search

136−create index posts_tags_idx on posts using gin (tags);

55+## Component System

13756

138−-- BRIN: large time-series tables (10-100x smaller)

139−create index events_time_idx on events using brin (created_at);

57+### Component Types

14058

141−-- Hash: equality-only (slightly faster than B-tree for =)

142−create index sessions_token_idx on sessions using hash (token);

143−```

59+**Agents** (600+) - AI specialists for development tasks

60+**Commands** (200+) - Custom slash commands for workflows

61+**MCPs** (55+) - External service integrations

62+**Settings** (60+) - Claude Code configuration files

63+**Hooks** (39+) - Automation triggers

64+**Loops** (18+) - Autonomous agentic workflows (goal + interval + stop condition) that reference other components

65+**Templates** (14+) - Complete project configurations

14466

145−Index type guide:

67+### Installation Patterns

14668

147−Reference: https://www.postgresql.org/docs/current/indexes-types.html

69+```bash

70+# Single component

71+npx claude-code-templates@latest --agent frontend-developer

72+npx claude-code-templates@latest --command setup-testing

73+npx claude-code-templates@latest --hook automation/simple-notifications

74+npx claude-code-templates@latest --loop engineering/docs-sweep-loop # also installs the loop's referenced components

14875

149−---

76+# Batch installation

77+npx claude-code-templates@latest --agent security-auditor --command security-audit --setting read-only-mode

15078

151−### 1.3 Create Composite Indexes for Multi-Column Queries

152−

153−**Impact: HIGH (5-10x faster multi-column queries)**

154−

155−When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.

156−

157−**Incorrect (separate indexes require bitmap scan):**

158−

159−```sql

160−-- Two separate indexes

161−create index orders_status_idx on orders (status);

162−create index orders_created_idx on orders (created_at);

163−

164−-- Query must combine both indexes (slower)

165−select * from orders where status = 'pending' and created_at > '2024-01-01';

79+# Interactive mode

80+npx claude-code-templates@latest

16681 ```

16782

168−**Correct (composite index):**

83+### Component Development

16984

170−```sql

171−-- Single composite index (leftmost column first for equality checks)

172−create index orders_status_created_idx on orders (status, created_at);

85+#### Adding New Components

17386

174−-- Query uses one efficient index scan

175−select * from orders where status = 'pending' and created_at > '2024-01-01';

176−-- Good: status (=) before created_at (>)

177−create index idx on orders (status, created_at);

87+**CRITICAL: Use the component-reviewer agent for ALL component changes**

17888

179−-- Works for: WHERE status = 'pending'

180−-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

181−-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)

182−```

89+When adding or modifying components, you MUST use the `component-reviewer` subagent to validate the component before committing:

18390

184−**Column order matters** - place equality columns first, range columns last:

185−

186−Reference: https://www.postgresql.org/docs/current/indexes-multicolumn.html

187−

188−---

189−

190−### 1.4 Use Covering Indexes to Avoid Table Lookups

191−

192−**Impact: MEDIUM-HIGH (2-5x faster queries by eliminating heap fetches)**

193−

194−Covering indexes include all columns needed by a query, enabling index-only scans that skip the table entirely.

195−

196−**Incorrect (index scan + heap fetch):**

197−

198−```sql

199−create index users_email_idx on users (email);

200−

201−-- Must fetch name and created_at from table heap

202−select email, name, created_at from users where email = 'user@example.com';

20391 ```

204−

205−**Correct (index-only scan with INCLUDE):**

206−

207−```sql

208−-- Include non-searchable columns in the index

209−create index users_email_idx on users (email) include (name, created_at);

210−

211−-- All columns served from index, no table access needed

212−select email, name, created_at from users where email = 'user@example.com';

213−-- Searching by status, but also need customer_id and total

214−create index orders_status_idx on orders (status) include (customer_id, total);

215−

216−select status, customer_id, total from orders where status = 'shipped';

92+Use the component-reviewer agent to review [component-path]

21793 ```

21894

219−Use INCLUDE for columns you SELECT but don't filter on:

95+**Component Creation Workflow:**

22096

221−Reference: https://www.postgresql.org/docs/current/indexes-index-only-scans.html

97+1. Create component file in `cli-tool/components/{type}/{category}/{name}.md`

98+2. Use descriptive hyphenated names (kebab-case)

99+3. Include clear descriptions and usage examples

100+4. **REVIEW with component-reviewer agent** (validates format, security, naming)

101+5. Fix any issues identified by the reviewer

102+6. **TEST before generating/publishing**: ask the human in the session whether

103+ they want to test the newly created/modified component(s) first. Do NOT run

104+ `generate_components_json.py`, commit, or publish until testing is confirmed

105+ or explicitly skipped by the user.

106+7. Run `python scripts/generate_components_json.py` to update catalog

222107

223−---

108+**The component-reviewer agent checks:**

109+- ✅ Valid YAML frontmatter and required fields

110+- ✅ Proper kebab-case naming conventions

111+- ✅ No hardcoded secrets (API keys, tokens, passwords)

112+- ✅ Relative paths only (no absolute paths)

113+- ✅ Supporting files exist (for hooks with scripts)

114+- ✅ Clear, specific descriptions

115+- ✅ Correct category placement

116+- ✅ Security best practices

224117

225−### 1.5 Use Partial Indexes for Filtered Queries

226−

227−**Impact: HIGH (5-20x smaller indexes, faster writes and queries)**

228−

229−Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition.

230−

231−**Incorrect (full index includes irrelevant rows):**

232−

233−```sql

234−-- Index includes all rows, even soft-deleted ones

235−create index users_email_idx on users (email);

236−

237−-- Query always filters active users

238−select * from users where email = 'user@example.com' and deleted_at is null;

118+**Example Usage:**

239119 ```

120+# After creating a new agent

121+Use the component-reviewer agent to review cli-tool/components/agents/development-team/react-expert.md

240122

241−**Correct (partial index matches query filter):**

123+# Before committing hook changes

124+Use the component-reviewer agent to review cli-tool/components/hooks/git/prevent-force-push.json

242125

243−```sql

244−-- Index only includes active users

245−create index users_active_email_idx on users (email)

246−where deleted_at is null;

247−

248−-- Query uses the smaller, faster index

249−select * from users where email = 'user@example.com' and deleted_at is null;

250−-- Only pending orders (status rarely changes once completed)

251−create index orders_pending_idx on orders (created_at)

252−where status = 'pending';

253−

254−-- Only non-null values

255−create index products_sku_idx on products (sku)

256−where sku is not null;

126+# For PR reviews with multiple components

127+Use the component-reviewer agent to review all modified components in cli-tool/components/

257128 ```

258129

259−Common use cases for partial indexes:

130+The agent will provide prioritized feedback:

131+- **❌ Critical Issues**: Must fix before merge (security, missing fields)

132+- **⚠️ Warnings**: Should fix (clarity, best practices)

133+- **📋 Suggestions**: Nice to have improvements

260134

261−Reference: https://www.postgresql.org/docs/current/indexes-partial.html

135+#### Skill Security Scanning (SkillSpector)

262136

263−---

137+Skills under `cli-tool/components/skills/**` are scanned for security

138+vulnerabilities by [SkillSpector](https://github.com/NVIDIA/skillspector)

139+(NVIDIA, Apache-2.0) — a static analyzer with 64 vulnerability patterns

140+(prompt injection, data exfiltration, supply chain, dangerous code/AST, taint

141+tracking, YARA signatures, etc.). It runs in static-only mode (`--no-llm`), so

142+no API key or secret is required.

264143

265−## 2. Connection Management

144+Two GitHub Actions drive it, both via the batch orchestrator

145+`scripts/skillspector_scan.py`:

266146

267−**Impact: CRITICAL**

147+- **`.github/workflows/skill-security-scan.yml`** (PR) — scans only the skills

148+ changed in the PR (`git diff`), posts an idempotent report comment, and

149+ **blocks** the check if any changed skill scores HIGH/CRITICAL (risk score

150+ > 50). Uploads an aggregated SARIF to the Security tab.

151+- **`.github/workflows/skill-security-scan-all.yml`** (weekly + manual) — scans

152+ all skills, reports to the run summary and SARIF, and **never blocks**.

268153

269−Connection pooling, limits, and serverless strategies. Critical for applications with high concurrency or serverless deployments.

154+SkillSpector requires Python 3.12+ and is installed from NVIDIA's `main`

155+branch (`pip install git+https://github.com/NVIDIA/skillspector.git@main`); it

156+is not published to PyPI. Risk bands: 0-20 LOW, 21-50 MEDIUM, 51-80 HIGH,

157+81-100 CRITICAL.

270158

271−### 2.1 Configure Idle Connection Timeouts

159+#### Statuslines with Python Scripts

272160

273−**Impact: HIGH (Reclaim 30-50% of connection slots from idle clients)**

161+Statuslines can reference Python scripts that are auto-downloaded to `.claude/scripts/`:

274162

275−Idle connections waste resources. Configure timeouts to automatically reclaim them.

276−

277−**Incorrect (connections held indefinitely):**

278−

279−```sql

280−-- No timeout configured

281−show idle_in_transaction_session_timeout; -- 0 (disabled)

282−

283−-- Connections stay open forever, even when idle

284−select pid, state, state_change, query

285−from pg_stat_activity

286−where state = 'idle in transaction';

287−-- Shows transactions idle for hours, holding locks

163+```javascript

164+// In src/index.js:installIndividualSetting()

165+if (settingName.includes('statusline/')) {

166+ const pythonFileName = settingName.split('/')[1] + '.py';

167+ const pythonUrl = githubUrl.replace('.json', '.py');

168+ additionalFiles['.claude/scripts/' + pythonFileName] = {

169+ content: pythonContent,

170+ executable: true

171+ };

172+}

288173 ```

289174

290−**Correct (automatic cleanup of idle connections):**

175+### Publishing Workflow

291176

292−```ini

293−-- Terminate connections idle in transaction after 30 seconds

294−alter system set idle_in_transaction_session_timeout = '30s';

177+```bash

178+# 1. Update component catalog

179+python scripts/generate_components_json.py

295180

296−-- Terminate completely idle connections after 10 minutes

297−alter system set idle_session_timeout = '10min';

181+# 2. Run tests

182+npm test

298183

299−-- Reload configuration

300−select pg_reload_conf();

301−# pgbouncer.ini

302−server_idle_timeout = 60

303−client_idle_timeout = 300

304−```

184+# 3. Check current npm version and align local version

185+npm view claude-code-templates version # check latest on registry

186+# Edit package.json version to be one patch above the registry version

305187

306−For pooled connections, configure at the pooler level:

188+# 4. Commit version bump and push

189+git add package.json && git commit -m "chore: Bump version to X.Y.Z"

190+git push origin main

307191

308−Reference: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT

192+# 5. Publish to npm (requires granular access token with "Bypass 2FA" enabled)

193+npm config set //registry.npmjs.org/:_authToken=YOUR_GRANULAR_TOKEN

194+npm publish

195+npm config delete //registry.npmjs.org/:_authToken # always clean up after

309196

310−---

197+# 6. Tag the release

198+git tag vX.Y.Z && git push origin vX.Y.Z

311199

312−### 2.2 Set Appropriate Connection Limits

313−

314−**Impact: CRITICAL (Prevent database crashes and memory exhaustion)**

315−

316−Too many connections exhaust memory and degrade performance. Set limits based on available resources.

317−

318−**Incorrect (unlimited or excessive connections):**

319−

320−```sql

321−-- Default max_connections = 100, but often increased blindly

322−show max_connections; -- 500 (way too high for 4GB RAM)

323−

324−-- Each connection uses 1-3MB RAM

325−-- 500 connections * 2MB = 1GB just for connections!

326−-- Out of memory errors under load

200+# 7. Deploy website (dashboard on Cloudflare Pages)

201+# Automatic on push to main (GitHub Actions). Manual: from dashboard/ run `npm run deploy`

327202 ```

328203

329−**Correct (calculate based on resources):**

204+**npm Publishing Notes:**

205+- Classic npm tokens were revoked Dec 2025. Use **granular access tokens** from [npmjs.com/settings/~/tokens](https://www.npmjs.com/settings/~/tokens)

206+- The token must have **Read and Write** permissions for `claude-code-templates` and **"Bypass 2FA"** enabled

207+- Always remove the token from npm config after publishing (`npm config delete`)

208+- The local `package.json` version may drift from npm if published from CI — always check `npm view claude-code-templates version` first

209+- Never hardcode or commit tokens

330210

331−```sql

332−-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved

333−-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max

334−-- But practically, 100-200 is better for query performance

211+## API Architecture

335212

336−-- Recommended settings for 4GB RAM

337−alter system set max_connections = 100;

213+### Critical Endpoints

338214

339−-- Also set work_mem appropriately

340−-- work_mem * max_connections should not exceed 25% of RAM

341−alter system set work_mem = '8MB'; -- 8MB * 100 = 800MB max

342−select count(*), state from pg_stat_activity group by state;

343−```

215+API endpoints live as Astro API routes in `dashboard/src/pages/api/`:

344216

345−Monitor connection usage:

217+**`/api/track-download-supabase`** (CRITICAL)

218+- Tracks component downloads for analytics

219+- Used by CLI on every installation

220+- Database: Supabase (component_downloads table)

346221

347−Reference: https://supabase.com/docs/guides/platform/performance#connection-management

222+**`/api/discord/interactions`**

223+- Discord bot slash commands

224+- Features: /search, /info, /install, /popular

348225

349−---

226+**`/api/claude-code-check`**

227+- Monitors Claude Code releases

228+- Triggered every 30 minutes by the `cloudflare-workers/crons` Worker (not a Vercel cron)

229+- Database: Neon (claude_code_versions, claude_code_changes, discord_notifications_log, monitoring_metadata tables)

350230

351−### 2.3 Use Connection Pooling for All Applications

231+### Shared API Libraries

352232

353−**Impact: CRITICAL (Handle 10-100x more concurrent users)**

233+- `dashboard/src/lib/api/cors.ts` — CORS headers, `corsResponse()`, `jsonResponse()`

234+- `dashboard/src/lib/api/neon.ts` — Neon client factory

235+- `dashboard/src/lib/api/auth.ts` — Clerk JWT verification

236+- `dashboard/src/lib/api/changelog-parser.ts` — Claude Code changelog parser

354237

355−Postgres connections are expensive (1-3MB RAM each). Without pooling, applications exhaust connections under load.

238+### Emergency Rollback

356239

357−**Incorrect (new connection per request):**

358−

359−```sql

360−-- Each request creates a new connection

361−-- Application code: db.connect() per request

362−-- Result: 500 concurrent users = 500 connections = crashed database

363−

364−-- Check current connections

365−select count(*) from pg_stat_activity; -- 487 connections!

240+```bash

241+# List recent Pages deployments

242+npx wrangler pages deployment list --project-name=aitmpl-dashboard

243+# Roll back to a previous deployment

244+npx wrangler pages deployment rollback <deployment-id> --project-name=aitmpl-dashboard

366245 ```

367246

368−**Correct (connection pooling):**

247+## Cloudflare Workers

369248

370−```sql

371−-- Use a pooler like PgBouncer between app and database

372−-- Application connects to pooler, pooler reuses a small pool to Postgres

249+The `cloudflare-workers/` directory contains Cloudflare Worker projects that run independently from the dashboard Pages project.

373250

374−-- Configure pool_size based on: (CPU cores * 2) + spindle_count

375−-- Example for 4 cores: pool_size = 10

251+### crons

376252

377−-- Result: 500 concurrent users share 10 actual connections

378−select count(*) from pg_stat_activity; -- 10 connections

379−```

253+Replaces the old Vercel cron jobs. On a schedule it calls the dashboard API endpoints (which stay on Cloudflare Pages) with a shared `TRIGGER_SECRET`.

380254

381−Pool modes:

382−- **Transaction mode**: connection returned after each transaction (best for most apps)

383−- **Session mode**: connection held for entire session (needed for prepared statements, temp tables)

255+- `*/30 * * * *` → `/api/claude-code-check` (monitors Claude Code npm releases)

256+- `0 * * * *` → `/api/health-check` (hourly; was every 15 min on Vercel, reduced to save invocations)

384257

385−Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pooler

258+Errors and cron check-ins are reported to Sentry (`sentry.js`, DSN from the `aitmpl-workers` project — see Error Tracking below).

386259

387−---

388−

389−### 2.4 Use Prepared Statements Correctly with Pooling

390−

391−**Impact: HIGH (Avoid prepared statement conflicts in pooled environments)**

392−

393−Prepared statements are tied to individual database connections. In transaction-mode pooling, connections are shared, causing conflicts.

394−

395−**Incorrect (named prepared statements with transaction pooling):**

396−

397−```sql

398−-- Named prepared statement

399−prepare get_user as select * from users where id = $1;

400−

401−-- In transaction mode pooling, next request may get different connection

402−execute get_user(123);

403−-- ERROR: prepared statement "get_user" does not exist

260+```bash

261+cd cloudflare-workers/crons

262+npm run dev # Local dev

263+npx wrangler deploy # Deploy

404264 ```

405265

406−**Correct (use unnamed statements or session mode):**

266+**Secrets (Cloudflare):** `DASHBOARD_URL` (e.g. `https://www.aitmpl.com`), `TRIGGER_SECRET`, `SENTRY_DSN` (optional).

407267

408−```sql

409−-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)

410−-- The query is prepared and executed in a single protocol message

268+### docs-monitor (DECOMMISSIONED 2026-07)

411269

412−-- Option 2: Deallocate after use in transaction mode

413−prepare get_user as select * from users where id = $1;

414−execute get_user(123);

415−deallocate get_user;

270+Monitored https://code.claude.com/docs hourly with Telegram notifications. **Deleted from Cloudflare** to free a cron-trigger slot for the newsletter worker (the account's free plan allows 5 cron triggers total). The code remains in `cloudflare-workers/docs-monitor/` and can be redeployed if a slot frees up (`npx wrangler deploy`).

416271

417−-- Option 3: Use session mode pooling (port 5432 vs 6543)

418−-- Connection is held for entire session, prepared statements persist

419−-- Many drivers use prepared statements by default

420−-- Node.js pg: { prepare: false } to disable

421−-- JDBC: prepareThreshold=0 to disable

422−```

272+### pulse (Weekly KPI Report)

423273

424−Check your driver settings:

274+Collects metrics from GitHub, Discord, Supabase, npm, and Google Analytics every Sunday at 14:00 UTC and sends a consolidated report via Telegram.

425275

426−Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pool-modes

276+**Architecture:** Single `index.js` file (no npm dependencies at runtime). All source collectors, formatter, and Telegram sender in one file.

427277

428−---

278+**Cron:** `0 14 * * 0` (Sundays 14:00 UTC / 11:00 AM Chile)

429279

430−## 3. Security & RLS

280+```bash

281+cd cloudflare-workers/pulse

282+npm run dev # Local dev

283+npx wrangler deploy # Deploy

431284

432−**Impact: CRITICAL**

285+# Manual trigger

286+curl -X POST https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger \

287+ -H "Authorization: Bearer $TRIGGER_SECRET"

433288

434−Row-Level Security policies, privilege management, and authentication patterns.

289+# Test single source

290+curl -X POST "https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger?source=github" \

291+ -H "Authorization: Bearer $TRIGGER_SECRET"

435292

436−### 3.1 Apply Principle of Least Privilege

437−

438−**Impact: MEDIUM (Reduced attack surface, better audit trail)**

439−

440−Grant only the minimum permissions required. Never use superuser for application queries.

441−

442−**Incorrect (overly broad permissions):**

443−

444−```sql

445−-- Application uses superuser connection

446−-- Or grants ALL to application role

447−grant all privileges on all tables in schema public to app_user;

448−grant all privileges on all sequences in schema public to app_user;

449−

450−-- Any SQL injection becomes catastrophic

451−-- drop table users; cascades to everything

293+# Dry run (no Telegram)

294+curl -X POST "https://pulse-weekly-report.SUBDOMAIN.workers.dev/trigger?send=false" \

295+ -H "Authorization: Bearer $TRIGGER_SECRET"

452296 ```

453297

454−**Correct (minimal, specific grants):**

455−

456−```sql

457−-- Create role with no default privileges

458−create role app_readonly nologin;

459−

460−-- Grant only SELECT on specific tables

461−grant usage on schema public to app_readonly;

462−grant select on public.products, public.categories to app_readonly;

463−

464−-- Create role for writes with limited scope

465−create role app_writer nologin;

466−grant usage on schema public to app_writer;

467−grant select, insert, update on public.orders to app_writer;

468−grant usage on sequence orders_id_seq to app_writer;

469−-- No DELETE permission

470−

471−-- Login role inherits from these

472−create role app_user login password 'xxx';

473−grant app_writer to app_user;

474−-- Revoke default public access

475−revoke all on schema public from public;

476−revoke all on all tables in schema public from public;

298+**Secrets (Cloudflare):**

299+```bash

300+TELEGRAM_BOT_TOKEN # Shared with docs-monitor

301+TELEGRAM_CHAT_ID # Shared with docs-monitor

302+GITHUB_TOKEN # GitHub PAT (public_repo scope)

303+SUPABASE_URL # Supabase project URL

304+SUPABASE_SERVICE_ROLE_KEY # Supabase service role key

305+DISCORD_BOT_TOKEN # Discord bot token

306+DISCORD_GUILD_ID # Discord server ID

307+TRIGGER_SECRET # For manual /trigger endpoint

308+GA_PROPERTY_ID # GA4 property ID (optional)

309+GA_SERVICE_ACCOUNT_JSON # Base64 service account (optional)

477310 ```

478311

479−Revoke public defaults:

312+**Graceful degradation:** Each source catches its own errors. Missing secrets or API failures show `⚠️ Unavailable` instead of crashing the report. Failed collectors are also reported to Sentry via `sentry.js` (see Error Tracking below). The Vercel collector was removed (2026-07) since the dashboard no longer deploys to Vercel.

480313

481−Reference: https://supabase.com/blog/postgres-roles-and-privileges

314+### newsletter (Weekly Community Components Email)

482315

483−---

316+Composes and sends a simple weekly email via Resend featuring trending components (one Skill, Agent, MCP, Hook and Setting per send, in that fixed order). Selection is weighted-random by recent downloads and the copy (subject, catalog intro, per-component sentences, stats cited, closer) rotates from pools so no two emails read the same. Body is plain text plus a minimal HTML version (bold + underlined component titles, clickable component links). Data comes from the live `trending-data.json` + `components.json`.

484317

485−### 3.2 Enable Row Level Security for Multi-Tenant Data

318+**Delivery:** Resend **Broadcast** targeting the segment in `RESEND_SEGMENT_ID` — Resend injects the per-recipient unsubscribe link (`{{{RESEND_UNSUBSCRIBE_URL}}}` placeholder in the body) and manages the suppression list automatically. Replies go to `NEWSLETTER_REPLY_TO`. The segment is the safety gate: point it at a pilot segment for tests or the full-audience segment for community-wide sends. Open/click tracking is enabled on the `aitmpl.com` domain with tracking subdomain `track.aitmpl.com` (metrics per broadcast at resend.com/broadcasts). Cron: Sundays 16:00 UTC (slot freed by decommissioning docs-monitor). `GET /preview?format=text` composes without sending; `POST /trigger` sends (`?send=false` for dry run).

486319

487−**Impact: CRITICAL (Database-enforced tenant isolation, prevent data leaks)**

320+```bash

321+cd cloudflare-workers/newsletter

322+npm run dev # Local dev

323+npx wrangler deploy # Deploy

488324

489−Row Level Security (RLS) enforces data access at the database level, ensuring users only see their own data.

325+# Preview content without sending (repeat to see the copy rotate)

326+curl "https://aitmpl-newsletter.SUBDOMAIN.workers.dev/preview?format=text" \

327+ -H "Authorization: Bearer $TRIGGER_SECRET"

490328

491−**Incorrect (application-level filtering only):**

492−

493−```sql

494−-- Relying only on application to filter

495−select * from orders where user_id = $current_user_id;

496−

497−-- Bug or bypass means all data is exposed!

498−select * from orders; -- Returns ALL orders

329+# Real send: creates + sends a Broadcast to the segment in RESEND_SEGMENT_ID

330+curl -X POST "https://aitmpl-newsletter.SUBDOMAIN.workers.dev/trigger" \

331+ -H "Authorization: Bearer $TRIGGER_SECRET"

499332 ```

500333

501−**Correct (database-enforced RLS):**

334+**Secrets (Cloudflare):** `RESEND_API_KEY` (full access — broadcasts/segments), `RESEND_SEGMENT_ID`, `NEWSLETTER_REPLY_TO`, `TRIGGER_SECRET`, `SENTRY_DSN` (optional). Public vars in `wrangler.toml [vars]`: `DASHBOARD_URL`, `RESEND_FROM_EMAIL` (`daniel.avila@aitmpl.com`).

502335

503−```sql

504−-- Enable RLS on the table

505−alter table orders enable row level security;

336+## Error Tracking (Sentry)

506337

507−-- Create policy for users to see only their orders

508−create policy orders_user_policy on orders

509− for all

510− using (user_id = current_setting('app.current_user_id')::bigint);

338+Free-tier Sentry, added to close the gap where automated cron/worker failures

339+were previously invisible. No official `@sentry/*` SDK is used anywhere —

340+every surface has its own tiny dependency-free client that posts directly to

341+the Sentry envelope API via `fetch()`, matching this repo's zero-dependency

342+worker style and avoiding Cloudflare Pages SSR friction with `@sentry/astro`.

511343

512−-- Force RLS even for table owners

513−alter table orders force row level security;

344+**Status as of 2026-07-04: all 3 projects live and verified end-to-end** (each

345+confirmed with a manual test event returning HTTP 200 from Sentry and

346+appearing in its Issues dashboard).

514347

515−-- Set user context and query

516−set app.current_user_id = '123';

517−select * from orders; -- Only returns orders for user 123

518−create policy orders_user_policy on orders

519− for all

520− to authenticated

521− using (user_id = auth.uid());

522−```

348+- ✅ **Cloudflare Workers** (Sentry project `aitmpl-workers`) — `SENTRY_DSN`

349+ secret set on all 3 workers (`aitmpl-crons`, `pulse-weekly-report`,

350+ `claude-docs-monitor`) via `wrangler secret put SENTRY_DSN`.

351+- ✅ **Dashboard** (Sentry project `aitmpl-dashboard`) — `SENTRY_DSN` set as a

352+ Cloudflare Pages secret (`wrangler pages secret put SENTRY_DSN

353+ --project-name=aitmpl-dashboard`). Wired into `captureApiError()` calls in

354+ `claude-code-check`, `health-check`, and the three `track-*` endpoints.

355+- ✅ **CLI** (Sentry project `aitmpl-cli`) — the DSN is public by design

356+ (send-only, not a secret) and ships **hardcoded as the default** in

357+ `cli-tool/src/error-reporting.js` (`DEFAULT_SENTRY_DSN`, overridable via

358+ `CCT_SENTRY_DSN` for testing against a different project). Reporting

359+ itself stays **opt-in**: requires the end user to set

360+ `CCT_ERROR_REPORTING=true`, and always defers to the existing

361+ `CCT_NO_TRACKING`/`CCT_NO_ANALYTICS`/`CI` opt-outs.

523362

524−Policy for authenticated role:

363+**Not yet configured (any surface):** Sentry alert rules to Discord/Telegram,

364+and Cron Monitors dashboards for the workers' scheduled check-ins (the

365+`checkIn()` calls already send `in_progress`/`ok`/`error` events — a Monitor

366+just needs to be created in the Sentry UI with matching slugs:

367+`claude-code-check`, `health-check`, `pulse-weekly-report`, `docs-monitor`).

525368

526−Reference: https://supabase.com/docs/guides/database/postgres/row-level-security

369+**Files:** `cloudflare-workers/{crons,pulse,docs-monitor}/sentry.js` (workers),

370+`dashboard/src/lib/api/error-tracking.ts` (dashboard), `cli-tool/src/error-reporting.js` (CLI).

527371

528−---

372+## Dashboard (www.aitmpl.com)

529373

530−### 3.3 Optimize RLS Policies for Performance

374+Astro + React + Tailwind dashboard serving both `www.aitmpl.com` and `app.aitmpl.com`. Clerk auth for user collections. Source lives in `dashboard/`. All API endpoints are Astro API routes in the same project.

531375

532−**Impact: HIGH (5-10x faster RLS queries with proper patterns)**

376+### Architecture

533377

534−Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.

378+- **Framework**: Astro 5 with React islands, Tailwind v4, `output: 'server'`, `@astrojs/cloudflare` adapter (`mode: 'directory'`)

379+- **Hosting**: Cloudflare Pages (project `aitmpl-dashboard`), SSR on Workers runtime

380+- **Auth**: Clerk (`window.Clerk` global, no ClerkProvider per island)

381+- **Data**: `components.json` and `trending-data.json` served from `dashboard/public/` (same-origin)

382+- **APIs**: All endpoints in `dashboard/src/pages/api/` (Astro API routes, no separate serverless project)

535383

536−**Incorrect (function called for every row):**

384+### Featured Pages (`/featured/[slug]`)

537385

538−```sql

539−create policy orders_policy on orders

540− using (auth.uid() = user_id); -- auth.uid() called per row!

386+Featured partner integrations shown on the dashboard homepage. Two files to edit:

541387

542−-- With 1M rows, auth.uid() is called 1M times

543−```

388+**`dashboard/src/lib/constants.ts`** — `FEATURED_ITEMS` array. Each entry has:

389+- `name`, `description`, `logo`, `url` (`/featured/slug`), `tag`, `tagColor`, `category`

390+- `ctaLabel`, `ctaUrl`, `websiteUrl`

391+- `installCommand` — shown in the sidebar Quick Install box

392+- `metadata` — key/value pairs shown in the Details sidebar (e.g. `Components: '8'`)

393+- `links` — sidebar links list

544394

545−**Correct (wrap functions in SELECT):**

395+**`dashboard/src/pages/featured/[slug].astro`** — Content for each slug rendered via `{slug === 'brightdata' && (...)}` blocks. Each block contains the full HTML content for that partner page.

546396

547−```sql

548−create policy orders_policy on orders

549− using ((select auth.uid()) = user_id); -- Called once, cached

397+**When adding a skill to a featured page:**

398+1. Add a new card `<div class="flex gap-3 ...">` inside the Skills Layer section of the relevant `{slug === '...'}` block

399+2. Update `installCommand` in `constants.ts` to include the new skill

400+3. Increment `metadata.Components` count in `constants.ts`

550401

551−-- 100x+ faster on large tables

552−-- Create helper function (runs as definer, bypasses RLS)

553−create or replace function is_team_member(team_id bigint)

554−returns boolean

555−language sql

556−security definer

557−set search_path = ''

558−as $$

559− select exists (

560− select 1 from public.team_members

561− where team_id = $1 and user_id = (select auth.uid())

562− );

563−$$;

402+Current featured slugs: `brightdata`, `neon-instagres`, `claudekit`, `braingrid`

564403

565−-- Use in policy (indexed lookup, not per-row check)

566−create policy team_orders_policy on orders

567− using ((select is_team_member(team_id)));

568−create index orders_user_id_idx on orders (user_id);

569−```

404+### Cloudflare Pages Project Setup

570405

571−Use security definer functions for complex checks:

572−Always add indexes on columns used in RLS policies:

406+A single Cloudflare Pages project (`aitmpl-dashboard`) serves all domains. Config lives in `dashboard/wrangler.toml`:

573407

574−Reference: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations

409+|---------|---------|----------------|--------------|

575411

576−---

412+`wrangler.toml` sets `pages_build_output_dir = "./dist"`, `compatibility_flags = ["nodejs_compat"]`, and the `PUBLIC_*` build-time vars in `[vars]`. Secrets are set via the Cloudflare Dashboard or `wrangler pages secret put`.

577413

578−## 4. Schema Design

414+### Deployment

579415

580−**Impact: HIGH**

416+**ALWAYS use the deployer agent (`.claude/agents/deployer.md`) for all deployments.** It runs pre-deploy checks (auth, git status, build) and handles the full pipeline safely. Never deploy manually.

581417

582−Table design, index strategies, partitioning, and data type selection. Foundation for long-term performance.

583−

584−### 4.1 Choose Appropriate Data Types

585−

586−**Impact: HIGH (50% storage reduction, faster comparisons)**

587−

588−Using the right data types reduces storage, improves query performance, and prevents bugs.

589−

590−**Incorrect (wrong data types):**

591−

592−```sql

593−create table users (

594− id int, -- Will overflow at 2.1 billion

595− email varchar(255), -- Unnecessary length limit

596− created_at timestamp, -- Missing timezone info

597− is_active varchar(5), -- String for boolean

598− price varchar(20) -- String for numeric

599−);

418+```bash

419+npm run deploy # Build + `wrangler pages deploy dist` for www + app.aitmpl.com

420+npm run deploy:dashboard # Same as above

600421 ```

601422

602−**Correct (appropriate data types):**

423+**CI/CD**: Pushes to `main` auto-deploy via GitHub Actions (`.github/workflows/deploy.yml`):

424+- Changes in `dashboard/**` trigger a build and `wrangler pages deploy dist --project-name=aitmpl-dashboard`

603425

604−```sql

605−create table users (

606− id bigint generated always as identity primary key, -- 9 quintillion max

607− email text, -- No artificial limit, same performance as varchar

608− created_at timestamptz, -- Always store timezone-aware timestamps

609− is_active boolean default true, -- 1 byte vs variable string length

610− price numeric(10,2) -- Exact decimal arithmetic

611−);

612−-- IDs: use bigint, not int (future-proofing)

613−-- Strings: use text, not varchar(n) unless constraint needed

614−-- Time: use timestamptz, not timestamp

615−-- Money: use numeric, not float (precision matters)

616−-- Enums: use text with check constraint or create enum type

617−```

426+**Required GitHub Secrets** (Settings > Secrets > Actions):

427+- `CLOUDFLARE_API_TOKEN` — Cloudflare API token with Pages edit permission

428+- `CLOUDFLARE_ACCOUNT_ID` — Cloudflare account ID

618429

619−Key guidelines:

430+### Environment Variables (Cloudflare)

620431

621−Reference: https://www.postgresql.org/docs/current/datatype.html

432+`PUBLIC_*` vars are build-time and live in `dashboard/wrangler.toml` `[vars]` (and are also passed to the GitHub Actions build step). Everything else is a Cloudflare secret (`wrangler pages secret put <NAME>` or the Pages dashboard):

622433

623−---

434+```bash

435+# Clerk

436+PUBLIC_CLERK_PUBLISHABLE_KEY=xxx # [vars] — build-time

437+CLERK_SECRET_KEY=xxx # secret

624438

625−### 4.2 Index Foreign Key Columns

439+# Data

440+PUBLIC_COMPONENTS_JSON_URL=/components.json # [vars] — build-time

626441

627−**Impact: HIGH (10-100x faster JOINs and CASCADE operations)**

442+# GitHub OAuth

443+PUBLIC_GITHUB_CLIENT_ID=xxx # [vars] — build-time

444+GITHUB_CLIENT_SECRET=xxx # secret

628445

629−Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.

446+# Supabase (download tracking)

447+SUPABASE_URL=https://xxx.supabase.co # secret

448+SUPABASE_SERVICE_ROLE_KEY=xxx # secret

630449

631−**Incorrect (unindexed foreign key):**

450+# Neon Database

451+NEON_DATABASE_URL=postgresql://user:pass@host/db?sslmode=require # secret

632452

633−```sql

634−create table orders (

635− id bigint generated always as identity primary key,

636− customer_id bigint references customers(id) on delete cascade,

637− total numeric(10,2)

638−);

639−

640−-- No index on customer_id!

641−-- JOINs and ON DELETE CASCADE both require full table scan

642−select * from orders where customer_id = 123; -- Seq Scan

643−delete from customers where id = 123; -- Locks table, scans all orders

453+# Discord

454+DISCORD_APP_ID=xxx # secret

455+DISCORD_BOT_TOKEN=xxx # secret

456+DISCORD_PUBLIC_KEY=xxx # secret

457+DISCORD_WEBHOOK_URL_CHANGELOG=https://discord.com/api/webhooks/xxx # secret

644458 ```

645459

646−**Correct (indexed foreign key):**

460+### Known Issues & Solutions

647461

648−```sql

649−create table orders (

650− id bigint generated always as identity primary key,

651− customer_id bigint references customers(id) on delete cascade,

652− total numeric(10,2)

653−);

462+**Node built-ins in SSR**

463+- The Cloudflare Workers runtime does not expose Node's `fs`/`path`/etc. by default. `astro.config.mjs` enables `nodejs_compat` (via `wrangler.toml`) and externalizes `node:fs`, `node:path`, `node:url`, `node:stream` in SSR. Avoid adding new hard dependencies on Node-only APIs in server code.

654464

655−-- Always index the FK column

656−create index orders_customer_id_idx on orders (customer_id);

465+**`react-dom/server` on Cloudflare**

466+- `astro.config.mjs` aliases `react-dom/server` to `react-dom/server.node` and marks `react-dom` as `noExternal` at build time so React SSR works on the Workers runtime. Don't remove this alias.

657467

658−-- Now JOINs and cascades are fast

659−select * from orders where customer_id = 123; -- Index Scan

660−delete from customers where id = 123; -- Uses index, fast cascade

661−select

662− conrelid::regclass as table_name,

663− a.attname as fk_column

664−from pg_constraint c

665−join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)

666−where c.contype = 'f'

667− and not exists (

668− select 1 from pg_index i

669− where i.indrelid = c.conrelid and a.attnum = any(i.indkey)

670− );

671−```

468+### Local Development

672469

673−Find missing FK indexes:

674−

675−Reference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

676−

677−---

678−

679−### 4.3 Partition Large Tables for Better Performance

680−

681−**Impact: MEDIUM-HIGH (5-20x faster queries and maintenance on large tables)**

682−

683−Partitioning splits a large table into smaller pieces, improving query performance and maintenance operations.

684−

685−**Incorrect (single large table):**

686−

687−```sql

688−create table events (

689− id bigint generated always as identity,

690− created_at timestamptz,

691− data jsonb

692−);

693−

694−-- 500M rows, queries scan everything

695−select * from events where created_at > '2024-01-01'; -- Slow

696−vacuum events; -- Takes hours, locks table

470+```bash

471+cd dashboard

472+npm install

473+npx astro dev --port 4321 # Dashboard + APIs at http://localhost:4321

697474 ```

698475

699−**Correct (partitioned by time range):**

476+## Data Files

700477

701−```sql

702−create table events (

703− id bigint generated always as identity,

704− created_at timestamptz not null,

705− data jsonb

706−) partition by range (created_at);

478+### Component Catalog

707479

708−-- Create partitions for each month

709−create table events_2024_01 partition of events

710− for values from ('2024-01-01') to ('2024-02-01');

480+- `docs/components.json` — Full generated catalog (source of truth), keeps `content` and `security` fields (needed by the legacy static site)

481+- `dashboard/public/components.json` — Dashboard copy, **without** `content`/`security` (lighter payload; dashboard doesn't need them)

482+- `dashboard/public/counts.json` — Per-type counts only (e.g. `{"agents": 421, ...}`), used by the sidebar/plugins pages instead of loading the full catalog

483+- `dashboard/public/components/{type}.json` — One file per component type (agents.json, commands.json, etc.), loaded on demand by `ComponentGrid.tsx` for the active tab

484+- `dashboard/public/search-index.json` — Flat array for `SearchModal.tsx`

485+- `dashboard/public/component-content/{type}/{slug}.json` — Full per-component content (incl. markdown body), fetched on demand when a component's detail view or PR flow needs it

486+- `dashboard/public/trending-data.json` — Trending/download stats

711487

712−create table events_2024_02 partition of events

713− for values from ('2024-02-01') to ('2024-03-01');

488+All of the above are served as static Cloudflare Pages assets with

489+`cache-control: public, max-age=86400, stale-while-revalidate=3600` (see

490+`dashboard/public/_headers`).

714491

715−-- Queries only scan relevant partitions

716−select * from events where created_at > '2024-01-15'; -- Only scans events_2024_01+

492+### Data Flow

717493

718−-- Drop old data instantly

719−drop table events_2023_01; -- Instant vs DELETE taking hours

720−```

494+1. `scripts/generate_components_json.py` scans `cli-tool/components/`

495+2. Generates `docs/components.json` (full, with `content`/`security`) and the split dashboard artifacts (`dashboard/public/components.json`, `counts.json`, `components/{type}.json`, `search-index.json`, `component-content/{type}/{slug}.json`) — these two writes are decoupled, so the dashboard payload stays lean without touching the legacy catalog

496+3. Dashboard islands (`ComponentGrid.tsx`, `SearchModal.tsx`, `Sidebar.astro`, `SendToRepoModal.tsx`) load the split artifacts instead of the full catalog

497+4. Download tracking via `/api/track-download-supabase`

721498

722−When to partition:

723−- Tables > 100M rows

724−- Time-series data with date-based queries

725−- Need to efficiently drop old data

499+### Plugins & Marketplaces Catalog

726500

727−Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html

501+- `scripts/generate_plugins_json.py` — scans the repos listed in `REPOS` via the `gh` CLI (needs `gh auth login`) and writes `dashboard/public/plugins.json`. This is a **manual, offline step** — it does not run during `npm run build` or CI/CD, so re-running it never affects deploy time.

502+- For each marketplace it records `plugins_list[].components` (counts per type) and `plugins_list[].components_items` (`{name, description}` per command/agent/skill/hook/mcp/lsp, description parsed from the item's frontmatter). The dashboard's `/plugins/[slug].astro` page renders this through `MarketplacePluginsList.tsx`, which shows a search box and a "view details" modal per plugin.

503+- **`max_local_scans = 50`** in `extract_marketplace_plugins_detail()` caps how many *locally-sourced* plugins (i.e. `source: "./plugins/..."` within the marketplace's own repo) get scanned for real component names/descriptions, per marketplace, to bound GitHub API calls. Plugins beyond that cap (or plugins hosted in an external repo, which are never scanned) fall back to showing only tag badges in the modal, with no itemized breakdown — this is a graceful degradation, not an error.

504+ - As of 2026-07-11, `anthropics/claude-plugins-official` alone has 51 locally-sourced plugins (out of 255 total), i.e. already at the edge of this cap. Bump `max_local_scans` if more complete coverage is needed — GitHub's rate limit (5000 req/hour authenticated) is not the constraint, wall-clock run time is (each item now costs 1 extra API call to fetch its file content for the description).

728505

729−---

506+### Legacy Static Site (docs/)

730507

731−### 4.4 Select Optimal Primary Key Strategy

508+The `docs/` directory contains the old static HTML site (no longer deployed to www). Blog articles in `docs/blog/` are still referenced externally.

732509

733−**Impact: HIGH (Better index locality, reduced fragmentation)**

510+### Blog Article Creation

734511

735−Primary key choice affects insert performance, index size, and replication

736−efficiency.

512+Use the CLI skill to create blog articles:

737513

738−**Incorrect (problematic PK choices):**

739−

740−```sql

741−-- identity is the SQL-standard approach

742−create table users (

743− id serial primary key -- Works, but IDENTITY is recommended

744−);

745−

746−-- Random UUIDs (v4) cause index fragmentation

747−create table orders (

748− id uuid default gen_random_uuid() primary key -- UUIDv4 = random = scattered inserts

749−);

514+```bash

515+/create-blog-article @cli-tool/components/{type}/{category}/{name}.json

750516 ```

751517

752−**Correct (optimal PK strategies):**

518+This automatically:

519+1. Generates AI cover image

520+2. Creates HTML with SEO optimization

521+3. Updates `docs/blog/blog-articles.json`

753522

754−```sql

755−-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)

756−create table users (

757− id bigint generated always as identity primary key

758−);

523+## Code Standards

759524

760−-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)

761−-- Requires pg_uuidv7 extension: create extension pg_uuidv7;

762−create table orders (

763− id uuid default uuid_generate_v7() primary key -- Time-ordered, no fragmentation

764−);

525+### Path Handling

526+- Use relative paths: `.claude/scripts/`, `.claude/hooks/`

527+- Never hardcode absolute paths or home directories

528+- Use `path.join()` for cross-platform compatibility

765529

766−-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)

767−create table events (

768− id text default concat(

769− to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),

770− gen_random_uuid()::text

771− ) primary key

772−);

773−```

530+### Naming Conventions

531+- Files: `kebab-case.js`, `PascalCase.js` (for classes)

532+- Functions/Variables: `camelCase`

533+- Constants: `UPPER_SNAKE_CASE`

534+- Components: `hyphenated-names`

774535

775−Guidelines:

776−- Single database: `bigint identity` (sequential, 8 bytes, SQL-standard)

777−- Distributed/exposed IDs: UUIDv7 (requires pg_uuidv7) or ULID (time-ordered, no

778− fragmentation)

779−- `serial` works but `identity` is SQL-standard and preferred for new

780− applications

781−- Avoid random UUIDs (v4) as primary keys on large tables (causes index

782− fragmentation)

783−[Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY)

536+### Error Handling

537+- Use try/catch for async operations

538+- Provide helpful error messages

539+- Log errors with context

540+- Implement fallback mechanisms

784541

785−---

542+## Testing

786543

787−### 4.5 Use Lowercase Identifiers for Compatibility

788−

789−**Impact: MEDIUM (Avoid case-sensitivity bugs with tools, ORMs, and AI assistants)**

790−

791−PostgreSQL folds unquoted identifiers to lowercase. Quoted mixed-case identifiers require quotes forever and cause issues with tools, ORMs, and AI assistants that may not recognize them.

792−

793−**Incorrect (mixed-case identifiers):**

794−

795−```sql

796−-- Quoted identifiers preserve case but require quotes everywhere

797−CREATE TABLE "Users" (

798− "userId" bigint PRIMARY KEY,

799− "firstName" text,

800− "lastName" text

801−);

802−

803−-- Must always quote or queries fail

804−SELECT "firstName" FROM "Users" WHERE "userId" = 1;

805−

806−-- This fails - Users becomes users without quotes

807−SELECT firstName FROM Users;

808−-- ERROR: relation "users" does not exist

544+```bash

545+npm test # Run all tests

546+npm run test:watch # Watch mode

547+npm run test:coverage # Coverage report

809548 ```

810549

811−**Correct (lowercase snake_case):**

550+Aim for 70%+ test coverage. Test critical paths and error handling.

812551

813−```sql

814−-- Unquoted lowercase identifiers are portable and tool-friendly

815−CREATE TABLE users (

816− user_id bigint PRIMARY KEY,

817− first_name text,

818− last_name text

819−);

552+## Common Issues

820553

821−-- Works without quotes, recognized by all tools

822−SELECT first_name FROM users WHERE user_id = 1;

823−-- ORMs often generate quoted camelCase - configure them to use snake_case

824−-- Migrations from other databases may preserve original casing

825−-- Some GUI tools quote identifiers by default - disable this

554+**API endpoint returns 404 after deploy**

555+- API routes must be in `dashboard/src/pages/api/` as Astro API routes

556+- Export named HTTP methods: `export const POST: APIRoute`, `export const GET: APIRoute`

826557

827−-- If stuck with mixed-case, create views as a compatibility layer

828−CREATE VIEW users AS SELECT "userId" AS user_id, "firstName" AS first_name FROM "Users";

829−```

558+**Download tracking not working**

559+- Check Cloudflare Pages logs: `npx wrangler pages deployment tail --project-name=aitmpl-dashboard`

560+- Verify environment variables / secrets in the Cloudflare Pages dashboard

561+- Test endpoint manually with curl

830562

831−Common sources of mixed-case identifiers:

563+**Components not updating on website**

564+- Run `python scripts/generate_components_json.py` (writes both `docs/components.json` and the split `dashboard/public/` artifacts directly — no manual copy step)

565+- Deploy and clear browser cache (artifacts are cached 24h at the edge, see `dashboard/public/_headers`)

832566

833−Reference: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

567+## Important Notes

834568

835−---

836−

837−## 5. Concurrency & Locking

838−

839−**Impact: MEDIUM-HIGH**

840−

841−Transaction management, isolation levels, deadlock prevention, and lock contention patterns.

842−

843−### 5.1 Keep Transactions Short to Reduce Lock Contention

844−

845−**Impact: MEDIUM-HIGH (3-5x throughput improvement, fewer deadlocks)**

846−

847−Long-running transactions hold locks that block other queries. Keep transactions as short as possible.

848−

849−**Incorrect (long transaction with external calls):**

850−

851−```sql

852−begin;

853−select * from orders where id = 1 for update; -- Lock acquired

854−

855−-- Application makes HTTP call to payment API (2-5 seconds)

856−-- Other queries on this row are blocked!

857−

858−update orders set status = 'paid' where id = 1;

859−commit; -- Lock held for entire duration

860−```

861−

862−**Correct (minimal transaction scope):**

863−

864−```sql

865−-- Validate data and call APIs outside transaction

866−-- Application: response = await paymentAPI.charge(...)

867−

868−-- Only hold lock for the actual update

869−begin;

870−update orders

871−set status = 'paid', payment_id = $1

872−where id = $2 and status = 'pending'

873−returning *;

874−commit; -- Lock held for milliseconds

875−-- Abort queries running longer than 30 seconds

876−set statement_timeout = '30s';

877−

878−-- Or per-session

879−set local statement_timeout = '5s';

880−```

881−

882−Use `statement_timeout` to prevent runaway transactions:

883−

884−Reference: https://www.postgresql.org/docs/current/tutorial-transactions.html

885−

886−---

887−

888−### 5.2 Prevent Deadlocks with Consistent Lock Ordering

889−

890−**Impact: MEDIUM-HIGH (Eliminate deadlock errors, improve reliability)**

891−

892−Deadlocks occur when transactions lock resources in different orders. Always

893−acquire locks in a consistent order.

894−

895−**Incorrect (inconsistent lock ordering):**

896−

897−```sql

898−-- Transaction A -- Transaction B

899−begin; begin;

900−update accounts update accounts

901−set balance = balance - 100 set balance = balance - 50

902−where id = 1; where id = 2; -- B locks row 2

903−

904−update accounts update accounts

905−set balance = balance + 100 set balance = balance + 50

906−where id = 2; -- A waits for B where id = 1; -- B waits for A

907−

908−-- DEADLOCK! Both waiting for each other

909−```

910−

911−**Correct (lock rows in consistent order first):**

912−

913−```sql

914−-- Explicitly acquire locks in ID order before updating

915−begin;

916−select * from accounts where id in (1, 2) order by id for update;

917−

918−-- Now perform updates in any order - locks already held

919−update accounts set balance = balance - 100 where id = 1;

920−update accounts set balance = balance + 100 where id = 2;

921−commit;

922−-- Single statement acquires all locks atomically

923−begin;

924−update accounts

925−set balance = balance + case id

926− when 1 then -100

927− when 2 then 100

928−end

929−where id in (1, 2);

930−commit;

931−-- Check for recent deadlocks

932−select * from pg_stat_database where deadlocks > 0;

933−

934−-- Enable deadlock logging

935−set log_lock_waits = on;

936−set deadlock_timeout = '1s';

937−```

938−

939−Alternative: use a single statement to update atomically:

940−Detect deadlocks in logs:

941−[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)

942−

943−---

944−

945−### 5.3 Use Advisory Locks for Application-Level Locking

946−

947−**Impact: MEDIUM (Efficient coordination without row-level lock overhead)**

948−

949−Advisory locks provide application-level coordination without requiring database rows to lock.

950−

951−**Incorrect (creating rows just for locking):**

952−

953−```sql

954−-- Creating dummy rows to lock on

955−create table resource_locks (

956− resource_name text primary key

957−);

958−

959−insert into resource_locks values ('report_generator');

960−

961−-- Lock by selecting the row

962−select * from resource_locks where resource_name = 'report_generator' for update;

963−```

964−

965−**Correct (advisory locks):**

966−

967−```sql

968−-- Session-level advisory lock (released on disconnect or unlock)

969−select pg_advisory_lock(hashtext('report_generator'));

970−-- ... do exclusive work ...

971−select pg_advisory_unlock(hashtext('report_generator'));

972−

973−-- Transaction-level lock (released on commit/rollback)

974−begin;

975−select pg_advisory_xact_lock(hashtext('daily_report'));

976−-- ... do work ...

977−commit; -- Lock automatically released

978−-- Returns immediately with true/false instead of waiting

979−select pg_try_advisory_lock(hashtext('resource_name'));

980−

981−-- Use in application

982−if (acquired) {

983− -- Do work

984− select pg_advisory_unlock(hashtext('resource_name'));

985−} else {

986− -- Skip or retry later

987−}

988−```

989−

990−Try-lock for non-blocking operations:

991−

992−Reference: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

993−

994−---

995−

996−### 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing

997−

998−**Impact: MEDIUM-HIGH (10x throughput for worker queues)**

999−

1000−When multiple workers process a queue, SKIP LOCKED allows workers to process different rows without waiting.

1001−

1002−**Incorrect (workers block each other):**

1003−

1004−```sql

1005−-- Worker 1 and Worker 2 both try to get next job

1006−begin;

1007−select * from jobs where status = 'pending' order by created_at limit 1 for update;

1008−-- Worker 2 waits for Worker 1's lock to release!

1009−```

1010−

1011−**Correct (SKIP LOCKED for parallel processing):**

1012−

1013−```sql

1014−-- Each worker skips locked rows and gets the next available

1015−begin;

1016−select * from jobs

1017−where status = 'pending'

1018−order by created_at

1019−limit 1

1020−for update skip locked;

1021−

1022−-- Worker 1 gets job 1, Worker 2 gets job 2 (no waiting)

1023−

1024−update jobs set status = 'processing' where id = $1;

1025−commit;

1026−-- Atomic claim-and-update in one statement

1027−update jobs

1028−set status = 'processing', worker_id = $1, started_at = now()

1029−where id = (

1030− select id from jobs

1031− where status = 'pending'

1032− order by created_at

1033− limit 1

1034− for update skip locked

1035−)

1036−returning *;

1037−```

1038−

1039−Complete queue pattern:

1040−

1041−Reference: https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

1042−

1043−---

1044−

1045−## 6. Data Access Patterns

1046−

1047−**Impact: MEDIUM**

1048−

1049−N+1 query elimination, batch operations, cursor-based pagination, and efficient data fetching.

1050−

1051−### 6.1 Batch INSERT Statements for Bulk Data

1052−

1053−**Impact: MEDIUM (10-50x faster bulk inserts)**

1054−

1055−Individual INSERT statements have high overhead. Batch multiple rows in single statements or use COPY.

1056−

1057−**Incorrect (individual inserts):**

1058−

1059−```sql

1060−-- Each insert is a separate transaction and round trip

1061−insert into events (user_id, action) values (1, 'click');

1062−insert into events (user_id, action) values (1, 'view');

1063−insert into events (user_id, action) values (2, 'click');

1064−-- ... 1000 more individual inserts

1065−

1066−-- 1000 inserts = 1000 round trips = slow

1067−```

1068−

1069−**Correct (batch insert):**

1070−

1071−```sql

1072−-- Multiple rows in single statement

1073−insert into events (user_id, action) values

1074− (1, 'click'),

1075− (1, 'view'),

1076− (2, 'click'),

1077− -- ... up to ~1000 rows per batch

1078− (999, 'view');

1079−

1080−-- One round trip for 1000 rows

1081−-- COPY is fastest for bulk loading

1082−copy events (user_id, action, created_at)

1083−from '/path/to/data.csv'

1084−with (format csv, header true);

1085−

1086−-- Or from stdin in application

1087−copy events (user_id, action) from stdin with (format csv);

1088−1,click

1089−1,view

1090−2,click

1091−\.

1092−```

1093−

1094−For large imports, use COPY:

1095−

1096−Reference: https://www.postgresql.org/docs/current/sql-copy.html

1097−

1098−---

1099−

1100−### 6.2 Eliminate N+1 Queries with Batch Loading

1101−

1102−**Impact: MEDIUM-HIGH (10-100x fewer database round trips)**

1103−

1104−N+1 queries execute one query per item in a loop. Batch them into a single query using arrays or JOINs.

1105−

1106−**Incorrect (N+1 queries):**

1107−

1108−```sql

1109−-- First query: get all users

1110−select id from users where active = true; -- Returns 100 IDs

1111−

1112−-- Then N queries, one per user

1113−select * from orders where user_id = 1;

1114−select * from orders where user_id = 2;

1115−select * from orders where user_id = 3;

1116−-- ... 97 more queries!

1117−

1118−-- Total: 101 round trips to database

1119−```

1120−

1121−**Correct (single batch query):**

1122−

1123−```sql

1124−-- Collect IDs and query once with ANY

1125−select * from orders where user_id = any(array[1, 2, 3, ...]);

1126−

1127−-- Or use JOIN instead of loop

1128−select u.id, u.name, o.*

1129−from users u

1130−left join orders o on o.user_id = u.id

1131−where u.active = true;

1132−

1133−-- Total: 1 round trip

1134−-- Instead of looping in application code:

1135−-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)

1136−

1137−-- Pass array parameter:

1138−select * from orders where user_id = any($1::bigint[]);

1139−-- Application passes: [1, 2, 3, 4, 5, ...]

1140−```

1141−

1142−Application pattern:

1143−

1144−Reference: https://supabase.com/docs/guides/database/query-optimization

1145−

1146−---

1147−

1148−### 6.3 Use Cursor-Based Pagination Instead of OFFSET

1149−

1150−**Impact: MEDIUM-HIGH (Consistent O(1) performance regardless of page depth)**

1151−

1152−OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).

1153−

1154−**Incorrect (OFFSET pagination):**

1155−

1156−```sql

1157−-- Page 1: scans 20 rows

1158−select * from products order by id limit 20 offset 0;

1159−

1160−-- Page 100: scans 2000 rows to skip 1980

1161−select * from products order by id limit 20 offset 1980;

1162−

1163−-- Page 10000: scans 200,000 rows!

1164−select * from products order by id limit 20 offset 199980;

1165−```

1166−

1167−**Correct (cursor/keyset pagination):**

1168−

1169−```sql

1170−-- Page 1: get first 20

1171−select * from products order by id limit 20;

1172−-- Application stores last_id = 20

1173−

1174−-- Page 2: start after last ID

1175−select * from products where id > 20 order by id limit 20;

1176−-- Uses index, always fast regardless of page depth

1177−

1178−-- Page 10000: same speed as page 1

1179−select * from products where id > 199980 order by id limit 20;

1180−-- Cursor must include all sort columns

1181−select * from products

1182−where (created_at, id) > ('2024-01-15 10:00:00', 12345)

1183−order by created_at, id

1184−limit 20;

1185−```

1186−

1187−For multi-column sorting:

1188−

1189−Reference: https://supabase.com/docs/guides/database/pagination

1190−

1191−---

1192−

1193−### 6.4 Use UPSERT for Insert-or-Update Operations

1194−

1195−**Impact: MEDIUM (Atomic operation, eliminates race conditions)**

1196−

1197−Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.

1198−

1199−**Incorrect (check-then-insert race condition):**

1200−

1201−```sql

1202−-- Race condition: two requests check simultaneously

1203−select * from settings where user_id = 123 and key = 'theme';

1204−-- Both find nothing

1205−

1206−-- Both try to insert

1207−insert into settings (user_id, key, value) values (123, 'theme', 'dark');

1208−-- One succeeds, one fails with duplicate key error!

1209−```

1210−

1211−**Correct (atomic UPSERT):**

1212−

1213−```sql

1214−-- Single atomic operation

1215−insert into settings (user_id, key, value)

1216−values (123, 'theme', 'dark')

1217−on conflict (user_id, key)

1218−do update set value = excluded.value, updated_at = now();

1219−

1220−-- Returns the inserted/updated row

1221−insert into settings (user_id, key, value)

1222−values (123, 'theme', 'dark')

1223−on conflict (user_id, key)

1224−do update set value = excluded.value

1225−returning *;

1226−-- Insert only if not exists (no update)

1227−insert into page_views (page_id, user_id)

1228−values (1, 123)

1229−on conflict (page_id, user_id) do nothing;

1230−```

1231−

1232−Insert-or-ignore pattern:

1233−

1234−Reference: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

1235−

1236−---

1237−

1238−## 7. Monitoring & Diagnostics

1239−

1240−**Impact: LOW-MEDIUM**

1241−

1242−Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection, and performance diagnostics.

1243−

1244−### 7.1 Enable pg_stat_statements for Query Analysis

1245−

1246−**Impact: LOW-MEDIUM (Identify top resource-consuming queries)**

1247−

1248−pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.

1249−

1250−**Incorrect (no visibility into query patterns):**

1251−

1252−```sql

1253−-- Database is slow, but which queries are the problem?

1254−-- No way to know without pg_stat_statements

1255−```

1256−

1257−**Correct (enable and query pg_stat_statements):**

1258−

1259−```sql

1260−-- Enable the extension

1261−create extension if not exists pg_stat_statements;

1262−

1263−-- Find slowest queries by total time

1264−select

1265− calls,

1266− round(total_exec_time::numeric, 2) as total_time_ms,

1267− round(mean_exec_time::numeric, 2) as mean_time_ms,

1268− query

1269−from pg_stat_statements

1270−order by total_exec_time desc

1271−limit 10;

1272−

1273−-- Find most frequent queries

1274−select calls, query

1275−from pg_stat_statements

1276−order by calls desc

1277−limit 10;

1278−

1279−-- Reset statistics after optimization

1280−select pg_stat_statements_reset();

1281−-- Queries with high mean time (candidates for optimization)

1282−select query, mean_exec_time, calls

1283−from pg_stat_statements

1284−where mean_exec_time > 100 -- > 100ms average

1285−order by mean_exec_time desc;

1286−```

1287−

1288−Key metrics to monitor:

1289−

1290−Reference: https://supabase.com/docs/guides/database/extensions/pg_stat_statements

1291−

1292−---

1293−

1294−### 7.2 Maintain Table Statistics with VACUUM and ANALYZE

1295−

1296−**Impact: MEDIUM (2-10x better query plans with accurate statistics)**

1297−

1298−Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.

1299−

1300−**Incorrect (stale statistics):**

1301−

1302−```sql

1303−-- Table has 1M rows but stats say 1000

1304−-- Query planner chooses wrong strategy

1305−explain select * from orders where status = 'pending';

1306−-- Shows: Seq Scan (because stats show small table)

1307−-- Actually: Index Scan would be much faster

1308−```

1309−

1310−**Correct (maintain fresh statistics):**

1311−

1312−```sql

1313−-- Manually analyze after large data changes

1314−analyze orders;

1315−

1316−-- Analyze specific columns used in WHERE clauses

1317−analyze orders (status, created_at);

1318−

1319−-- Check when tables were last analyzed

1320−select

1321− relname,

1322− last_vacuum,

1323− last_autovacuum,

1324− last_analyze,

1325− last_autoanalyze

1326−from pg_stat_user_tables

1327−order by last_analyze nulls first;

1328−-- Increase frequency for high-churn tables

1329−alter table orders set (

1330− autovacuum_vacuum_scale_factor = 0.05, -- Vacuum at 5% dead tuples (default 20%)

1331− autovacuum_analyze_scale_factor = 0.02 -- Analyze at 2% changes (default 10%)

1332−);

1333−

1334−-- Check autovacuum status

1335−select * from pg_stat_progress_vacuum;

1336−```

1337−

1338−Autovacuum tuning for busy tables:

1339−

1340−Reference: https://supabase.com/docs/guides/database/database-size#vacuum-operations

1341−

1342−---

1343−

1344−### 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries

1345−

1346−**Impact: LOW-MEDIUM (Identify exact bottlenecks in query execution)**

1347−

1348−EXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.

1349−

1350−**Incorrect (guessing at performance issues):**

1351−

1352−```sql

1353−-- Query is slow, but why?

1354−select * from orders where customer_id = 123 and status = 'pending';

1355−-- "It must be missing an index" - but which one?

1356−```

1357−

1358−**Correct (use EXPLAIN ANALYZE):**

1359−

1360−```sql

1361−explain (analyze, buffers, format text)

1362−select * from orders where customer_id = 123 and status = 'pending';

1363−

1364−-- Output reveals the issue:

1365−-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=100) (actual time=0.015..450.123 rows=50 loops=1)

1366−-- Filter: ((customer_id = 123) AND (status = 'pending'::text))

1367−-- Rows Removed by Filter: 999950

1368−-- Buffers: shared hit=5000 read=15000

1369−-- Planning Time: 0.150 ms

1370−-- Execution Time: 450.500 ms

1371−-- Seq Scan on large tables = missing index

1372−-- Rows Removed by Filter = poor selectivity or missing index

1373−-- Buffers: read >> hit = data not cached, needs more memory

1374−-- Nested Loop with high loops = consider different join strategy

1375−-- Sort Method: external merge = work_mem too low

1376−```

1377−

1378−Key things to look for:

1379−

1380−Reference: https://supabase.com/docs/guides/database/inspect

1381−

1382−---

1383−

1384−## 8. Advanced Features

1385−

1386−**Impact: LOW**

1387−

1388−Full-text search, JSONB optimization, PostGIS, extensions, and advanced Postgres features.

1389−

1390−### 8.1 Index JSONB Columns for Efficient Querying

1391−

1392−**Impact: MEDIUM (10-100x faster JSONB queries with proper indexing)**

1393−

1394−JSONB queries without indexes scan the entire table. Use GIN indexes for containment queries.

1395−

1396−**Incorrect (no index on JSONB):**

1397−

1398−```sql

1399−create table products (

1400− id bigint primary key,

1401− attributes jsonb

1402−);

1403−

1404−-- Full table scan for every query

1405−select * from products where attributes @> '{"color": "red"}';

1406−select * from products where attributes->>'brand' = 'Nike';

1407−```

1408−

1409−**Correct (GIN index for JSONB):**

1410−

1411−```sql

1412−-- GIN index for containment operators (@>, ?, ?&, ?|)

1413−create index products_attrs_gin on products using gin (attributes);

1414−

1415−-- Now containment queries use the index

1416−select * from products where attributes @> '{"color": "red"}';

1417−

1418−-- For specific key lookups, use expression index

1419−create index products_brand_idx on products ((attributes->>'brand'));

1420−select * from products where attributes->>'brand' = 'Nike';

1421−-- jsonb_ops (default): supports all operators, larger index

1422−create index idx1 on products using gin (attributes);

1423−

1424−-- jsonb_path_ops: only @> operator, but 2-3x smaller index

1425−create index idx2 on products using gin (attributes jsonb_path_ops);

1426−```

1427−

1428−Choose the right operator class:

1429−

1430−Reference: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

1431−

1432−---

1433−

1434−### 8.2 Use tsvector for Full-Text Search

1435−

1436−**Impact: MEDIUM (100x faster than LIKE, with ranking support)**

1437−

1438−LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.

1439−

1440−**Incorrect (LIKE pattern matching):**

1441−

1442−```sql

1443−-- Cannot use index, scans all rows

1444−select * from articles where content like '%postgresql%';

1445−

1446−-- Case-insensitive makes it worse

1447−select * from articles where lower(content) like '%postgresql%';

1448−```

1449−

1450−**Correct (full-text search with tsvector):**

1451−

1452−```sql

1453−-- Add tsvector column and index

1454−alter table articles add column search_vector tsvector

1455− generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;

1456−

1457−create index articles_search_idx on articles using gin (search_vector);

1458−

1459−-- Fast full-text search

1460−select * from articles

1461−where search_vector @@ to_tsquery('english', 'postgresql & performance');

1462−

1463−-- With ranking

1464−select *, ts_rank(search_vector, query) as rank

1465−from articles, to_tsquery('english', 'postgresql') query

1466−where search_vector @@ query

1467−order by rank desc;

1468−-- AND: both terms required

1469−to_tsquery('postgresql & performance')

1470−

1471−-- OR: either term

1472−to_tsquery('postgresql | mysql')

1473−

1474−-- Prefix matching

1475−to_tsquery('post:*')

1476−```

1477−

1478−Search multiple terms:

1479−

1480−Reference: https://supabase.com/docs/guides/database/full-text-search

1481−

1482−---

1483−

1484−## References

1485−

1486−- https://www.postgresql.org/docs/current/

1487−- https://supabase.com/docs

1488−- https://wiki.postgresql.org/wiki/Performance_Optimization

1489−- https://supabase.com/docs/guides/database/overview

1490−- https://supabase.com/docs/guides/auth/row-level-security

569+- **Component catalog**: Always regenerate after adding/modifying components

570+- **API tests**: Required before production deploy (breaks download tracking)

571+- **Secrets**: Never commit API keys (use environment variables)

572+- **Paths**: Use relative paths for all project files

573+- **Backwards compatibility**: Don't break existing component installations

1491574

Two files, one repository

Built for the same person as RuleStack