Documentation Architecture Analysis

Date: November 7, 2025
Status: Historical/Diagnostic - Superseded by Pattern Documents
Version: 1.0

📋 Note: This document captured initial findings and gaps identified during the documentation audit conducted on November 7, 2025. The issues and recommendations documented here have been addressed and implemented in subsequent documentation updates. For current enforced patterns and architectural guidelines, refer to:

Canonical Patterns: DATABASE-PATTERNS.md

Pattern Audits: DOCUMENTATION-PATTERN-AUDIT-REPORT.md and DOCUMENTATION-PATTERN-SECOND-PASS-AUDIT.md

Cursor Rules: .cursorrules/database-patterns.md

This document is maintained for historical reference and to document the evolution of our documentation standards.

Executive Summary

This document analyzes the architectural layering and pattern consistency across the database (db), code generation (codegen), and authentication (auth) documentation suites. It identifies current cross-references, gaps, and provides recommendations for establishing clear, unambiguous patterns for database interaction.

Key Findings

✅ Strengths:

Database documentation has good coverage of generated functions
Implementation code (src/lib/db/queries.ts) follows correct patterns
Generated code structure is well-organized

❌ Gaps:

Codegen documentation lacks references to database layer
Auth documentation doesn't emphasize codegen usage
.cursorrules lacks explicit database interaction patterns
Cross-references are incomplete across documentation suites

Architectural Principle

Codegen defines the canonical way to interact with the database:

┌─────────────────────────────────────┐
│    Database Layer (Lowest)          │
│    - Connection management           │
│    - Pool configuration              │
│    - Transaction patterns            │
└────────────┬────────────────────────┘
             │ provides connections to
┌────────────▼────────────────────────┐
│    Codegen Layer (Middle)            │
│    - Type generation from PostgreSQL │
│    - Strongly-typed function wrappers│
│    - Runtime Zod validation          │
└────────────┬────────────────────────┘
             │ types & functions used by
┌────────────▼────────────────────────┐
│    Auth Layer (Application)          │
│    - Authentication services         │
│    - Session management              │
│    - JWT operations                  │
└────────────┬────────────────────────┘
             │ patterns used by
┌────────────▼────────────────────────┐
│    Future Features                   │
│    - Graph operations                │
│    - Webhooks                        │
│    - Studio functionality            │
└─────────────────────────────────────┘

Current State Analysis

1. Database Documentation (docs/db/)

File: docs/db/README.md

✅ Strengths:

Links to codegen documentation in "Related Documentation"
Links to auth documentation
Mentions type generation

❌ Gaps:

Doesn't emphasize codegen as the primary interaction method
Cross-reference to codegen could be more prominent

File: docs/db/02-query-execution.md

✅ Strengths:

Has dedicated "Generated Functions" section

Shows correct import pattern:

import { studio, auth, graph } from '@/lib/db/generated';

Shows correct usage:

await studio.login(client, { jwt_token: token });

Best practices section recommends generated functions
Links to codegen documentation in "Related Documentation"

❌ Gaps:

Could be more explicit about WHEN to use generated vs raw SQL
Generated functions section could be earlier/more prominent

Recommendation:

✅ Overall well-structured
Minor improvements needed to emphasize codegen-first approach

2. Codegen Documentation (docs/codegen/)

File: docs/codegen/README.md

✅ Strengths:

Excellent modular structure
Clear phase-based implementation guide
Good technical depth

❌ Gaps:

CRITICAL: Does not reference database documentation
Missing explanation of dependency on db connection patterns
Doesn't show how it fits in overall architecture
No cross-reference to docs/db/

File: docs/codegen/04-function-invocation.md

✅ Strengths:

Excellent technical specification
Clear function semantics
Good code examples

❌ Gaps:

Doesn't reference connection management from db layer
Missing link to docs/db/01-connection-management.md
Doesn't explain how to get PoolClient instances

Recommendation:

CRITICAL: Add "Dependencies" section to README linking to db docs
Add explanation of how codegen builds on db connection layer
Cross-reference db documentation in relevant sections

3. Auth Documentation (docs/auth/)

File: docs/auth/README.md

✅ Strengths:

Comprehensive service documentation
Good quick start examples
Clear architecture diagrams

❌ Gaps:

Doesn't mention codegen-generated functions
Missing reference to codegen documentation
Doesn't emphasize that auth functions should use codegen

File: docs/auth/05-services.md

✅ Strengths:

Shows correct withClient pattern
Good service integration examples
Comprehensive workflow coverage

❌ Gaps:

CRITICAL: Examples don't show imports from generated functions
No mention of codegen layer
Service examples could show generated function usage
Missing cross-reference to codegen docs

Code Examples Issue:

// Current auth docs show:
const result = await auth.api().createSessionWithTokens({ ... });

