Database Query Prompt Templates

```sql
-- Top 10 customers by spend in last 30 days
-- Excludes test accounts and non-completed orders

SELECT 
 u.id,
 u.name,
 u.email,
 COUNT(o.id) AS order_count,
 SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE 
 o.status = 'completed'
 AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
 AND u.email NOT LIKE '%@test.com'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 10;
```

**Performance recommendations:**

For 5M orders, add these indexes if not present:
```sql
-- Composite index for the WHERE + JOIN
CREATE INDEX idx_orders_user_status_date 
 ON orders(user_id, status, created_at)
 WHERE status = 'completed';
```

**Query explanation:**
- `INNER JOIN` excludes users with no orders (appropriate here since we want customers with spend)
- Filtering in WHERE before GROUP BY reduces rows to aggregate
- Partial index with `WHERE status = 'completed'` is smaller and faster since we only query completed orders

## Analysis: Why It's Slow

1. **Full table scan on reviews** - No index on `product_id`, scanning 10M rows
2. **Subquery for avg rating** - Executes once per product (N+1 pattern)
3. **OR condition breaking index** - `WHERE category_id = 5 OR featured = 1` can't use indexes efficiently

## Optimized Query

```sql
-- Step 1: Pre-aggregate reviews (can be materialized view)
WITH review_stats AS (
 SELECT 
 product_id,
 AVG(rating) AS avg_rating,
 COUNT(*) AS review_count
 FROM reviews
 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
 GROUP BY product_id
)
SELECT 
 p.id,
 p.name,
 p.price,
 c.name AS category_name,
 COALESCE(rs.avg_rating, 0) AS avg_rating,
 COALESCE(rs.review_count, 0) AS review_count
FROM products p
INNER JOIN categories c ON c.id = p.category_id
LEFT JOIN review_stats rs ON rs.product_id = p.id
WHERE p.category_id = 5

UNION

SELECT... -- Same query with WHERE p.featured = 1
```

## Recommended Indexes

```sql
-- Critical: Index for reviews aggregation
CREATE INDEX idx_reviews_product_date 
 ON reviews(product_id, created_at, rating);

-- For product lookups
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_featured ON products(featured) WHERE featured = 1;
```

## Consider: Materialized View

Since reviews don't change constantly:

```sql
CREATE TABLE review_stats_cache AS
SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
FROM reviews GROUP BY product_id;

-- Refresh hourly via cron/scheduler
```

**Expected improvement:** 45s → <500ms with indexes, <100ms with materialized view

```javascript
// User engagement stats for the past week
db.events.aggregate([
 // Stage 1: Filter to relevant events this week
 {
 $match: {
 type: { $in: ['pageview', 'session'] },
 timestamp: {
 $gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)
 }
 }
 },
 
 // Stage 2: Group by user, separate pageviews from sessions
 {
 $group: {
 _id: '$userId',
 pageViews: {
 $sum: { $cond: [{ $eq: ['$type', 'pageview'] }, 1, 0] }
 },
 totalDuration: {
 $sum: {
 $cond: [
 { $eq: ['$type', 'session'] },
 '$properties.duration',
 0
 ]
 }
 },
 sessionCount: {
 $sum: { $cond: [{ $eq: ['$type', 'session'] }, 1, 0] }
 }
 }
 },
 
 // Stage 3: Calculate average and reshape output
 {
 $project: {
 _id: 0,
 userId: '$_id',
 pageViews: 1,
 avgDuration: {
 $cond: [
 { $gt: ['$sessionCount', 0] },
 { $divide: ['$totalDuration', '$sessionCount'] },
 0
 ]
 }
 }
 },
 
 // Stage 4: Sort by engagement (optional)
 { $sort: { pageViews: -1 } }
]);
```

**Recommended index:**
```javascript
// Compound index covering the $match stage
db.events.createIndex(
 { type: 1, timestamp: -1 },
 { name: 'idx_type_timestamp' }
);

// If querying specific users often:
db.events.createIndex(
 { userId: 1, type: 1, timestamp: -1 }
);
```

