database-patterns ยท diff

v2.0.0 to v2.0.0

44 added, 3 removed. Audit A to A.

What this update changes in the checks

No check changed: every fixed check gives the same answer before and after this update.

A tripped check is a label, not a verdict: read the matched text and the lines below. This week's updates that newly trip a check.

---
version: "2.0.0"
- globs: src/**/*.ts,migrations/*.sql
- description: Database patterns and D1 query best practices
+ globs: "src/**/*.ts,migrations/*.sql"
+ alwaysApply: true
+ description: "Database patterns and D1 query best practices"
lastUpdated: "2025-10-08"
- dependencies: ["quality-standards", "api-patterns"]
+ 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