Database Function Lint Manager

Lint PostgreSQL functions across environments (local, staging, production), analyze function usage in the codebase, and generate actionable reports for fixing or dropping broken functions.

When to Use This Skill

DO use this skill when:

• Running supabase db lint and need to analyze/categorize results
• Detecting broken functions referencing non-existent columns/tables
• Analyzing which database functions are actually used in the codebase
• Generating cleanup recommendations for unused legacy functions
• Pre-deployment database validation
• Investigating database errors related to function calls

DO NOT use this skill when:

• Creating new database migrations (use database-migration-manager)
• Writing RLS policies (use rls-policy-generator)
• Fixing a single known function (just create the migration directly)

---

Critical Rules (NEVER VIOLATE)

1. REPORT ONLY - Never auto-generate fix migrations without explicit user approval
2. ANALYZE USAGE FIRST - Always check if a function is used before recommending actions
3. CATEGORIZE BY SEVERITY - Separate critical (used) from low priority (unused)
4. PRESERVE FUNCTION SIGNATURES - When fixing, maintain existing function signatures for backwards compatibility
5. TEST LOCALLY FIRST - Always test fixes on local before staging/production

---

Quick Reference

Run Lint by Environment

# Local database (requires Docker/Supabase running)
cd apps/web && pnpm supabase db lint --local -s public

# Staging database
pnpm supabase db lint --db-url "postgresql://postgres.PROJECT_REF:PASSWORD@aws-1-eu-central-1.pooler.supabase.com:5432/postgres" -s public

# Production database (linked project)
cd apps/web && pnpm supabase db lint --linked -s public

Analyze Function Usage

# Search for function usage in codebase
grep -rn "\.rpc(['\"]function_name['\"]" apps/web packages/features

# Check if function is called from migrations
grep -rn "function_name" apps/web/supabase/migrations/

Output Format Options

# JSON output for parsing
pnpm supabase db lint --linked -o json

# Pretty output for human reading
pnpm supabase db lint --linked -o pretty

---

Issue Categories

1. Missing Column

Error: column X does not exist Cause: Function references a column that was renamed, dropped, or never existed Fix Options:

• Update function to use correct column name
• Drop function if unused

2. Missing Table

Error: relation X does not exist Cause: Function references a table that was renamed or dropped Fix Options:

• Update function to use correct table name
• Drop function if unused

3. Type Mismatch

Error: structure of query does not match function result type Cause: Function return type doesn't match actual SELECT columns Fix Options:

• Update function return type definition
• Update SELECT to match return type

4. Ambiguous Reference

Error: column reference X is ambiguous Cause: Column name exists in multiple tables in the query Fix Options:

• Qualify column with table alias (e.g., t.user_id instead of user_id)
• Rename parameter to avoid collision

5. Unused Variable

Warning: never read variable X Cause: Variable declared but not used in function body Fix Options:

• Remove unused variable declaration
• Use the variable as intended

---

Usage Analysis Patterns

TypeScript/JavaScript Patterns

// Direct RPC call
const { data } = await supabase.rpc('function_name', { param: value });

// Chained RPC
await client.rpc('function_name').single();

// In service files
return this.client.rpc('function_name', params);

SQL Patterns (Migrations/Triggers)

-- Direct call
SELECT public.function_name(arg1, arg2);

-- In trigger
EXECUTE FUNCTION public.function_name();

-- In policy
USING (public.function_name())

Search Commands

# Find all RPC calls to a function
grep -rn "rpc(['\"]get_events_with_cast['\"]" apps/web packages/features

# Find SQL references
grep -rn "get_events_with_cast" apps/web/supabase/

# Find in tests
grep -rn "get_events_with_cast" apps/web/app/__tests__/

---

Report Template

When generating a lint report, use this format:

# Database Function Lint Report

**Environment**: Production | Staging | Local
**Date**: YYYY-MM-DD HH:MM
**Total Issues**: N (M errors, K warnings)

## Summary

| Category | Count | Used | Unused |
|----------|-------|------|--------|
| Missing Column | N | N | N |
| Missing Table | N | N | N |
| Type Mismatch | N | N | N |
| Ambiguous Reference | N | N | N |
| Unused Variable | N | N | N |

## Critical Issues (Used Functions - Must Fix)

### function_name
- **Error**: Description of the error
- **SQL State**: XXXXX
- **Location**: Migration file where function is defined
- **Line**: Line number in function
- **Usage Found**:
  - file/path.ts:123
  - file/path2.ts:456
- **Recommended Action**: Create migration to fix X

<details>
<summary>Problematic Query</summary>

