template:
  id: database-documentation-template-v2
  name: Database Schema Documentation
  version: 2.0
  output:
    format: markdown
    filename: database-documentation-{{timestamp}}.md
    title: "{{project_name}} Database Documentation"

# Database Documentation Template
# Comprehensive documentation for database schema, API integration, and usage

sections:
  - id: header
    title: Document Header
    template: |
      # {{project_name}} Database Documentation

      **Version:** {{schema_version}}  
      **Database:** PostgreSQL (Supabase)  
      **Generated:** {{created_date}}  
      **Project URL:** {{#if project_url}}{{project_url}}{{else}}https://{{project_ref}}.supabase.co{{/if}}

      ## Overview

      This document provides comprehensive documentation for the {{project_name}} database schema, including table definitions, security policies, API integration examples, and usage guidelines.

  - id: architecture
    title: Architecture Overview
    template: |
      ## Architecture Principles

      ### Design Philosophy
      - **Security First**: Row Level Security (RLS) enabled on all user-facing tables
      - **Performance Optimized**: Strategic indexes for common query patterns
      - **Audit Ready**: Created/updated timestamps and change tracking
      - **Scalable Design**: UUID primary keys and optimized relationships
      - **API-First**: Designed for modern API and real-time usage patterns

      ### Technical Stack
      - **Database**: PostgreSQL {{postgres_version}} on Supabase
      - **Authentication**: Supabase Auth with {{auth_method}}
      - **Security**: Row Level Security (RLS) with policy-based access control
      - **Performance**: Optimized indexes and query patterns
      - **Real-time**: Supabase Realtime for live data updates

      ### Data Flow Architecture
      ```
      Client Application
           ↕ (Authenticated API calls)
      Supabase API Gateway
           ↕ (RLS Policy Enforcement)
      PostgreSQL Database
      ```

  - id: tables
    title: Table Definitions
    template: |
      ## Database Tables

      ### Entity Relationship Overview
      {{#if entity_relationships}}
      ```
      {{entity_relationships}}
      ```
      {{/if}}

      {{#each tables}}
      ### {{table_name}} Table
      **Purpose**: {{description}}

      | Column | Type | Constraints | Description |
      |--------|------|-------------|-------------|
      {{#each columns}}
      | {{column_name}} | {{data_type}} | {{constraints}} | {{description}} |
      {{/each}}

      {{#if relationships}}
      **Relationships:**
      {{#each relationships}}
      - {{relationship_type}}: {{description}}
      {{/each}}
      {{/if}}

      {{#if rls_policies}}
      **Row Level Security:**
      {{#each rls_policies}}
      - **{{policy_name}}**: {{description}}
        ```sql
        {{policy_sql}}
        ```
      {{/each}}
      {{/if}}

      {{#if indexes}}
      **Performance Indexes:**
      {{#each indexes}}
      - **{{index_name}}**: {{description}}
      {{/each}}
      {{/if}}

      {{/each}}

  - id: security
    title: Security Implementation
    template: |
      ## Security Model

      ### Authentication Integration
      {{project_name}} uses Supabase Auth for user authentication with the following configuration:

      **Authentication Methods:**
      {{#each auth_methods}}
      - {{method}}: {{description}}
      {{/each}}

      **Session Management:**
      - Session duration: {{session_duration}}
      - Token refresh: {{token_refresh_strategy}}
      - Multi-device support: {{multi_device_support}}

      ### Row Level Security (RLS)
      All user-facing tables implement RLS policies to ensure data isolation:

      {{#each security_policies}}
      #### {{table_name}} Security
      **Access Model**: {{access_model}}

      {{#each policies}}
      **{{policy_name}}**
      - Operation: {{operation}}
      - Condition: `{{condition}}`
      - Description: {{description}}

      {{/each}}
      {{/each}}

      ### API Security
      **API Key Usage:**
      - **Anonymous Key**: Public operations, rate-limited
      - **Service Role Key**: Server-side operations, full access
      - **User JWT**: Authenticated user operations with RLS

      **Security Best Practices:**
      {{#each security_best_practices}}
      - {{practice}}
      {{/each}}

  - id: api-integration
    title: API Integration
    template: |
      ## API Integration Examples

      ### Environment Configuration
      ```bash
      # Environment Variables
      NEXT_PUBLIC_SUPABASE_URL={{supabase_url}}
      NEXT_PUBLIC_SUPABASE_ANON_KEY={{anon_key}}
      SUPABASE_SERVICE_ROLE_KEY={{service_role_key}}
      ```

      ### Client Setup
      ```javascript
      // Supabase Client Setup
      import { createClient } from '@supabase/supabase-js'

      const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL
      const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY

      export const supabase = createClient(supabaseUrl, supabaseAnonKey)
      ```

      ### Authentication Examples
      ```javascript
      // User Authentication
      {{#each auth_examples}}
      // {{description}}
      {{code_example}}

      {{/each}}
      ```

      ### Data Operations
      {{#each api_examples}}
      #### {{operation_name}}
      ```javascript
      {{code_example}}
      ```
      {{#if description}}
      *{{description}}*
      {{/if}}

      {{/each}}

      ### Real-time Subscriptions
      ```javascript
      // Real-time Data Subscriptions
      {{#each realtime_examples}}
      // {{description}}
      {{code_example}}

      {{/each}}
      ```

  - id: performance
    title: Performance Guidelines
    template: |
      ## Performance Optimization

      ### Query Performance
      **Optimized Query Patterns:**
      {{#each optimized_queries}}
      - **{{query_name}}**: {{description}}
        ```sql
        {{sql_example}}
        ```
        *Performance: {{performance_note}}*

      {{/each}}

      ### Index Usage
      **Strategic Indexes:**
      {{#each performance_indexes}}
      - **{{index_name}}**: {{purpose}}
        - Query pattern: {{query_pattern}}
        - Performance gain: {{performance_gain}}
      {{/each}}

      ### Caching Strategies
      {{#each caching_strategies}}
      **{{strategy_name}}:**
      - Implementation: {{implementation}}
      - Use case: {{use_case}}
      - Performance impact: {{impact}}

      {{/each}}

      ### Monitoring and Metrics
      **Key Metrics to Monitor:**
      {{#each monitoring_metrics}}
      - {{metric_name}}: {{description}} (Target: {{target}})
      {{/each}}

  - id: development
    title: Development Guide
    template: |
      ## Development Guidelines

      ### Local Development Setup
      ```bash
      # 1. Install Dependencies
      npm install @supabase/supabase-js

      # 2. Environment Configuration
      cp .env.example .env.local
      # Add your Supabase credentials

      # 3. Database Connection Test
      {{test_connection_code}}
      ```

      ### Common Patterns
      {{#each development_patterns}}
      #### {{pattern_name}}
      **Use Case**: {{use_case}}

      ```{{language}}
      {{code_example}}
      ```

      **Best Practices:**
      {{#each best_practices}}
      - {{practice}}
      {{/each}}

      {{/each}}

      ### Error Handling
      ```javascript
      // Standard Error Handling Pattern
      {{error_handling_example}}
      ```

      ### Testing Guidelines
      **Database Testing:**
      {{#each testing_guidelines}}
      - {{guideline}}
      {{/each}}

  - id: deployment
    title: Deployment & Operations
    template: |
      ## Deployment and Operations

      ### Environment Management
      **Development → Staging → Production**

      {{#each environments}}
      **{{environment_name}}:**
      - Database URL: {{database_url}}
      - Purpose: {{purpose}}
      - Security level: {{security_level}}
      {{/each}}

      ### Schema Migrations
      **Migration Strategy:**
      {{#each migration_strategy}}
      - {{step}}
      {{/each}}

      **Rollback Procedures:**
      ```sql
      {{rollback_example}}
      ```

      ### Backup and Recovery
      **Automated Backups:**
      - Frequency: {{backup_frequency}}
      - Retention: {{backup_retention}}
      - Storage location: {{backup_location}}

      **Recovery Procedures:**
      {{#each recovery_procedures}}
      1. {{step}}
      {{/each}}

      ### Monitoring and Alerts
      **Monitoring Setup:**
      {{#each monitoring_setup}}
      - {{monitoring_item}}
      {{/each}}

      **Alert Thresholds:**
      {{#each alert_thresholds}}
      - {{metric}}: {{threshold}} (Action: {{action}})
      {{/each}}

  - id: troubleshooting
    title: Troubleshooting
    template: |
      ## Troubleshooting Guide

      ### Common Issues
      {{#each common_issues}}
      #### {{issue_title}}
      **Symptoms:** {{symptoms}}

      **Diagnosis:**
      {{#each diagnosis_steps}}
      1. {{step}}
      {{/each}}

      **Solution:**
      {{#each solution_steps}}
      1. {{step}}
      {{/each}}

      **Prevention:** {{prevention}}

      {{/each}}

      ### Performance Issues
      **Slow Query Diagnosis:**
      ```sql
      -- Query performance analysis
      {{slow_query_diagnosis}}
      ```

      **Index Analysis:**
      ```sql
      -- Index usage analysis
      {{index_analysis_query}}
      ```

      ### Security Issues
      **RLS Policy Testing:**
      ```sql
      -- Test RLS policies
      {{rls_testing_queries}}
      ```

      **Access Control Verification:**
      {{#each access_verification}}
      - {{verification_step}}
      {{/each}}

  - id: appendix
    title: Appendix
    template: |
      ## Appendix

      ### Database Schema SQL
      **Complete Schema File:** `schema-{{timestamp}}.sql`

      ### API Reference
      **Supabase Dashboard:** {{supabase_dashboard_url}}
      **API Documentation:** {{api_docs_url}}

      ### Support and Resources
      {{#each support_resources}}
      - **{{resource_name}}**: {{resource_url}}
      {{/each}}

      ### Change Log
      | Date | Version | Changes | Author |
      |------|---------|---------|--------|
      {{#each changelog}}
      | {{date}} | {{version}} | {{changes}} | {{author}} |
      {{/each}}

      ### Glossary
      {{#each glossary_terms}}
      **{{term}}**: {{definition}}
      {{/each}}

      ---

      *Generated by Database Wizard for {{project_name}} on {{created_date}}*

# Template Variables Schema
variables:
  project_name:
    type: string
    description: "Name of the project"
    required: true
  
  schema_version:
    type: string
    description: "Schema version number"
    default: "1.0"
  
  created_date:
    type: string
    description: "Documentation generation date"
    default: "{{current_date}}"
  
  project_ref:
    type: string
    description: "Supabase project reference"
    required: false
  
  project_url:
    type: string
    description: "Supabase project URL"
    required: false
  
  postgres_version:
    type: string
    description: "PostgreSQL version"
    default: "15"
  
  auth_method:
    type: string
    description: "Authentication method"
    default: "Email/Password + OAuth"
  
  tables:
    type: array
    description: "Database tables documentation"
    required: true
  
  security_policies:
    type: array
    description: "RLS security policies"
    required: true
  
  api_examples:
    type: array
    description: "API integration examples"
    required: true
  
  performance_indexes:
    type: array
    description: "Performance index documentation"
    required: true
  
  common_issues:
    type: array
    description: "Troubleshooting information"
    required: true

# Usage Examples
examples:
  basic_documentation:
    project_name: "TaskFlow SaaS"
    tables:
      - table_name: "users"
        description: "User profiles and authentication"
        columns:
          - column_name: "id"
            data_type: "UUID"
            constraints: "PRIMARY KEY"
            description: "Unique user identifier"
          - column_name: "email"
            data_type: "TEXT"
            constraints: "UNIQUE NOT NULL"
            description: "User email address"
    
    api_examples:
      - operation_name: "Get User Profile"
        code_example: |
          const { data: user } = await supabase
            .from('users')
            .select('*')
            .eq('id', userId)
            .single()
        
    common_issues:
      - issue_title: "RLS Policy Blocking Queries"
        symptoms: "Cannot fetch data even with valid authentication"
        diagnosis_steps:
          - "Check if auth.uid() is available in current context"
          - "Verify RLS policies match application logic"
        solution_steps:
          - "Ensure user is properly authenticated"
          - "Review and update RLS policy conditions"