// Should also show the underlying pattern:
import { studio } from '@/lib/db/generated';
await studio.login(client, { jwt_token: token });

Recommendation:

Add note emphasizing codegen-generated functions
Link to codegen documentation
Add examples showing direct use of generated functions

4. Implementation Code (src/lib/db/)

File: src/lib/db/queries.ts

✅ Strengths:

EXCELLENT: Correctly imports from generated:
```
import { studio } from './generated';
```

EXCELLENT: Uses generated functions:

await studio.login(client, { jwt_token: appToken });
await studio.logout(client);

Serves as good reference implementation

⚠️ Minor Issues:

Some utility functions use raw SQL:

export const getCurrentRole = async (client: PoolClient): Promise<string> => {
    const res = await client.query(`SELECT current_role`);
    return res.rows[0].current_role;
};

Could potentially have generated versions, but these may be intentional for system queries

File: src/lib/db/generated/index.ts

✅ Strengths:

EXCELLENT: Clear documentation in file header
Good namespace exports
Clear usage examples in comments

Recommendation:

Implementation code is exemplary
Use as reference for documentation examples

5. Cursor Rules (.cursorrules/)

File: .cursorrules/database.md

✅ Strengths:

Good local development setup guide
Clear troubleshooting steps

❌ Gaps:

CRITICAL: No mention of codegen
CRITICAL: No guidance on database interaction patterns
Missing rules about using generated functions
Doesn't mandate codegen-first approach

Recommendation:

CRITICAL: Add database interaction patterns section
Mandate use of generated functions
Add examples of correct vs incorrect patterns

Missing Cross-References

Critical Missing Links

docs/codegen/README.md → docs/db/
- Need: Link to connection management
- Need: Explain dependency on db layer
- Impact: Developers won't understand the foundation
docs/auth/README.md → docs/codegen/
- Need: Reference to generated functions
- Need: Explain that auth uses codegen patterns
- Impact: Developers might write raw SQL in auth code
.cursorrules/database.md → Generated Functions
- Need: Explicit patterns and rules
- Need: Mandate codegen usage
- Impact: No enforcement of best practices

Pattern Consistency Check

✅ Correct Pattern (Found in Implementation)

// CORRECT: Using generated functions
import { studio, auth, graph } from '@/lib/db/generated';

await studio.login(client, { jwt_token: token });
const sessionId = await auth.session_id(client);
await studio.logout(client);

Benefits:

✅ Type safety at compile time
✅ Runtime validation with Zod
✅ IDE autocomplete
✅ Automatic parameter marshaling
✅ Consistent error handling

❌ Anti-Pattern (Should be Avoided)

// WRONG: Raw SQL bypassing codegen
await client.query('SELECT studio.login($1)', [token]);
await client.query('SELECT auth.session_id()');
await client.query('SELECT studio.logout()');

Problems:

❌ No compile-time type checking
❌ No runtime validation
❌ Manual parameter handling
❌ Error-prone
❌ No IDE support

When Raw SQL is Acceptable

// Acceptable for:
// 1. System queries
const role = await client.query('SELECT current_role');

// 2. Functions not yet in codegen
const result = await client.query('SELECT new_function($1)', [param]);

// 3. Complex dynamic queries
const query = buildDynamicQuery(filters);
const result = await client.query(query, params);

// 4. Performance-critical paths (measure first!)

Documentation Quality Matrix

Documentation	Cross-Refs	Patterns	Examples	Completeness	Priority
db/README.md	Good	Good	Excellent	90%	Medium
db/02-query-execution.md	Good	Excellent	Excellent	95%	Low
codegen/README.md	Poor	Good	Good	70%	HIGH
codegen/04-function-invocation.md	Poor	Excellent	Excellent	80%	High
auth/README.md	Fair	Fair	Good	75%	High
auth/05-services.md	Poor	Fair	Good	70%	HIGH
.cursorrules/database.md	None	None	Fair	40%	CRITICAL

Recommendations by Priority

🔴 Critical (Must Fix)

Add Database Interaction Patterns to .cursorrules
- Create clear rules mandating codegen usage
- Add correct vs incorrect examples
- Specify when raw SQL is acceptable
Add Codegen→DB Cross-References
- Link codegen docs to connection management
- Explain architectural dependency
- Show integration with db layer
Add Auth→Codegen Cross-References
- Reference codegen in auth docs
- Show examples using generated functions
- Emphasize the dependency

🟠 High Priority (Should Fix)

Strengthen db→codegen Emphasis
- Make codegen-first approach more prominent
- Move generated functions section earlier
- Add more examples
Add Generated Function Examples to Auth Docs
- Show direct usage of studio.login, etc.
- Explain when to use AuthService vs direct functions
- Cross-reference to codegen

