database-patterns ยท v2.0.0 ยท 2025-10-08 ยท sha256 6c2870855ffe20c3

database-patterns v2.0.0A

Immutable. This exact content never changes and is served at /api/v1/blob/6c2870855ffe20c3.

---
version: "2.0.0"
globs: "src/**/*.ts,migrations/*.sql"
alwaysApply: true
description: "Database patterns and D1 query best practices"
lastUpdated: "2025-10-08"
dependencies: ["quality-standards", "api-patterns", "bun-runtime", "database-patterns"]
---

# Database Patterns

## ๐Ÿ” Code Searchability Patterns

### Find database-patterns Issues with ast-grep

```bash
# Find env.$DB.prepare($QUERY)
# Find await stmt.bind($$$).all()
# Find await stmt.bind($$$).first()
# Find normalizeD1Result<$$$>($$$)
ast-grep --pattern 'env.$DB.prepare($QUERY)' src/
ast-grep --pattern 'await stmt.bind($$$).all()' src/
ast-grep --pattern 'await stmt.bind($$$).first()' src/
ast-grep --pattern 'normalizeD1Result<$$$>($$$)' src/
sg -p 'env.$DB.prepare($QUERY)' src/
sg -p 'await' src/
sg -p 'await' src/
sg -p 'normalizeD1Result<$$$>($$$)' src/
```

### database-patterns Discovery Commands

```bash
sg search "env.ANALYTICS.prepare" src/
sg search "normalizeD1Result" src/
sg search ".bind(" src/
sg search ".all()" src/
```

### Search Examples

```bash
# Find all database queries
sg search 'env.$DB.prepare($QUERY)' src/
# Find D1 result normalization
sg search 'await' src/
# Find parameterized queries
sg search 'await' src/
# Find query execution patterns
sg search 'normalizeD1Result<$$$>($$$)' src/
```
## D1 Database Usage

### Environment Bindings

**Primary Databases:**
- `ANALYTICS` - Main betting analytics database
- `RAW_FEED_DB` - Raw feed database

**Configuration:** [wrangler.toml](mdc:wrangler.toml)

### Query Patterns

#### Parameterized Queries (CRITICAL)

```typescript
// โœ… CORRECT: Parameterized query
const result = await env.ANALYTICS.prepare(`
  SELECT * FROM line_movements 
  WHERE eid = ? AND ts > ?
  LIMIT ?
`).bind(eventID, timestamp, limit).all();

// โŒ WRONG: String interpolation (SQL injection risk)
const result = await env.ANALYTICS.prepare(`
  SELECT * FROM line_movements 
  WHERE eid = '${eventID}' AND ts > '${timestamp}'
  LIMIT ${limit}
`).all();
```

#### Type Safety

```typescript
import { normalizeD1Result } from '../utils/request';

// Query with type conversion
const result = await env.ANALYTICS.prepare(`
  SELECT eid, mt, ts, old_line, new_line
  FROM line_movements
  WHERE eid = ?
  ORDER BY ts DESC
  LIMIT ?
`).bind(eventID, limit).all();

// Normalize with proper typing
type Movement = {
  eid: string;
  mt: string;
  ts: string;
  old_line: number;
  new_line: number;
};

const movements = normalizeD1Result<Movement>(result);
```

### Database Tables

#### Core Tables

**Analytics Tables:**
- `line_movements` - Line change tracking
- `sharp_indicators` - Customer profiling
- `bet_history` - Historical bets
- `hold_tracking` - Hold percentage history
- `exposure_tracking` - Real-time exposure

**MCP Tables:**
- `sharp_indicators` - Customer profiling
- `line_movements` - Line changes
- `bet_history` - Historical bets
- `hold_tracking` - Hold % history
- `exposure_tracking` - Real-time exposure

#### Schema Patterns

```sql
-- Migration naming: NNNN_description.sql
-- Example: 0001_initial_schema.sql

-- Table with TTL
CREATE TABLE line_movements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  eid TEXT NOT NULL,
  mt TEXT NOT NULL,
  ts TEXT NOT NULL,
  old_line REAL,
  new_line REAL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Index for performance
CREATE INDEX idx_line_movements_eid_ts ON line_movements(eid, ts);
```

### Migration Patterns

#### File Naming

```
migrations/
โ”œโ”€โ”€ 0001_initial_schema.sql
โ”œโ”€โ”€ 0002_add_ttl.sql
โ”œโ”€โ”€ 0003_mcp_tables.sql
โ””โ”€โ”€ 0004_test_data.sql
```

#### Migration Commands

