Database Operations Skill

Status: Active Last Updated: 2026-01-08

Overview

This skill provides guidance for database operations with the Claude Family PostgreSQL database (ai_company_foundation).

Quick Reference

Connection

python
1# Python (psycopg3)
2import psycopg
3
4conn = psycopg.connect(
5    host="localhost",
6    port=5432,
7    dbname="ai_company_foundation",  # Note: dbname, not database
8    user="postgres",
9    password="your_password"
10)

Primary Schema

Use claude.* for all new work. Legacy schemas (claude_family, claude_pm, claude_mission_control) are deprecated.

Core Tables

Data Gateway (MANDATORY)

Before writing to any constrained column, check valid values:

sql
1SELECT valid_values FROM claude.column_registry
2WHERE table_name = 'TABLE' AND column_name = 'COLUMN';

Common Constraints

Key Gotchas

1. psycopg3 vs psycopg2

python
1# psycopg2 uses 'database'
2conn = psycopg2.connect(database="ai_company_foundation")
3
4# psycopg3 uses 'dbname'
5conn = psycopg.connect(dbname="ai_company_foundation")

2. UUID Array Casting

sql
1-- WRONG: PostgreSQL doesn't auto-cast text[] to uuid[]
2INSERT INTO table (uuid_array_col) VALUES (ARRAY['id1', 'id2']);
3
4-- CORRECT: Explicit cast
5INSERT INTO table (uuid_array_col) VALUES (ARRAY['id1', 'id2']::uuid[]);

3. DISTINCT with ORDER BY

sql
1-- WRONG: Column in ORDER BY must be in SELECT for DISTINCT
2SELECT DISTINCT title FROM knowledge ORDER BY created_at;
3
4-- CORRECT: Include the ORDER BY column
5SELECT DISTINCT title, created_at FROM knowledge ORDER BY created_at;

4. Schema Consolidation

Legacy functions in claude_family.* may reference deleted tables. Check function definitions with:

sql
1SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'function_name';

Replace with direct queries to claude.* schema.

Common Queries

sql
1-- Check project status
2SELECT * FROM claude.projects WHERE project_name = 'your-project';
3
4-- Recent sessions
5SELECT session_start, summary FROM claude.sessions
6WHERE project_name = 'your-project' ORDER BY session_start DESC LIMIT 5;
7
8-- Valid values for any field
9SELECT valid_values FROM claude.column_registry
10WHERE table_name = 'TABLE' AND column_name = 'COLUMN';
11
12-- Check open feedback
13SELECT feedback_id::text, feedback_type, description, status
14FROM claude.feedback
15WHERE status IN ('new', 'in_progress')
16ORDER BY created_at DESC;

Related Skills

• testing-patterns - Database testing approaches
• feature-workflow - Feature tracking in database

Version: 1.0