🟢 Medium Priority (Nice to Have)

Create Examples Document
- Comprehensive correct patterns
- Anti-patterns to avoid
- Migration guide for existing code
Create Future Spec Template
- Template showing how new features integrate
- Checklist for proper layering
- Example feature specification

Deliverables

1. Analysis Document ✅

This document provides comprehensive analysis of current state.

2. Documentation Updates (Next Steps)

Update docs/codegen/README.md with db references
Update docs/auth/README.md with codegen references
Update docs/auth/05-services.md with generated function examples
Strengthen docs/db/README.md codegen emphasis

3. Cursor Rules Update (Critical)

Create .cursorrules/database-patterns.md
Add explicit rules mandating codegen usage
Add correct vs incorrect examples

4. Examples Document

Create docs/DATABASE-PATTERNS.md
Clear examples of correct patterns
Anti-patterns to avoid
When raw SQL is acceptable

5. Future Spec Template

Create docs/FEATURE-SPEC-TEMPLATE.md
Show proper architectural layering
Integration checklist
Example feature specification

Success Criteria

After implementing recommendations:

✅ Clear hierarchy: db → codegen → auth → features
✅ All three documentation suites cross-reference correctly
✅ No examples of raw SQL where codegen should be used
✅ .cursorrules explicitly mandates codegen usage
✅ Template for future feature documentation established
✅ Unambiguous for any developer which pattern to use

Appendix A: File Locations

Documentation Files

docs/db/README.md - Database layer overview
docs/db/02-query-execution.md - Query patterns and generated functions
docs/codegen/README.md - Code generation overview
docs/codegen/04-function-invocation.md - Function invocation patterns
docs/auth/README.md - Auth system overview
docs/auth/05-services.md - Service integration

Implementation Files

src/lib/db/queries.ts - Query utilities (good reference)
src/lib/db/generated/index.ts - Generated functions entry point
src/lib/db/generated/studio/ - Studio schema functions
src/lib/db/generated/auth/ - Auth schema functions

Configuration Files

.cursorrules/database.md - Database setup guide (needs patterns)

Appendix B: Generated Functions Structure

src/lib/db/generated/
├── index.ts              # Main exports, namespace pattern
├── studio/               # Studio schema
│   ├── functions.ts      # studio.login(), studio.logout()
│   └── models.ts         # Type definitions
├── auth/                 # Auth schema
│   ├── functions.ts      # auth.session_id(), auth.subject_id()
│   └── models.ts
├── graph/                # Graph schema
│   ├── functions.ts      # graph operations
│   └── models.ts
├── cursors/              # Cursor pagination
├── audit/                # Audit logging
└── webhook/              # Webhook operations

Appendix C: Example Integration

Correct Usage in Auth Service

// src/lib/auth/services/session/create.ts
import { withClient } from '@/lib/db';
import { studio } from '@/lib/db/generated';

export async function createSession(userId: string, token: string) {
    return await withClient(async (client) => {
        // ✅ CORRECT: Using generated function
        const role = await studio.login(client, { jwt_token: token });
        
        // Service logic continues...
        return { sessionId, role };
    });
}

Incorrect Usage (Anti-Pattern)

// ❌ WRONG: Don't do this
export async function createSession(userId: string, token: string) {
    return await withClient(async (client) => {
        // ❌ WRONG: Raw SQL bypassing types
        const result = await client.query(
            'SELECT studio.login($1)',
            [token]
        );
        const role = result.rows[0].login;
        
        return { sessionId, role };
    });
}

Document Version: 1.0
Next Review: February 7, 2025
Maintainer: Platform Engineering Team

Documentation Architecture Analysis

Executive Summary

Key Findings

Architectural Principle

Current State Analysis

1. Database Documentation (docs/db/)

2. Codegen Documentation (docs/codegen/)

3. Auth Documentation (docs/auth/)

4. Implementation Code (src/lib/db/)

5. Cursor Rules (.cursorrules/)

Missing Cross-References

Critical Missing Links

Recommended Additional Links

Pattern Consistency Check

✅ Correct Pattern (Found in Implementation)

❌ Anti-Pattern (Should be Avoided)

When Raw SQL is Acceptable

Documentation Quality Matrix

Recommendations by Priority

🔴 Critical (Must Fix)

🟠 High Priority (Should Fix)

🟢 Medium Priority (Nice to Have)

Deliverables

1. Analysis Document ✅

2. Documentation Updates (Next Steps)

3. Cursor Rules Update (Critical)

4. Examples Document

5. Future Spec Template

Success Criteria

Appendix A: File Locations

Documentation Files

Implementation Files

Configuration Files

Appendix B: Generated Functions Structure

Appendix C: Example Integration

Correct Usage in Auth Service

Incorrect Usage (Anti-Pattern)