Feature Specification Template

Feature Name: [Your Feature Name]
Date: [YYYY-MM-DD]
Author: [Your Name]
Status: [Draft | In Review | Approved | Implemented]
Version: 1.0

Overview

[Brief description of the feature - 2-3 sentences]

Goals

[Primary goal 1]
[Primary goal 2]
[Primary goal 3]

Non-Goals

[Explicitly out of scope item 1]
[Explicitly out of scope item 2]

Architecture Integration

Layer Placement

This feature operates at the [Database | Codegen | Application] layer.

Database Layer (PostgreSQL)
  ↓ (provides connection & data)
Codegen Layer (Type-safe wrappers)
  ↓ (provides types & functions)
Application Layer (Business logic)
  ↓ (uses)
[YOUR FEATURE] ← You are here

🔐 Database Interaction Golden Rules

Application code MUST call PostgreSQL functions via generated wrappers in src/lib/db/generated/.

Use withClient + generated functions for all business logic.

Raw client.query() is allowed only for:

migrations & schema management,

admin/maintenance scripts,

diagnostics/troubleshooting,

or explicitly documented performance-critical paths.

See DATABASE-PATTERNS.md for full patterns and anti-patterns.

Pattern Compliance Checklist

REQUIRED: Every feature spec must confirm adherence to architectural patterns:

All new SQL functions are exposed via codegen and consumed via src/lib/db/generated/
No raw SQL is introduced in feature application code, except where explicitly allowed by DATABASE-PATTERNS.md
Auth/session-sensitive behavior follows Auth docs and uses generated functions or approved services
Feature implementation follows patterns documented in DATABASE-PATTERNS.md
Generated functions are used instead of client.query() for all database operations in application code

Dependencies

Required layers:

Database layer (src/lib/db/) - Connection management
Codegen layer (src/lib/db/generated/) - Type-safe functions
[Other dependencies]

Related features:

[Feature 1] - [How they relate]
[Feature 2] - [How they relate]

Database Schema

New Tables

-- db/[NUMBER]_[feature]_schema.sql

