database-naming-standards.md

1# Database Naming Standards: Code-First Approach
2
3## Overview
4
5This document establishes database naming standards that prioritize consistency with TypeScript/JavaScript code conventions. Our approach eliminates mapping overhead and reduces bugs by using identical naming across all layers.
6
7## Core Principle: Code-First Naming
8
9**The database naming follows the code naming conventions, not the reverse.**
10
11In TypeScript/JavaScript, we use camelCase for properties and methods. Therefore, our database columns, parameters, and entity properties all use camelCase to maintain perfect consistency.
12
13## Why Code-First Naming
14
15### 1. **Direct Object Mapping**
16No translation layer needed between database results and TypeScript objects:
17
18```typescript
19// ✅ PERFECT: Database result maps directly to TypeScript object
20const user = await db.queryOne<User>("SELECT id, email, firstName FROM Users WHERE id = @id", { id });
21// Result: { id: "123", email: "test@example.com", firstName: "John" }
22// No mapping required - properties match exactly
23```
24
25### 2. **Eliminates Mapping Bugs**
26Common mapping errors are completely avoided:
27
28```typescript
29// ❌ MAPPING ERRORS (with snake_case database):
30// Developer forgets to map snake_case to camelCase
31const user = { 
32  id: result.id,
33  email: result.email,
34  firstName: result.first_name,  // Easy to miss this mapping
35  createdAt: result.created_at   // Or forget this one
36};
37
38// ✅ NO MAPPING NEEDED (with camelCase database):
39const user = result;  // Direct assignment, no errors possible
40```
41
42### 3. **Reduced Development Overhead**
43- No mapping configuration to maintain
44- No translation utilities to write/debug
45- Fewer places for bugs to hide
46- Simpler code reviews
47
48### 4. **Better ORM Compatibility**
49Most TypeScript ORMs work more naturally with camelCase:
50
51```typescript
52// ✅ NATURAL: ORM properties match database directly
53@Entity('Users')
54class User {
55  @Column()
56  id: string;
57  
58  @Column()
59  email: string;
60  
61  @Column()
62  firstName: string;  // Maps directly to database column
63}
64```
65
66### 5. **Parameter Consistency**
67SQL parameters match object properties exactly:
68
69```typescript
70// ✅ CONSISTENT: Same names throughout
71const user = { id: "123", email: "new@example.com" };
72await db.execute("UPDATE Users SET email = @email WHERE id = @id", user);
73// Object properties match parameter names exactly
74```
75
76## Database Naming Standards
77
78### Table Names
79- **PascalCase**: `Users`, `UserSessions`, `ApiKeys`, `ProjectMembers`
80- Rationale: Clear distinction from columns, matches C# entity conventions
81
82### Column Names  
83- **camelCase**: `id`, `email`, `firstName`, `createdAt`, `lastLoginAt`
84- Rationale: Matches TypeScript property naming exactly
85
86### Primary Keys
87- **Always**: `id` (camelCase)
88- **Foreign Keys**: `userId`, `projectId`, `organizationId` (camelCase)
89
90### SQL Parameters
91- **camelCase**: `@id`, `@email`, `@firstName`, `@createdAt`
92- Rationale: Match column names and object properties exactly
93
94### Indexes
95- **Format**: `IX_{TableName}_{columnName}`: `IX_Users_email`, `IX_Users_createdAt`
96
97## Standard Implementation Pattern
98
99### Entity Interface
100```typescript
101interface User {
102  id: string;
103  email: string;
104  firstName: string;
105  lastName: string;
106  emailVerified: boolean;
107  emailVerificationToken?: string;
108  emailVerificationExpires?: Date;
109  createdAt: Date;
110  updatedAt?: Date;
111}
112```
113
114### Database Schema
115```sql
116CREATE TABLE Users (
117    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
118    email NVARCHAR(255) NOT NULL UNIQUE,
119    firstName NVARCHAR(255) NOT NULL,
120    lastName NVARCHAR(255) NOT NULL,
121    emailVerified BIT NOT NULL DEFAULT 0,
122    emailVerificationToken UNIQUEIDENTIFIER NULL,
123    emailVerificationExpires DATETIME2 NULL,
124    createdAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
125    updatedAt DATETIME2 NULL
126);
127```
128
129### Repository Queries
130```typescript
131// ✅ PERFECT CONSISTENCY: All names match across layers
132async findByEmail(email: string): Promise<User | null> {
133  const query = `SELECT * FROM Users WHERE email = @email`;
134  return await this.db.queryOne<User>(query, { email });
135}
136
137async updateProfile(id: string, data: { firstName?: string; lastName?: string }): Promise<User | null> {
138  const updates = Object.keys(data).map(key => `${key} = @${key}`).join(', ');
139  const query = `
140    UPDATE Users 
141    SET ${updates}, updatedAt = GETUTCDATE()
142    OUTPUT INSERTED.*
143    WHERE id = @id
144  `;
145  
146  const result = await this.db.query<User>(query, { ...data, id });
147  return result[0] || null;
148}
149```
150
151## Required Standard Columns
152
153### Every Entity Table Must Include:
154- `id` - Primary key (UNIQUEIDENTIFIER)
155- `createdAt` - Creation timestamp (DATETIME2, NOT NULL, DEFAULT GETUTCDATE())  
156- `updatedAt` - Last update timestamp (DATETIME2, NULL)
157
158### Audit Tables Should Include:
159- `modifiedBy` - User who made the change
160- `modifiedAt` - When change was made
161- `changeReason` - Optional reason for change
162
163## AI Model Instructions
164
165### For Code Generation:
166When generating database-related code, always:
167
1681. **Use camelCase for all database columns and entity properties**
1692. **Use camelCase for all SQL parameters matching column names exactly**
1703. **Use PascalCase for table names only**
1714. **Ensure parameter names match column names exactly: `@email` matches column `email`**
1725. **Include standard columns: `id`, `createdAt`, `updatedAt`**
173
174### Template Pattern:
175```typescript
176interface EntityName {
177  id: string;
178  someProperty: string;        // camelCase property
179  anotherProperty: number;     // camelCase property
180  createdAt: Date;
181  updatedAt?: Date;
182}
183
184// SQL uses same naming
185const query = `
186  SELECT id, someProperty, anotherProperty, createdAt, updatedAt 
187  FROM EntityNames 
188  WHERE someProperty = @someProperty AND id = @id
189`;
190```
191
192## Benefits Summary
193
194### Development Benefits:
195- **Faster Development**: No mapping code to write
196- **Fewer Bugs**: No translation errors possible
197- **Simpler Debugging**: Same names in logs, database, and code
198- **Better Tooling**: IDE autocomplete works across all layers
199
200### Maintenance Benefits:
201- **Easier Refactoring**: Rename once, works everywhere
202- **Clearer Code Reviews**: No mental translation needed
203- **Reduced Complexity**: Fewer abstraction layers
204- **Better Documentation**: Same terminology throughout
205
206### Performance Benefits:
207- **No Mapping Overhead**: Direct object assignment
208- **Smaller Bundle Size**: No mapping utilities needed
209- **Better ORM Performance**: Natural property mapping
210
211## Migration Notes
212
213When updating existing systems:
2141. Rename database columns to camelCase
2152. Update all SQL queries to use camelCase parameters
2163. Update entity interfaces to match database exactly
2174. Remove any mapping/transformation code
2185. Update documentation and examples
219
220## Enforcement
221
222### Required Checks:
223- [ ] Entity interfaces use camelCase properties
224- [ ] Database columns use camelCase naming
225- [ ] SQL parameters use camelCase matching columns exactly
226- [ ] Table names use PascalCase
227- [ ] No mapping/transformation code exists between database and entities
228
229This approach prioritizes practical development concerns over naming convention traditions, resulting in cleaner, more maintainable, and less error-prone code.