**Performance notes:**
- `$match` at the start uses index and reduces documents early
- Single `$group` stage avoids multiple passes over data
- Conditional `$sum` is more efficient than separate `$match` stages per type

## Schema Design

```sql
-- Organizations (tenants)
CREATE TABLE organizations (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 name VARCHAR(255) NOT NULL,
 slug VARCHAR(100) UNIQUE NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Users (belong to one org)
CREATE TABLE users (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 organization_id UUID NOT NULL REFERENCES organizations(id),
 email VARCHAR(255) NOT NULL,
 name VARCHAR(255),
 role VARCHAR(50) DEFAULT 'member',
 created_at TIMESTAMPTZ DEFAULT NOW(),
 UNIQUE(organization_id, email) -- Email unique per org
);

-- Subscriptions (one per org, keeps history)
CREATE TABLE subscriptions (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 organization_id UUID NOT NULL REFERENCES organizations(id),
 plan_id VARCHAR(50) NOT NULL, -- 'starter', 'pro', 'enterprise'
 status VARCHAR(20) NOT NULL, -- 'active', 'canceled', 'past_due'
 current_period_start TIMESTAMPTZ NOT NULL,
 current_period_end TIMESTAMPTZ NOT NULL,
 canceled_at TIMESTAMPTZ,
 created_at TIMESTAMPTZ DEFAULT NOW(),
 
 -- Only one active subscription per org
 CONSTRAINT one_active_per_org EXCLUDE USING gist (
 organization_id WITH =,
 tstzrange(current_period_start, current_period_end) WITH &&
 ) WHERE (status = 'active')
);

-- Invoices
CREATE TABLE invoices (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 organization_id UUID NOT NULL REFERENCES organizations(id),
 subscription_id UUID REFERENCES subscriptions(id),
 amount_cents INTEGER NOT NULL,
 currency CHAR(3) DEFAULT 'USD',
 status VARCHAR(20) NOT NULL, -- 'draft', 'open', 'paid', 'void'
 due_date DATE NOT NULL,
 paid_at TIMESTAMPTZ,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Payments
CREATE TABLE payments (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 invoice_id UUID NOT NULL REFERENCES invoices(id),
 amount_cents INTEGER NOT NULL,
 payment_method VARCHAR(50), -- 'card', 'bank_transfer'
 external_id VARCHAR(255), -- Stripe charge ID, etc.
 status VARCHAR(20) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit log (append-only)
CREATE TABLE audit_log (
 id BIGSERIAL PRIMARY KEY,
 organization_id UUID NOT NULL,
 user_id UUID,
 action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(50) NOT NULL,
 entity_id UUID NOT NULL,
 changes JSONB,
 created_at TIMESTAMPTZ DEFAULT NOW()
);
```

## Indexes for Query Patterns

```sql
-- Get org's current subscription
CREATE INDEX idx_subscriptions_org_status 
 ON subscriptions(organization_id, status)
 WHERE status = 'active';

-- List invoices by date range
CREATE INDEX idx_invoices_org_date 
 ON invoices(organization_id, created_at DESC);

-- Monthly revenue report
CREATE INDEX idx_invoices_status_date 
 ON invoices(status, created_at)
 WHERE status = 'paid';

-- Audit log queries (always by org)
CREATE INDEX idx_audit_org_date 
 ON audit_log(organization_id, created_at DESC);
```

## Multi-Tenancy Strategy

Using row-level security for tenant isolation:

```sql
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
 USING (organization_id = current_setting('app.current_org')::uuid);
```

## Notes

- **UUIDs** for primary keys: Better for distributed systems, harder to guess
- **Subscription history**: Keep all subscriptions, query `WHERE status = 'active'` for current
- **Amount in cents**: Avoids floating-point issues with currency
- **Audit log is append-only**: Never update or delete, just insert