```bash
# Apply migrations to remote
wrangler d1 migrations apply betting-analytics --remote

# Apply to local
wrangler d1 migrations apply betting-analytics --local

# List migrations
wrangler d1 migrations list betting-analytics
```

### Performance Patterns

#### Query Optimization

```typescript
// โœ… GOOD: Use indexes
const result = await env.ANALYTICS.prepare(`
  SELECT * FROM line_movements 
  WHERE eid = ? AND ts > ?
  ORDER BY ts DESC
  LIMIT ?
`).bind(eventID, timestamp, limit).all();

// โœ… GOOD: Limit results
const result = await env.ANALYTICS.prepare(`
  SELECT * FROM table 
  WHERE condition = ?
  LIMIT 100
`).bind(value).all();
```

#### Batch Operations

```typescript
// โœ… GOOD: Batch inserts
const stmt = env.ANALYTICS.prepare(`
  INSERT INTO line_movements (eid, mt, ts, old_line, new_line)
  VALUES (?, ?, ?, ?, ?)
`);

for (const movement of movements) {
  await stmt.bind(
    movement.eid,
    movement.mt,
    movement.ts,
    movement.old_line,
    movement.new_line
  ).run();
}
```

### Error Handling

#### Database Error Patterns

```typescript
import { createLogger } from '../utils/logger';
import { normalizeD1Result } from '../utils/request';

const logger = createLogger(request);

try {
  const result = await env.ANALYTICS.prepare(query).bind(...params).all();
  return normalizeD1Result(result);
} catch (error) {
  logger.error('database_query_failed', {
    query: query.substring(0, 100), // Truncate for logging
    errorType: error instanceof Error ? error.name : 'Unknown'
  }, error as Error);

  throw new Error(`Database query failed: ${error instanceof Error ? error.message : 'Unknown error'}`);
}
```

### Testing Database

#### Mock Environment

```typescript
const mockEnv = {
  ANALYTICS: {
    prepare: (query: string) => ({
      bind: (...params: any[]) => ({
        first: vi.fn().mockResolvedValue(null),
        all: vi.fn().mockResolvedValue({ results: [] }),
        run: vi.fn().mockResolvedValue({ success: true })
      }),
      first: vi.fn().mockResolvedValue(null),
      all: vi.fn().mockResolvedValue({ results: [] }),
      run: vi.fn().mockResolvedValue({ success: true })
    })
  }
};
```

#### Test Data Patterns

```typescript
// Test data setup
const testMovements = [
  {
    eid: 'test-event-1',
    mt: 'SPREAD',
    ts: '2025-01-01T00:00:00Z',
    old_line: -3.5,
    new_line: -4.0
  }
];

// Mock database response
(mockEnv.ANALYTICS.prepare as any).mockReturnValue({
  bind: vi.fn().mockReturnValue({
    all: vi.fn().mockResolvedValue({ results: testMovements })
  })
});
```

## Key Files

- [Database Migrations](mdc:migrations/) - Schema definitions
- [MCP Handlers](mdc:src/mcp/handlers/) - Database usage examples
- [API Routes](mdc:src/api/routes.ts) - Query patterns
- [Wrangler Config](mdc:wrangler.toml) - Database bindings

## Rules Summary

1. โœ… **Always** use parameterized queries (`.bind()`)
2. โœ… **Always** normalize D1 results with `normalizeD1Result()` โญ **UPDATED**
3. โœ… **Always** use indexes for performance
4. โœ… **Always** limit query results
5. โœ… **Always** handle database errors with StructuredLogger โญ **UPDATED**
6. โœ… **Always** use proper migration naming
7. โŒ **Never** use string interpolation in SQL
8. โŒ **Never** use `as unknown as` (use `normalizeD1Result()` instead) โญ **UPDATED**
9. โŒ **Never** ignore database errors
10. โŒ **Never** create tables without indexes

## Related Rules

- [Quality Standards](mdc:.cursor/rules/quality-standards.mdc) - Code quality standards โญ **NEW**
- [API Patterns](mdc:.cursor/rules/api-patterns.mdc) - API validation & error handling

## Related Documentation

- [docs/QUALITY_STANDARDS.md](mdc:docs/QUALITY_STANDARDS.md) - Complete quality guide โญ **NEW**
- [src/utils/request.ts](mdc:src/utils/request.ts) - Request & D1 utilities โญ **NEW**
- [src/utils/logger.ts](mdc:src/utils/logger.ts) - Logging utilities โญ **NEW**
- [src/shared/constants.ts](mdc:src/shared/constants.ts) - All constants โญ **NEW**

---

**Status:** Production patterns established
**Last Updated:** 2025-10-08