-- Table 1
CREATE TABLE [schema].[table_name] (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- columns
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_[table]_[column] ON [schema].[table]([column]);

COMMENT ON TABLE [schema].[table_name] IS
'[Description of table purpose]';

New Functions

-- db/[NUMBER]_[feature]_functions.sql

-- Function 1: [Description]
CREATE OR REPLACE FUNCTION [schema].[function_name](
    p_param1 TYPE,
    p_param2 TYPE
) RETURNS [RETURN_TYPE] AS $$
BEGIN
    -- Implementation
    RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION [schema].[function_name] IS
'[Description of what function does]';

-- Grant permissions
GRANT EXECUTE ON FUNCTION [schema].[function_name] TO [role];

Security Considerations

Row-Level Security:

-- Enable RLS
ALTER TABLE [schema].[table] ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY [policy_name]
    ON [schema].[table]
    FOR SELECT
    USING (owner_id = auth.subject_id());

Permissions:

Grant appropriate permissions to studio_app_connector
Grant appropriate permissions to authenticated users
Document any elevated privileges needed

Code Generation Integration

Expected Generated Functions

After running pnpm db:codegen, the following functions will be available:

// src/lib/db/generated/[schema]/functions.ts

// Auto-generated by codegen
export async function functionName(
    client: PoolClient,
    params: {
        p_param1: Type;
        p_param2: Type;
    }
): Promise<ReturnType> {
    // Implementation auto-generated
}

Type Definitions

// src/lib/db/generated/[schema]/models.ts

export interface EntityName {
    id: string;
    // fields
    created_at: Date;
    updated_at: Date;
}

export const entityNameSchema = z.object({
    id: z.string().uuid(),
    // Zod schema fields
    created_at: z.coerce.date(),
    updated_at: z.coerce.date()
});

Service Layer Implementation

Service Class

// src/lib/[feature]/service.ts

import { withClient } from '@/lib/db';
import { [schema] } from '@/lib/db/generated';

export class [Feature]Service {
    /**
     * [Description of operation]
     */
    async operation(params: OperationParams): Promise<OperationResult> {
        return await withClient(async (client) => {
            // ✅ CORRECT: Using generated functions
            return await [schema].functionName(client, {
                p_param1: params.param1,
                p_param2: params.param2
            });
        });
    }
    
    /**
     * [Description of transaction operation]
     */
    async transactionOperation(params: any): Promise<any> {
        return await withClient(async (client) => {
            await client.query('BEGIN');
            
            try {
                const result1 = await [schema].operation1(client, params1);
                const result2 = await [schema].operation2(client, params2);
                
                await client.query('COMMIT');
                return { result1, result2 };
            } catch (error) {
                await client.query('ROLLBACK');
                throw error;
            }
        });
    }
}

API Integration

// src/app/api/[feature]/route.ts

import { [Feature]Service } from '@/lib/[feature]/service';

export async function POST(request: Request) {
    const body = await request.json();
    
    try {
        const service = new [Feature]Service();
        const result = await service.operation(body);
        
        return Response.json(result, { status: 200 });
    } catch (error) {
        console.error('[Feature] operation failed:', error);
        return Response.json(
            { error: 'Operation failed' },
            { status: 500 }
        );
    }
}

Usage Examples

Basic Usage

import { [Feature]Service } from '@/lib/[feature]/service';

const service = new [Feature]Service();

// Example 1: Simple operation
const result = await service.operation({
    param1: 'value1',
    param2: 'value2'
});

console.log('Result:', result);

Advanced Usage

import { withClient } from '@/lib/db';
import { [schema] } from '@/lib/db/generated';

// Direct function usage (when service layer not needed)
const data = await withClient(async (client) => {
    return await [schema].complexOperation(client, {
        // parameters
    });
});

Error Handling

try {
    const result = await service.operation(params);
} catch (error) {
    if (error instanceof ZodError) {
        // Input validation failed
        console.error('Invalid parameters:', error.errors);
    } else if (error.code === '23505') {
        // Unique constraint violation
        console.error('Duplicate entry');
    } else {
        // Other errors
        console.error('Operation failed:', error);
    }
}

Testing Strategy

Unit Tests

// tests/unit/[feature]/service.test.ts

import { describe, it, expect, beforeEach } from 'vitest';
import { [Feature]Service } from '@/lib/[feature]/service';

describe('[Feature]Service', () => {
    let service: [Feature]Service;
    
    beforeEach(() => {
        service = new [Feature]Service();
    });
    
    it('should perform operation successfully', async () => {
        const result = await service.operation({
            param1: 'test1',
            param2: 'test2'
        });
        
        expect(result).toBeDefined();
        expect(result.someField).toBe('expected');
    });
    
    it('should handle validation errors', async () => {
        await expect(
            service.operation({ invalid: 'params' })
        ).rejects.toThrow();
    });
});

Integration Tests

// tests/integration/[feature]/workflow.test.ts

import { test } from '../test-helpers';
import { [schema] } from '@/lib/db/generated';

test('[feature] end-to-end workflow', async ({ client, cleanup }) => {
    // Setup
    const testData = { /* test data */ };
    
    // Execute
    const result = await [schema].operation(client, testData);
    
    // Assert
    expect(result).toBeDefined();
    expect(result.id).toBeTruthy();
    
    // Verify
    const retrieved = await [schema].getById(client, {
        id: result.id
    });
    expect(retrieved).toEqual(result);
    
    // Cleanup handled by test framework
});

Test Coverage Goals

Performance Considerations

Expected Performance

Operation	Target Latency	Target Throughput
[Operation 1]	<10ms	1000 ops/sec
[Operation 2]	<50ms	500 ops/sec
[Operation 3]	<100ms	100 ops/sec

Optimization Strategy

Database Level:
- Appropriate indexes created
- Query plans reviewed
- Connection pooling configured
Application Level:
- Caching strategy (if applicable)
- Batch operations (if applicable)
- Connection management optimized
Monitoring:
- Key metrics identified
- Alerts configured
- Dashboard created

Migration & Rollout Plan

Phase 1: Schema Deployment

# 1. Apply schema
psql -d database -f db/[NUMBER]_[feature]_schema.sql

# 2. Verify tables created
psql -d database -c "\dt [schema].*"

# 3. Verify functions created
psql -d database -c "\df [schema].*"

Phase 2: Code Generation

# Regenerate types
pnpm db:codegen

# Verify generated files
ls src/lib/db/generated/[schema]/

Phase 3: Service Deployment

# 1. Deploy code
git push origin main

# 2. Run migrations
pnpm migrate

# 3. Restart services
# [Platform-specific restart command]

Rollback Plan

-- Rollback script: db/rollback_[NUMBER]_[feature].sql

-- Drop functions
DROP FUNCTION IF EXISTS [schema].[function_name];

-- Drop tables
DROP TABLE IF EXISTS [schema].[table_name];

Security Review Checklist

SQL injection prevention (using parameterized queries)
Row-level security policies implemented
Appropriate permissions granted (least privilege)
Sensitive data encrypted
Audit logging configured
Rate limiting considered
Input validation comprehensive
Error messages don't leak sensitive info

Documentation Checklist

Feature overview documented
API endpoints documented
Database schema documented
Integration examples provided
Error handling documented
Performance characteristics documented
Security considerations documented
Testing strategy documented

Dependencies & Prerequisites

Development Dependencies

{
  "dependencies": {
    "@/lib/db": "workspace:*",
    "zod": "^3.22.0"
  },
  "devDependencies": {
    "vitest": "^1.0.0"
  }
}

External Services

[Service 1] - [Purpose]
[Service 2] - [Purpose]

Configuration

# Environment variables
FEATURE_ENABLED=true
FEATURE_CONFIG_VALUE=value

References

Documentation

Architecture Analysis: docs/ARCHITECTURE-ANALYSIS.md
Database Patterns: docs/DATABASE-PATTERNS.md
Database Layer: docs/db/README.md
Codegen System: docs/codegen/README.md
Cursor Rules: .cursorrules/database-patterns.md

[Feature 1 Documentation]
[Feature 2 Documentation]

External Resources

[External API documentation]
[Third-party service docs]

Success Criteria

Functional Requirements

[Requirement 1] implemented and tested
[Requirement 2] implemented and tested
[Requirement 3] implemented and tested

Non-Functional Requirements

Acceptance Criteria

Feature works in development environment
Feature works in staging environment
Integration tests passing
Performance benchmarks met
Security scan passed
Documentation reviewed
Stakeholder approval received

Open Questions

[Question 1]
- Status: [Answered | Open | Blocked]
- Resolution: [Answer or decision]
[Question 2]
- Status: [Answered | Open | Blocked]
- Resolution: [Answer or decision]

Task Breakdown for Linear

This feature can be broken down into trackable tasks for Linear. See Linear Multi-Project Sync for details on creating task files.

Task Structure

Create markdown task files in docs/[feature]/tasks/:

---
id: "1.1"
title: "Implement Database Schema"
priority: 1
estimate: 8
labels: [database, backend]
dependencies: []
relatedTasks: ["1.2"]
---

# Implement Database Schema

Create tables and functions for [feature].

## Requirements
[Requirements from feature spec]

## Implementation
[SQL or code snippets]

## Testing
[Test plan]

Suggested Task Breakdown

Task 1.1: Database schema implementation (8h)
Task 1.2: Function implementation (5h)
Task 1.3: Type generation integration (3h)
Task 1.4: Service layer implementation (5h)
Task 1.5: API endpoint implementation (3h)
Task 1.6: Integration testing (5h)
Task 1.7: Documentation (2h)

Sync to Linear

# Create project file
cat > docs/linear/projects/feature-name.yaml << EOF
name: [Feature Name] Implementation
project: PRJ-xxx
tasks:
  - [feature]/tasks/task-1.1.md
  - [feature]/tasks/task-1.2.md
  # ...
EOF

# Sync to Linear
pnpm linear:sync:projects docs/linear/projects/feature-name.yaml --dry-run

Timeline

Phase	Duration	Status
Specification	2 days	[Not Started
Schema Design	3 days	[Not Started
Implementation	5 days	[Not Started
Testing	3 days	[Not Started
Review	2 days	[Not Started
Deployment	1 day	[Not Started

Total Estimated: 16 days
Actual: [TBD]

Approval Sign-off

Technical Lead: [Name] - [Date]
Database Admin: [Name] - [Date]
Security Review: [Name] - [Date]
Product Owner: [Name] - [Date]

Document Version: 1.0
Last Updated: [YYYY-MM-DD]
Next Review: [YYYY-MM-DD]
Maintained By: [Team Name]