Database Schema

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

Audit 0.4.1 · source sha ea59a0c53d79 · 14 Oct 2026Report an issue or request a re-scan

• Publisheralinaqi GitHub userOwner of the repository this skill is read from
• Source folderalinaqi/claude-bootstrap/skills/database-schemaThe exact folder we audited
• LicenceMITAs the repository states it
• Commit pinned for this verdictea59a0c53d79A new audit runs when the source changes
• Repository statusNot archivedPushed today
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its description: Schema awareness - read before coding, type generation, prevent column errors

---
name: database-schema
description: Schema awareness - read before coding, type generation, prevent column errors
when-to-use: Before writing any database queries or modifying data models
user-invocable: false
paths: ["**/schema.*", "**/migrations/**", "**/models/**", "**/*.prisma", "**/drizzle/**"]
effort: medium
---

# Database Schema Awareness Skill


**Problem:** Claude forgets schema details mid-session - wrong column names, missing fields, incorrect types. TDD catches this at runtime, but we can prevent it earlier.

---

## Core Rule: Read Schema Before Writing Database Code

**MANDATORY: Before writing ANY code that touches the database:**

```
┌─────────────────────────────────────────────────────────────┐
│  1. READ the schema file (see locations below)              │
│  2. VERIFY columns/types you're about to use exist          │
│  3. REFERENCE schema in your response when writing queries  │
│  4. TYPE-CHECK using generated types (Drizzle/Prisma/etc)   │
└─────────────────────────────────────────────────────────────┘
```

**If schema file doesn't exist → CREATE IT before proceeding.**

---

## Schema File Locations (By Stack)

| Stack | Schema Location | Type Generation |
|-------|-----------------|-----------------|
| **Drizzle** | `src/db/schema.ts` or `drizzle/schema.ts` | Built-in TypeScript |
| **Prisma** | `prisma/schema.prisma` | `npx prisma generate` |
| **Supabase** | `supabase/migrations/*.sql` + types | `supabase gen types typescript` |
| **SQLAlchemy** | `app/models/*.py` or `src/models.py` | Pydantic models |
| **TypeORM** | `src/entities/*.ts` | Decorators = types |
| **Raw SQL** | `schema.sql` or `migrations/` | Manual types required |

### Schema Reference File (Recommended)

Create `_project_specs/schema-reference.md` for quick lookup:

```markdown
# Database Schema Reference

*Auto-generated or manually maintained. Claude: READ THIS before database work.*

## Tables

### users
| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | uuid | NO | gen_random_uuid() | PK |
| email | text | NO | - | Unique |
| name | text | YES | - | Display name |
| created_at | timestamptz | NO | now() | - |
| updated_at | timestamptz | NO | now() | - |

### orders
| Column | Type | Nullable | Default | Notes |
|--------|------|----------|---------|-------|
| id | uuid | NO | gen_random_uuid() | PK |
| user_id | uuid | NO | - | FK → users.id |
| status | text | NO | 'pending' | enum: pending/paid/shipped/delivered |
| total_cents | integer | NO | - | Amount in cents |
| created_at | timestamptz | NO | now() | - |

## Relationships
- users 1:N orders (user_id)

## Enums
- order_status: pending, paid, shipped, delivered
```

---

## Pre-Code Checklist (Database Work)

Before writing any database code, Claude MUST:

```markdown
### Schema Verification Checklist
- [ ] Read schema file: `[path to schema]`
- [ ] Columns I'm using exist: [list columns]
- [ ] Types match my code: [list type mappings]
- [ ] Relationships are correct: [list FKs]
- [ ] Nullable fields handled: [list nullable columns]
```

**Example in practice:**

```markdown
### Schema Verification for TODO-042 (Add order history endpoint)

- [x] Read schema: `src/db/schema.ts`
- [x] Columns exist: orders.id, orders.user_id, orders.status, orders.total_cents, orders.created_at
- [x] Types: id=uuid→string, total_cents=integer→number, status=text→OrderStatus enum
- [x] Relationships: orders.user_id → users.id (many-to-one)
- [x] Nullable: none of these columns are nullable
```

---

## Type Generation Commands

### Drizzle (TypeScript)

```typescript
// Schema defines types automatically
// src/db/schema.ts
import { pgTable, uuid, text, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id),
  status: text('status').notNull().default('pending'),
  totalCents: integer('total_cents').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

// Inferred types - USE THESE
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Order = typeof orders.$inferSelect;
export type NewOrder = typeof orders.$inferInsert;
```

### Prisma

```prisma
// prisma/schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

model Order {
  id         String   @id @default(uuid())
  userId     String   @map("user_id")
  user       User     @relation(fields: [userId], references: [id])
  status     String   @default("pending")
  totalCents Int      @map("total_cents")
  createdAt  DateTime @default(now()) @map("created_at")

  @@map("orders")
}
```

```bash
# Generate types after schema changes
npx prisma generate
```

### Supabase

```bash
# Generate TypeScript types from live database
supabase gen types typescript --local > src/types/database.ts

# Or from remote
supabase gen types typescript --project-id your-project-id > src/types/database.ts
```

```typescript
// Use generated types
import { Database } from '@/types/database';

type User = Database['public']['Tables']['users']['Row'];
type NewUser = Database['public']['Tables']['users']['Insert'];
type Order = Database['public']['Tables']['orders']['Row'];
```

### SQLAlchemy (Python)

```python
# app/models/user.py
from sqlalchemy import Column, String, DateTime
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql import func
from app.db import Base
import uuid

class User(Base):
    __tablename__ = "users"

    id = Column(UUID(as_uuid=True), primary

Excerpt, first 6,000 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/alinaqi/claude-bootstrap
git -C claude-bootstrap checkout ea59a0c53d79

Works in

1. 2026-10-14
   ✓SAFE B 89 / 100

   first audit · source ea59a0c53d79

Questions about Database Schema

• github.com/alinaqi/claude-bootstrap/tree/main/skills/database-sc — the audited source at commit ea59a0c53d79 and every finding aboveObserved · 14 Oct 2026

Spotted something wrong? Report a correction · Methodology