git:20251008.4a59e8e to v2.0.0

42 added, 9 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
+ lastUpdated: "2025-10-08"
+ dependencies: ["quality-standards", "api-patterns"]
---
# Database Patterns
## 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();
- // Convert with unknown cast (required for D1Result type)
- const movements = result.results as unknown as Array<{
+ // 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 result.results;
+ return normalizeD1Result(result);
} catch (error) {
- console.error(`[${requestId}] Database error:`, error);
- throw new Error(`Database query failed: ${error.message}`);
+ 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** cast D1 results with `as unknown as`
+ 2. ✅ **Always** normalize D1 results with `normalizeD1Result()` ⭐ **UPDATED**
3. ✅ **Always** use indexes for performance
4. ✅ **Always** limit query results
- 5. ✅ **Always** handle database errors
+ 5. ✅ **Always** handle database errors with StructuredLogger ⭐ **UPDATED**
6. ✅ **Always** use proper migration naming
7. ❌ **Never** use string interpolation in SQL
- 8. ❌ **Never** forget to bind parameters
+ 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