```sql
SELECT column_that_doesnt_exist FROM table

Low Priority (Unused Functions - Consider Dropping)

function_name

• Error: Description of the error
• Location: Migration file
• Usage Found: NONE
• Recommended Action: Create DROP FUNCTION IF EXISTS migration

Warnings (Non-Critical)

function_name

• Warning: unused variable "v_temp"
• Impact: No runtime impact, code cleanliness only
• Recommended Action: Low priority cleanup

Action Summary

• Must Fix: N functions actively in use with errors
• Consider Drop: N functions with no usage found
• Investigate: N functions with unclear status
• Low Priority: N warnings (non-blocking)

Next Steps

1. For critical issues, create fix migrations:

   pnpm supabase:web migrations new fix_function_name
   

2. For unused functions, create drop migrations:

   DROP FUNCTION IF EXISTS public.function_name(param_types);
   

3. Test locally before deploying:

   pnpm supabase:web:reset
   pnpm supabase:web db lint --local
   

---

## Known Issues Reference

These functions were detected as broken in production (2025-12-03):

| Function | Error | Category | Likely Status |
|----------|-------|----------|---------------|
| `get_user_bookmarks` | profile_bookmarks.notes doesn't exist | Missing Column | Legacy/Unused |
| `is_following_profile` | following_profile_id doesn't exist | Missing Column | Legacy/Unused |
| `get_user_following` | following_profile_id doesn't exist | Missing Column | Legacy/Unused |
| `get_user_upcoming_events` | participations table doesn't exist | Missing Table | Legacy/Unused |
| `update_identity_verification_status` | admin_notes doesn't exist | Missing Column | Legacy/Unused |
| `user_has_verified_identity` | iv.status doesn't exist | Missing Column | Legacy/Unused |
| `get_user_identity_verification` | iv.status doesn't exist | Missing Column | Legacy/Unused |
| `get_dancer_availability` | available_date doesn't exist | Missing Column | Legacy/Unused |
| `transfer_team_account_ownership` | accounts_memberships.role doesn't exist | Missing Column | Legacy/Unused |
| `log_role_change` | role_change_audit.user_id doesn't exist | Missing Column | Legacy/Unused |
| `get_dancer_upcoming_events` | return type mismatch | Type Mismatch | Needs Investigation |
| `get_profile_missing_fields` | primary_training_method doesn't exist | Missing Column | Possibly Used |
| `accept_admin_invitation` | ambiguous user_id reference | Ambiguous Reference | Possibly Used |
| `get_events_with_cast` | e.name doesn't exist (should be e.title) | Missing Column | Likely Used |
| `get_event_with_cast_by_id` | e.name doesn't exist | Missing Column | Likely Used |
| `get_user_events` | event_participations table doesn't exist | Missing Table | Legacy/Unused |
| `create_hire_order_with_items` | unit_price column doesn't exist | Missing Column | Needs Investigation |

---

## Environment Configuration

### Local
- Requires Docker and Supabase running (`pnpm supabase:web:start`)
- Uses `--local` flag
- Safe for testing fixes

### Staging
**Project**: `hxpcknyqswetsqmqmeep`
**Connection**:
```bash
# Load password from .env.local (preferred)
source apps/web/.env.local 2>/dev/null

# Or use environment variable directly
export SUPABASE_DB_PASSWORD_STAGING="your_password"

# 1Password fallback (if not in .env.local)
if [ -z "$SUPABASE_DB_PASSWORD_STAGING" ]; then
  SUPABASE_DB_PASSWORD_STAGING=$(op item get rkzjnr5ffy5u6iojnsq3clnmia --fields notesPlain --reveal)
fi

Production

Project: csjruhqyqzzqxnfeyiaf

• Uses --linked flag (requires supabase link setup)
• READ ONLY - never apply fixes directly

---

Workflow: Fixing Broken Functions

Step 1: Generate Report

# Run lint on production
cd apps/web && pnpm supabase db lint --linked -s public -o json > lint-report.json

Step 2: Analyze Usage

For each function in the report:

# Check codebase for usage
grep -rn "rpc(['\"]function_name['\"]" apps/web packages/features

# Check migrations for references
grep -rn "function_name" apps/web/supabase/migrations/

Step 3: Categorize

• CRITICAL: Function is used in production code → Must fix
• LOW: Function has no usage → Consider dropping
• INVESTIGATE: Unclear if used → Research before action

Step 4: Create Fix Migration

# Create new migration
pnpm supabase:web migrations new fix_broken_function_name

# Edit the migration file with the fix

Step 5: Test Locally

pnpm supabase:web:reset
pnpm supabase db lint --local -s public

Step 6: Deploy

Follow standard migration deployment process via database-migration-manager skill.

---

Integration with Existing Tools

validate-db-contracts.sh

Located at .claude/skills/db-lint-manager/scripts/validate-db-contracts.sh

• Validates specific functions against table schemas
• Complements lint by checking column existence

validate-function-schema.sh

Located at .claude/skills/db-lint-manager/scripts/validate-function-schema.sh

• Validates atomic_profile_update function specifically
• Can be extended for other critical functions

Pre-commit Hooks

The lint can be integrated into pre-commit hooks for migrations:

# In lefthook.yml
pre-commit:
  commands:
    db-lint:
      glob: "apps/web/supabase/migrations/*.sql"
      run: cd apps/web && pnpm supabase db lint --local -s public

---

Troubleshooting

"Cannot connect to database"

• Ensure Docker is running for local lint
• Check credentials for staging/production
• Try session mode port (5432) if transaction mode (6543) fails

"Function not found in lint output"

• Function may be in a different schema (check -s flag)
• Function may have been dropped already
• Function may be defined in a different way (trigger vs standalone)

"False positive - function actually works"

• plpgsql_check is conservative; some valid patterns may flag
• Test the function manually to verify
• Add to known false positives list if confirmed working

---

Related Skills

• database-migration-manager: For creating and deploying migrations
• rls-policy-generator: For RLS policy issues
• quality-auto-fixer: For general code quality checks