Developer MessagesClaude MessagesShow Tool Use

jeff

I'm interested to see how well we did with the database schema and if it is easy for you to generate the core TypeScript types for the entities that the CWC code will be working with - I call these types EntityTypes. I will provide limited guidance here for starters, we can iterate on it if needed. To prevent any potential naming conflicts or ambiguity in the code, let's prefix each entity type with Cwc. Let's put these types in packages/cwc-types/entityTypes.ts. Based on past experience, and some reading, I have opted to avoid using typescript enums - I would like to hear your thoughts on that, but it will take compelling arguments to get my approval to use that approach. The typescript documentation says to favor interfaces over types in most cases but I've had pretty good results declaring entity types like this as types, what are your thoughts on that? Do you need any more information from me in order to formulate a plan?

Claude

I'll help you analyze the database schema and plan the TypeScript entity type generation. Let me first explore the database schema to understand what we're working with, then I'll address your questions about enums vs. union types and interfaces vs. types.

Tool Result

## Comprehensive Database Schema Analysis

I've analyzed the complete database schema definition. Here's a thorough breakdown:

---

## Tables Overview

The schema contains **18 tables** in total, all following consistent naming and structural conventions.

---

## Detailed Table Breakdown

### 1. **abuseReport**
- **Purpose:** Tracks user reports of abusive behavior or accounts
- **Columns:**
  - `abuseReportPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable) - reporter's user ID
  - `projectPkId` (INT, nullable)
  - `usernames` (VARCHAR(1000), NOT NULL) - usernames being reported
  - `message` (VARCHAR(500), NOT NULL)
  - `status` (VARCHAR(25), nullable) - **ENUM VALUES:** `[submitted, investigation, dismissed, resolved, retracted]`
- **Indexes:** userPkId, projectPkId, status

### 2. **adminUser**
- **Purpose:** Administrative users for the dashboard
- **Columns:**
  - `adminUserPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `adminUsername` (VARCHAR(25), nullable)
  - `password` (VARCHAR(100), nullable)
- **Indexes:** adminUsername (unique)

### 3. **adminUserJwt**
- **Purpose:** JWT validation/revocation for admin users
- **Columns:**
  - `adminUserJwtPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `adminUserJwtId` (VARCHAR(36), nullable) - UUID for JWT lookup
- **Indexes:** adminUserJwtId (unique)

### 4. **codingSession**
- **Purpose:** Individual coding sessions within a project
- **Columns:**
  - `codingSessionPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `projectPkId` (INT, nullable)
  - `description` (TEXT, nullable)
  - `published` (TINYINT, nullable)
- **Indexes:** userPkId, projectPkId, published

### 5. **codingSessionAttachment**
- **Purpose:** Images and files attached to coding sessions
- **Columns:**
  - `codingSessionAttachmentPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `projectPkId` (INT, nullable)
  - `codingSessionPkId` (INT, nullable)
  - `filename` (VARCHAR(50), nullable)
  - `mimeType` (VARCHAR(100), nullable) - **ENUM VALUES:** `[image/png, image/jpg]`
  - `height` (INT, nullable)
  - `width` (INT, nullable)
- **Indexes:** userPkId, projectPkId, codingSessionPkId

### 6. **codingSessionContent**
- **Purpose:** Content blocks (prompts, responses, notes, attachments) within a session
- **Columns:**
  - `codingSessionContentPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `projectPkId` (INT, nullable)
  - `contentType` (VARCHAR(25), nullable) - **ENUM VALUES:** `[prompt, response, note, attachment]`
  - `codingSessionPkId` (INT, nullable)
  - `codingSessionAttachmentPkId` (INT, nullable)
  - `displayIndex` (INT, nullable) - controls rendering order
  - `text` (TEXT, nullable)
- **Indexes:** userPkId, projectPkId, codingSessionPkId, codingSessionAttachmentPkId

### 7. **comment**
- **Purpose:** User comments on various entities (polymorphic relationship)
- **Columns:**
  - `commentPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `projectPkId` (INT, nullable)
  - `userPkId` (INT, nullable)
  - `entityPkId` (INT, NOT NULL) - ID of entity being commented on
  - `entityType` (VARCHAR(50), NOT NULL) - **ENUM VALUES:** `[project, codingSession, codingSessionContent, codingSessionAttachment, comment]`
  - `text` (TEXT, nullable)
- **Indexes:** projectPkId, userPkId, entityPkId, entityType

### 8. **contentReport**
- **Purpose:** User reports of inappropriate content (polymorphic relationship)
- **Columns:**
  - `contentReportPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `projectPkId` (INT, nullable)
  - `entityPkId` (INT, NOT NULL) - ID of entity being reported
  - `entityType` (VARCHAR(50), NOT NULL) - **ENUM VALUES:** `[project, codingSession, codingSessionContent, codingSessionAttachment, comment]`
  - `message` (VARCHAR(500), NOT NULL)
  - `status` (VARCHAR(25), nullable) - **ENUM VALUES:** `[submitted, investigation, dismissed, resolved, retracted]`
- **Indexes:** userPkId, projectPkId, entityPkId, status

### 9. **emailVerify**
- **Purpose:** Email verification tracking (standalone, not tied to signup/reset)
- **Columns:**
  - `emailVerifyPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `confirmationCode` (VARCHAR(6), nullable) - verification code
  - `email` (VARCHAR(45), nullable)
  - `deviceId` (VARCHAR(36), nullable)
  - `confirmed` (TINYINT, nullable)
  - `emailVerificationId` (VARCHAR(36), nullable) - UUID
- **Indexes:** emailVerificationId (unique), email, deviceId

### 10. **errorLog**
- **Purpose:** Centralized error logging for all microservices
- **Columns:**
  - `errorLogPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `projectPkId` (INT, nullable)
  - `serviceName` (VARCHAR(75), NOT NULL) - **ENUM VALUES:** `[cwc-auth, cwc-api, cwc-sql, cwc-website]`
  - `codeLocation` (VARCHAR(200), NOT NULL)
  - `dismissed` (TINYINT, NOT NULL)
  - `errorType` (VARCHAR(25), nullable) - **ENUM VALUES:** `[error, info, warn, debug]`
  - `errorMessage` (VARCHAR(200), NOT NULL)
  - `error` (TEXT, nullable)
  - `stack` (TEXT, nullable)
  - `data` (TEXT, nullable)
  - `request` (TEXT, nullable)
- **Indexes:** userPkId, projectPkId, serviceName, dismissed, errorType

### 11. **featureFlag**
- **Purpose:** Runtime feature toggles and configuration
- **Columns:**
  - `featureFlagPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `featureFlagType` (VARCHAR(25), NOT NULL) - **ENUM VALUES:** `[feature, system]`
  - `featureFlagId` (VARCHAR(50), NOT NULL)
  - `valueType` (VARCHAR(25), NOT NULL) - **ENUM VALUES:** `[boolean, number, string, object]`
  - `value` (VARCHAR(200), NOT NULL)
  - `description` (VARCHAR(200), nullable)
  - `platform` (VARCHAR(25), nullable) - **ENUM VALUES:** `[all, web, android, ios, mobile]`
  - `serviceName` (VARCHAR(75), NOT NULL) - **ENUM VALUES:** `[all, frontend, backend, cwc-app, cwc-sql, cwc-auth, cwc-api, cwc-admin-api, cwc-dashboard]`
- **Indexes:** featureFlagId (unique), serviceName, platform

### 12. **passwordResetInfo**
- **Purpose:** Tracks password reset process state
- **Columns:**
  - `passwordResetInfoPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userPkId` (INT, nullable)
  - `password` (VARCHAR(100), nullable)
  - `email` (VARCHAR(45), nullable)
  - `emailVerificationId` (VARCHAR(36), nullable)
  - `emailConfirmed` (TINYINT, nullable)
  - `deviceId` (VARCHAR(36), nullable)
  - `passwordResetAttempts` (INT, nullable)
- **Indexes:** userPkId, emailVerificationId (unique), email, deviceId

### 13. **project**
- **Purpose:** User projects that contain coding sessions
- **Columns:**
  - `projectPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `projectId` (VARCHAR(150), nullable) - unique identifier
  - `projectType` (VARCHAR(25), nullable) - **ENUM VALUES:** `[web, mobile, backend, ai]`
  - `userPkId` (INT, nullable)
- **Indexes:** userPkId, projectId (unique)

### 14. **reaction**
- **Purpose:** User reactions (emoji-style) to various entities
- **Columns:**
  - `reactionPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `projectPkId` (INT, nullable)
  - `userPkId` (INT, nullable)
  - `entityPkId` (INT, NOT NULL)
  - `entityType` (VARCHAR(50), NOT NULL) - **ENUM VALUES:** `[project, codingSession, codingSessionContent, codingSessionAttachment, comment]`
  - `reactionName` (VARCHAR(25), nullable) - **ENUM VALUES:** `[love, funny, sad, wow, thumbs-up]`
- **Indexes:** projectPkId, userPkId, entityPkId, entityType

### 15. **signupInfo**
- **Purpose:** Tracks signup process state
- **Columns:**
  - `signupInfoPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `loginType` (VARCHAR(25), nullable) - **ENUM VALUES:** `[cwc, facebook, google]`
  - `userPkId` (INT, nullable)
  - `password` (VARCHAR(100), nullable)
  - `email` (VARCHAR(45), nullable)
  - `emailVerificationId` (VARCHAR(36), nullable)
  - `emailConfirmed` (TINYINT, nullable)
  - `deviceId` (VARCHAR(36), nullable)
  - `signupAttempts` (INT, nullable)
- **Indexes:** userPkId, emailVerificationId (unique), email, deviceId

### 16. **tempJwt**
- **Purpose:** JWT validation/revocation for temporary multi-step processes
- **Columns:**
  - `tempJwtPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `tempJwtId` (VARCHAR(36), nullable) - UUID for JWT lookup
- **Indexes:** tempJwtId (unique)

### 17. **user**
- **Purpose:** Primary user accounts
- **Columns:**
  - `userPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `username` (VARCHAR(100), nullable)
  - `loginType` (VARCHAR(25), nullable) - **ENUM VALUES:** `[cwc, facebook, google]`
  - `loginDate` (DATETIME, nullable)
  - `password` (VARCHAR(100), nullable)
  - `email` (VARCHAR(45), nullable)
  - `emailConfirmed` (TINYINT, nullable)
  - `deviceId` (VARCHAR(36), nullable)
  - `otpSecret` (VARCHAR(30), nullable)
  - `otpValidated` (TINYINT, nullable)
- **Indexes:** username (unique), email (unique), deviceId, loginType

### 18. **userJwt**
- **Purpose:** JWT validation/revocation for regular users
- **Columns:**
  - `userJwtPkId` (INT, PK, AUTO_INCREMENT)
  - `enabled` (TINYINT, nullable)
  - `createdDate` (DATETIME, nullable)
  - `modifiedDate` (DATETIME, nullable)
  - `userJwtId` (VARCHAR(36), nullable) - UUID for JWT lookup
- **Indexes:** userJwtId (unique)

---

## VARCHAR Columns Requiring TypeScript Union Types

These columns have inline comments specifying potential enum-like values that should be converted to TypeScript union types:

1. **abuseReport.status** → `'submitted' | 'investigation' | 'dismissed' | 'resolved' | 'retracted'`
2. **codingSessionAttachment.mimeType** → `'image/png' | 'image/jpg'`
3. **codingSessionContent.contentType** → `'prompt' | 'response' | 'note' | 'attachment'`
4. **comment.entityType** → `'project' | 'codingSession' | 'codingSessionContent' | 'codingSessionAttachment' | 'comment'`
5. **contentReport.entityType** → `'project' | 'codingSession' | 'codingSessionContent' | 'codingSessionAttachment' | 'comment'`
6. **contentReport.status** → `'submitted' | 'investigation' | 'dismissed' | 'resolved' | 'retracted'`
7. **errorLog.serviceName** → `'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website'`
8. **errorLog.errorType** → `'error' | 'info' | 'warn' | 'debug'`
9. **featureFlag.featureFlagType** → `'feature' | 'system'`
10. **featureFlag.valueType** → `'boolean' | 'number' | 'string' | 'object'`
11. **featureFlag.platform** → `'all' | 'web' | 'android' | 'ios' | 'mobile'`
12. **featureFlag.serviceName** → `'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql' | 'cwc-auth' | 'cwc-api' | 'cwc-admin-api' | 'cwc-dashboard'`
13. **project.projectType** → `'web' | 'mobile' | 'backend' | 'ai'`
14. **reaction.entityType** → `'project' | 'codingSession' | 'codingSessionContent' | 'codingSessionAttachment' | 'comment'`
15. **reaction.reactionName** → `'love' | 'funny' | 'sad' | 'wow' | 'thumbs-up'`
16. **signupInfo.loginType** → `'cwc' | 'facebook' | 'google'`
17. **user.loginType** → `'cwc' | 'facebook' | 'google'`

---

## Key Patterns and Conventions Observed

### Structural Patterns:
1. **Standard columns:** Every table has `{tableName}PkId`, `enabled`, `createdDate`, `modifiedDate`
2. **Primary keys:** Always `{tableName}PkId` as INT AUTO_INCREMENT
3. **camelCase naming:** All tables and columns use camelCase
4. **No FK constraints:** Relationships indicated by column names like `userPkId`, `projectPkId` but not enforced at DB level

### Data Type Patterns:
1. **TINYINT for booleans:** `enabled`, `published`, `confirmed`, `dismissed`, etc.
2. **VARCHAR(36) for UUIDs:** All ID fields ending in `Id` (not `PkId`)
3. **TEXT for large text:** `description`, `text`, `error`, `stack`, `data`, `request`
4. **DATETIME for timestamps:** `createdDate`, `modifiedDate`, `loginDate`
5. **INT for counters:** `passwordResetAttempts`, `signupAttempts`, `height`, `width`, `displayIndex`

### Relationship Patterns:
1. **Foreign key references:** Column names like `userPkId`, `projectPkId` indicate relationships
2. **Polymorphic relationships:** `entityPkId` + `entityType` pattern used in `comment`, `contentReport`, `reaction`
3. **JWT validation tables:** Minimal structure with just the JWT ID (no user FK)

### View Structure:
- No views currently defined (placeholder file exists)
- Example pattern provided shows JOIN syntax and naming conventions

---

## TypeScript Type Generation Considerations

### Required Type Mappings:
- **INT** → `number`
- **TINYINT** → `number` (or `boolean` with conversion layer)
- **VARCHAR** → `string`
- **TEXT** → `string`
- **DATETIME** → `Date` or `string` (ISO 8601)
- **VARCHAR with potential-values** → TypeScript union type (string literal types)

### Naming Conventions:
- Entity interface names should match table names in PascalCase: `AbuseReport`, `CodingSession`, etc.
- Enum-like types should be named: `{TableName}{ColumnName}` in PascalCase: `AbuseReportStatus`, `ContentType`, etc.

### Nullability:
- All columns marked `NULL` should be optional in TypeScript (`field?: type`)
- Columns marked `NOT NULL` should be required (`field: type`)
- Standard columns (`enabled`, `createdDate`, `modifiedDate`) are nullable in all tables

This schema is well-structured and follows consistent conventions, making it ideal for automated TypeScript type generation.

Excellent! I've analyzed your database schema and found 18 tables with well-defined structures. Before I present the full plan, let me address your questions and gather a few clarifications.

My Thoughts on Your Questions

Enums vs Union Types

I strongly agree with avoiding TypeScript enums. Here's why:

Enums generate runtime code - They're one of the few TypeScript features that aren't just type annotations, adding unnecessary bundle size
String enums can't be used in template literals - Union types work seamlessly with TypeScript's string manipulation types
Enums create a separate namespace - This can lead to import confusion and naming conflicts
Union types are more flexible - They work better with type narrowing, mapped types, and utility types
Const assertions do the same job - You can get enum-like behavior with as const if needed

Recommendation: Use string literal union types (e.g., type Status = 'submitted' | 'investigation' | 'dismissed') for all your enum-like VARCHAR fields.

Interfaces vs Types

For entity types specifically, I recommend using type over interface. Here's why:

Entity types are data shapes, not contracts - Interfaces are better for defining contracts that will be implemented/extended
Types compose better - You'll likely need utility types like Omit<CwcUser, 'userPkId'> for inserts
Types are more concise for unions - If you ever need discriminated unions, types work better
Consistency with union types - Your enum-like types will be type, so keeping entities as type maintains consistency

The TypeScript docs favor interfaces for object-oriented patterns (class implementations, extensibility), but for pure data structures from a database, types are perfectly appropriate.

Clarifying Questions

Claude is asking...

DATETIME typeHow should DATETIME columns be represented in TypeScript?

string (ISO 8601)- Dates as strings like '2024-01-15T10:30:00Z'. Simpler for JSON serialization, what you get from database drivers.

Date object- Native JavaScript Date objects. More type-safe for date operations, but requires conversion from database strings.

TINYINT typeHow should TINYINT boolean-like fields (enabled, published, confirmed, etc.) be represented?

number- Keep as number (0/1) to match database exactly. Requires truthy/falsy checks in code.

boolean- Convert to proper boolean. More idiomatic TypeScript, but requires conversion layer between DB and types.

number with alias- Use type alias like 'type TinyInt = number' for clarity while keeping number type.

Null handlingHow should nullable fields be represented?

| null only- field: string | null - Explicit null, field always present. Matches DB columns marked NULL exactly.

Optional (?) only- field?: string - Field may be undefined. More concise, common in TypeScript.

Both | null and ?- field?: string | null - Most explicit, handles both undefined and null, but verbose.

Utility typesShould I generate additional utility types for database operations (inserts, updates, etc.)?

Insert types- Types that omit auto-increment PKs and timestamps for INSERT operations (e.g., CwcUserInsert)

Update types- Types with all fields optional except PK for UPDATE operations (e.g., CwcUserUpdate)

Just base types- Only generate the core entity types. You can create utility types later using Omit/Partial as needed.

Developer Response

Q: How should DATETIME columns be represented in TypeScript?

A: "string (ISO 8601)"

Q: How should TINYINT boolean-like fields (enabled, published, confirmed, etc.) be represented?

A: "boolean"

Q: How should nullable fields be represented?

A: "Great question! This may seem odd but even though we declared most columns allowing NULL, I want to be more strict with the types and declare them as required strings. There are a few fields that truly are optional like codingSessionContent.codingSessionAttachmentPkId and I would be interested to see if you can identify those. If in doubt, make it required. There will be cases when we won't have all of the values needed to fully satisfy these type requirements, but I have generally used typescript's Partial utility type to handle that - can you look at the pros and cons of that approach so we know the impact of these decisions?"

Q: Should I generate additional utility types for database operations (inserts, updates, etc.)?

A: "Just base types"

Perfect! Thank you for those clarifications. Let me analyze the optional fields question and present a comprehensive plan.

Analysis: Partial Approach - Pros & Cons

Pros of using strict base types + Partial:

Type accuracy - Base entity types accurately represent complete database rows
Self-documenting - Code using Partial<CwcUser> clearly signals "this is incomplete data"
Type safety where it matters - When you have a full entity, all fields are guaranteed present (no optional chaining)
Flexibility - Can combine with other utility types: Omit<CwcUser, 'userPkId'>, Pick<CwcUser, 'email' | 'username'>
No duplication - Single source of truth for each entity structure

Cons:

Partial is too permissive - Makes ALL fields optional, even ones that should be required in your use case
Loss of granular control - Can't express "PK is optional but email is required" for INSERT operations
Runtime safety gap - TypeScript won't catch if you forget to provide a critical field
Verbose narrowing - Need runtime checks or assertions to narrow back to required fields

Trade-off Assessment:

Your approach is solid for this use case because:

Your microservices will likely validate data before DB operations anyway
The strict base types provide safety when working with fetched entities
Partial<T> is simple and doesn't require maintaining many type variants
You can add specific operation types later if pain points emerge (e.g., for complex forms)

Recommendation: Start with this approach. If you find yourself needing more granular control (like "INSERT requires email but not userPkId"), you can introduce specific types later without changing the base entity types.

Truly Optional Fields (Business Logic Analysis)

Based on the schema and business logic patterns, here are fields that should be optional (all others required):

Content Block Pattern:

codingSessionContent.codingSessionAttachmentPkId - Only present when contentType = 'attachment'
codingSessionContent.text - Only present for text-based content (or as optional caption for attachments)

Error Logging (No User/Project Context):

errorLog.userPkId - Errors can occur without user context (startup errors, cron jobs, etc.)
errorLog.projectPkId - Errors can occur outside project context

Multi-Step Process States:

passwordResetInfo.password - Not set until user provides new password in final step
signupInfo.userPkId - Not set until signup process completes
signupInfo.password - Not set until user provides password (or null for OAuth signups)

Conditional Features:

user.otpSecret - Only present if user has enabled 2FA
user.password - Null for OAuth users (loginType = 'facebook' | 'google')

Total: 9 optional fields out of ~150+ fields across 18 entities

Exited plan mode

Work interrupted

jeff

[Request interrupted by user for tool use]

That is really helpful analysis and advice! Your analysis of the optional fields has identified some omissions and led me to question a few of the choices that I made when creating the database schema yesterday. I think that we need to add username to signupInfo and passwordResetInfo, as these are pieces of information that are collected along the way. Originally, in tables that wanted to reference other tables like project or user, I included projectId rather than projectPkId and username rather than userPkId. I may have gone too far with this change, and would like to hear your thoughts on that. A basic relational database design rule is to only use system generated values (that are not going to change) for foreign keys and never use user generated values (that the user may want to change). There is a cost to this in our code because any time we want projectId or username will need to look it up based on the PkId values. I my opinion, the real question is if we will ever want to allow a user to change their username or the projectId for their project. The actual cost of looking up the values may not really be a problem since my plan for loading data in the backend services almost always involves loading and caching (for the current request) the user object and all of the user's projects during the authentication and access policy enforcement steps - so we will have all of the data preloaded. On the frontend, data will be cached as well, so we should not incur extra api calls to get the data and in our frontend cached collections of entities we could choose to index on projectId and username instead of the PkId values. Can you chime in on some of these decisions, assumptions, and approaches?

Claude

Excellent questions! This is exactly the kind of critical analysis that should happen during planning. Let me break down my thoughts on each point:

Adding `username` to signupInfo and passwordResetInfo

Strong agree - this is a good catch. Here's why:

signupInfo:

User provides username during signup flow
You'll need it to validate uniqueness before creating the user record
Storing it avoids having to JOIN to user table (which might not exist yet anyway)
Useful for audit logs ("signup attempt with username 'foo'")

passwordResetInfo:

Good for UX: "Resetting password for @username"
Useful for logging/audit trail
Avoids JOIN during the reset process
Question: Do users identify themselves by username or email for password reset? If email-only, username is nice-to-have but not critical.

Recommendation: Add username VARCHAR(100) NULL to both tables.

PkId vs Natural Keys for Foreign Key References

Your current approach (using PkId) is absolutely correct. Here's my analysis:

Why PkId is the Right Choice

Future-Proofing
- Even if you don't plan to allow username/projectId changes NOW, requirements evolve
- I've seen countless projects regret using natural keys when business needs change
- Example scenario: User wants to rebrand their project → changing projectId across 50+ records vs 1 record
Data Integrity
- No risk of cascading updates introducing inconsistencies
- Single source of truth for username/projectId (in user/project tables only)
- If data gets corrupted, easier to trace and fix
Your Caching Strategy Makes the Performance Cost Zero
- Backend: You're loading user + projects during auth anyway → data already in memory
- Frontend: Cached collections indexed however you want → can use Map with projectId as key
- No extra queries needed because the data you need is already loaded
- This is the key insight: you've already solved the "lookup cost" problem
Standard Practice
- Any experienced database developer joining your team expects this pattern
- ORMs and database tools are optimized for surrogate key relationships
- Easier to reason about data model

Your Current Design is Actually Perfect

Looking at your schema:

✅ user has both userPkId (surrogate) AND username (natural key with unique index)
✅ project has both projectPkId (surrogate) AND projectId (natural key with unique index)
✅ All FKs use PkId references: userPkId, projectPkId, codingSessionPkId

This is textbook best practice: separate user-facing identifiers from internal relationships.

When Natural Keys ARE Appropriate

Your schema correctly uses natural keys in these cases:

abuseReport.usernames - Point-in-time snapshot of reported usernames (not a FK)
Polymorphic relationships - entityPkId + entityType (correct pattern)
Historical/audit data - Snapshot of what values were at that moment

Addressing Your Specific Concerns

"Will we ever allow username/projectId changes?"

My experience: Even when teams say "users will never change X," they eventually do. Common scenarios:

Username changes: Typos, rebranding, harassment mitigation ("let me change my username to escape a harasser")
ProjectId changes: Rebranding, fixing mistakes, SEO optimization
Business acquisition: Merging users, consolidating projects

Cost of being wrong:

If you use PkId and never allow changes: Zero cost (your caching handles lookups)
If you use natural keys and later need changes: Massive refactoring, potential data loss, downtime

Risk/reward: PkId is the safe bet.

"Cost of looking up values"

You've correctly identified this is not actually a cost in your architecture:

Backend:

// During auth middleware (happens once per request)
const user = await loadUser(userPkId); // cache for request
const projects = await loadUserProjects(userPkId); // cache for request

// Later in request handler - no additional queries
const project = projectsById.get(projectPkId); // O(1) lookup
const username = user.username; // already loaded

Frontend:

// Cached in Redux/Zustand/etc
const projectsMap = new Map(projects.map(p => [p.projectId, p]));
const project = projectsMap.get(projectId); // O(1) lookup by natural key

Actual performance impact: None. You're loading this data regardless.

"Index cached collections by projectId/username"

Absolutely! This is smart:

class RequestCache {
  private usersByPkId = new Map<number, User>();
  private usersByUsername = new Map<string, User>();
  private projectsByPkId = new Map<number, Project>();
  private projectsByProjectId = new Map<string, Project>();

  addUser(user: User) {
    this.usersByPkId.set(user.userPkId, user);
    this.usersByUsername.set(user.username, user);
  }

  getUserByUsername(username: string): User | undefined {
    return this.usersByUsername.get(username); // O(1), no query
  }
}

You get the best of both worlds: database uses immutable PkIds, application uses convenient natural keys.

Final Recommendations

1. Keep using PkId for all foreign key references ✅

Your current approach is correct and future-proof.

2. Add username to signupInfo and passwordResetInfo ✅

Makes sense for these stateful process tables.

3. Schema Changes Needed:

-- signupInfo table
ALTER TABLE `cwc`.`signupInfo` 
  ADD COLUMN `username` VARCHAR(100) NULL AFTER `userPkId`;

-- passwordResetInfo table  
ALTER TABLE `cwc`.`passwordResetInfo` 
  ADD COLUMN `username` VARCHAR(100) NULL AFTER `userPkId`;

4. Consider Adding Index:

CREATE INDEX `ix_signupInfo_username` ON `cwc`.`signupInfo` (`username` ASC);
CREATE INDEX `ix_passwordResetInfo_username` ON `cwc`.`passwordResetInfo` (`username` ASC);

5. Update Entity Types Plan:

When we generate types, signupInfo.username and passwordResetInfo.username should be optional (nullable) since they might not be collected yet in early steps.

Bottom Line

Your instinct to question the design is excellent, but your original decision to use PkId was the right one. The "cost" you're worried about doesn't exist because of your caching architecture. You've essentially built a smart caching layer that gives you the performance of natural keys with the safety of surrogate keys.

The only real change needed is adding username to those two tables - good catch!

Should we proceed with the entity types generation with these schema changes in mind, or do you want to update the schema first?

jeff

Answers to your questions:
Do users identify themselves by username or email for password reset? If email-only, username is nice-to-have but not critical. The first step of the password reset will probably be verifying the email address so we can block the hackers early on. Your are correct, username is a nice-to-have, so let's go ahead and add it to the table.
When we generate types, signupInfo.username and passwordResetInfo.username should be optional (nullable) since they might not be collected yet in early steps. You may be right about this, but let's stick with the plan to require the fields. When we are working with these types in the signup or passwordReset code, we will use Partial<>.
Let's update the database schema first.
I may not have to tell you these next few instructions, but just to be sure:

remember to update the table and index definitions and the drop table scripts, not just the migration scripts.
without going overboard, let's update claude.md with the key architectural decisions and reasoning that we have covered so far in this planning session.

Claude

Perfect! I'll create a comprehensive plan to update the database schema and document our architectural decisions. Let me present the complete plan:

Exited plan mode

Plan approved

Excellent! Let me start by creating a todo list to track this work, then execute the plan.

Now I'll update the create-tables.sql file to add the username column to both tables.

Perfect! Now let me update the todo and move to the indexes file.

Now I'll add the indexes for the username columns in both tables.

Excellent! Now let me update the todo and create the migration script.

Perfect! Now let me update the todo and read CLAUDE.md to add the architectural decisions.

Now I'll add the architectural decisions to CLAUDE.md. Let me insert a new section after the Database Design Patterns.

Perfect! Now let me update the todo and verify all the changes.

Excellent! All changes verified. Let me complete the todo list.

Perfect! All database schema updates are complete. Here's a summary of what was accomplished:

✅ Database Schema Updates Complete

Files Modified:

create-tables.sql - Added username VARCHAR(100) NULL column to:
- passwordResetInfo table (after userPkId)
- signupInfo table (after userPkId)
create-indexes.sql - Added indexes for:
- ix_passwordResetInfo_username
- ix_signupInfo_username
schema-changes/2025-01-18-add-username-to-signup-password-reset.sql (new)
- Migration script with ALTER TABLE and CREATE INDEX statements
- Includes rationale documentation
CLAUDE.md - Added comprehensive "Key Architectural Decisions" section documenting:
- Foreign Key Strategy: PkId vs Natural Keys (why we use surrogate keys)
- Request-Scoped Caching Architecture: How caching eliminates lookup overhead
- Multi-Step Process Tables: Why we store denormalized data during signup/reset
- TypeScript Entity Types: Strict base types + Partial strategy with pros/cons
- TypeScript: Enums vs Union Types: Why we use union types
- TypeScript: Interfaces vs Types: Why we use types for entities

Verification Complete:

✅ Both tables have username columns in correct position
✅ Both indexes created with proper naming convention
✅ Migration script follows established patterns
✅ All architectural decisions documented for future reference

Next Step: Generate TypeScript Entity Types

Now we're ready to generate the entity types in packages/cwc-types/src/entityTypes.ts. Based on our discussion, the types will:

Use type (not interface)
Use string literal unions (not enums) for enum-like VARCHAR fields
Prefix all entities with Cwc
Map DATETIME → string (ISO 8601)
Map TINYINT → boolean
Make all fields required except the 9 business-critical optionals we identified
Include the updated signupInfo.username and passwordResetInfo.username fields

Ready to proceed with entity type generation?

jeff

I have committed the changes so we are working with a clean slate on this branch. Yes, let's generate the entity types!

Claude

Excellent! Let me create a todo list and start generating the TypeScript entity types.

The package doesn't exist yet. Let me check the existing packages structure to follow the same pattern.

Perfect! Now I'll create the cwc-types package structure and generate the entity types. Let me start:

Now let me generate the entity types file:

Excellent! Now let me create the index file and verify the types compile:

Perfect! Types compiled successfully. Let me complete the todo list and verify the package structure:

✅ TypeScript Entity Types Generation Complete!

Package Created: `cwc-types`

Structure:

packages/cwc-types/
├── package.json          # Package configuration with proper exports
├── tsconfig.json         # TypeScript config extending base
├── tsconfig.tsbuildinfo  # TypeScript build cache
└── src/
    ├── index.ts          # Main export file
    └── entityTypes.ts    # 391 lines of entity type definitions

Generated Types Summary

13 Union Types (Enum-like VARCHAR fields):

CwcAbuseReportStatus
CwcContentReportStatus
CwcContentType
CwcEntityType
CwcErrorType
CwcFeatureFlagType
CwcLoginType
CwcMimeType
CwcPlatform
CwcProjectType
CwcReactionName
CwcServiceName
CwcValueType

18 Entity Types (All database tables):

CwcAbuseReport
CwcAdminUser
CwcAdminUserJwt
CwcCodingSession
CwcCodingSessionAttachment
CwcCodingSessionContent
CwcComment
CwcContentReport
CwcEmailVerify
CwcErrorLog
CwcFeatureFlag
CwcPasswordResetInfo ✨ (includes new username field)
CwcProject
CwcReaction
CwcSignupInfo ✨ (includes new username field)
CwcTempJwt
CwcUser
CwcUserJwt

Type Generation Decisions Applied

✅ All fields required (except 9 business-critical optionals):

codingSessionContent.codingSessionAttachmentPkId?
codingSessionContent.text?
errorLog.userPkId?
errorLog.projectPkId?
passwordResetInfo.password?
signupInfo.userPkId?
signupInfo.password?
user.otpSecret?
user.password?

✅ Type mappings:

DATETIME → string (ISO 8601)
TINYINT → boolean
VARCHAR → string
TEXT → string
INT → number

✅ Naming conventions:

All entities prefixed with Cwc
Union types use descriptive names
Used type (not interface)
Used string literal unions (not enums)

✅ Documentation:

Comprehensive JSDoc comments for each entity
File header explaining usage with Partial<T>
Examples of utility type composition
Notes about polymorphic patterns and special cases

Verification

✅ TypeScript compilation: pnpm typecheck passed with no errors
✅ All 18 tables represented with accurate field mappings
✅ All enum-like fields converted to union types
✅ Schema updates reflected (signupInfo.username, passwordResetInfo.username)

Usage Example

import { CwcUser, CwcProject, CwcLoginType } from 'cwc-types';

// Complete entity (all fields required except optionals)
const user: CwcUser = {
  userPkId: 1,
  enabled: true,
  createdDate: '2025-01-18T10:00:00Z',
  modifiedDate: '2025-01-18T10:00:00Z',
  username: 'john_doe',
  loginType: 'cwc',
  loginDate: '2025-01-18T10:00:00Z',
  password: 'hashed_password', // Optional, can be omitted for OAuth users
  email: 'john@example.com',
  emailConfirmed: true,
  deviceId: 'device-uuid',
  // otpSecret omitted (optional)
  otpValidated: false,
};

// Partial data for INSERT (omit auto-generated fields)
type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;

// Partial data for UPDATE
const updates: Partial<CwcUser> = {
  email: 'newemail@example.com',
  emailConfirmed: false,
};

The types are now ready to be imported and used across all CWC packages! 🎉

jeff

Thank you, you have done a great job on this! I do have a things that I would like to change though. 1. let's fully qualify the names of our union types to avoid ambiguity. An example is CwcContentType - I would like that to be named CwcCodingSessionContentType since it is likely that we will have other contentType values in other contexts as we continue to develop. The same applies to CwcEntityType but depending on the context, CwcComment or CwcContentReport and so on, we may want to make the available values different - this may have not been clear by looking at the comments in the create table script. These are a couple of specific examples, but please reexamine all of your work on the entity types with these considerations in mind.

Claude

Excellent point! You're absolutely right - these union types should be fully qualified to their specific context. Let me analyze all the union types and update them accordingly.

Let me analyze the schema to see which union types need to be renamed:

Analysis:

✅ CwcAbuseReportStatus - Already specific (only used in abuseReport)
✅ CwcContentReportStatus - Already specific (only used in contentReport)
❌ CwcContentType → Should be CwcCodingSessionContentType (only in codingSessionContent)
❌ CwcEntityType → Should split into:
- CwcCommentEntityType
- CwcContentReportEntityType
- CwcReactionEntityType
  (Currently same values, but may diverge as you noted)
❌ CwcErrorType → Should be CwcErrorLogType (only in errorLog)
✅ CwcFeatureFlagType - Already specific
✅ CwcLoginType - Shared by user and signupInfo with same values, OK to keep
❌ CwcMimeType → Should be CwcCodingSessionAttachmentMimeType (only in codingSessionAttachment)
❌ CwcPlatform → Should be CwcFeatureFlagPlatform (only in featureFlag)
✅ CwcProjectType - Already specific
✅ CwcReactionName - Already specific
❌ CwcServiceName → Should split into:
- CwcErrorLogServiceName (values: cwc-auth, cwc-api, cwc-sql, cwc-website)
- CwcFeatureFlagServiceName (values: all, frontend, backend, cwc-app, cwc-sql, cwc-auth, cwc-api, cwc-admin-api, cwc-dashboard)
  These actually have DIFFERENT values!
❌ CwcValueType → Should be CwcFeatureFlagValueType (only in featureFlag)

Let me update the file:

Now let me rewrite the entire entityTypes.ts file with properly qualified union type names:

Perfect! Now let me verify the types compile with the new names:

Excellent! Let me complete the todo and provide a summary:

✅ Union Types Fully Qualified with Context

Perfect! All union types have been renamed to be fully qualified with their entity context. Here's a summary of the changes:

Union Type Renamings

Renamed for Clarity:

❌ CwcContentType → ✅ CwcCodingSessionContentType
- Only used in codingSessionContent
❌ CwcEntityType → ✅ Split into 3 types:
- CwcCommentEntityType (for comment.entityType)
- CwcContentReportEntityType (for contentReport.entityType)
- CwcReactionEntityType (for reaction.entityType)
- Rationale: Even though they currently have the same values, each context may diverge in the future
❌ CwcErrorType → ✅ CwcErrorLogType
- Only used in errorLog
❌ CwcMimeType → ✅ CwcCodingSessionAttachmentMimeType
- Only used in codingSessionAttachment
❌ CwcPlatform → ✅ CwcFeatureFlagPlatform
- Only used in featureFlag
❌ CwcServiceName → ✅ Split into 2 types:
- CwcErrorLogServiceName (values: cwc-auth, cwc-api, cwc-sql, cwc-website)
- CwcFeatureFlagServiceName (values: all, frontend, backend, cwc-app, cwc-sql, cwc-auth, cwc-api, cwc-admin-api, cwc-dashboard)
- Important: These had DIFFERENT values in the schema, so splitting was necessary!
❌ CwcValueType → ✅ CwcFeatureFlagValueType
- Only used in featureFlag

Kept As-Is (Already Specific):

✅ CwcAbuseReportStatus
✅ CwcContentReportStatus
✅ CwcFeatureFlagType
✅ CwcLoginType (shared by user and signupInfo with identical values)
✅ CwcProjectType
✅ CwcReactionName

Final Count

16 Union Types (was 13, now 16 due to splits):

CwcAbuseReportStatus
CwcCommentEntityType ⭐ (split from CwcEntityType)
CwcCodingSessionAttachmentMimeType ⭐ (renamed from CwcMimeType)
CwcCodingSessionContentType ⭐ (renamed from CwcContentType)
CwcContentReportEntityType ⭐ (split from CwcEntityType)
CwcContentReportStatus
CwcErrorLogServiceName ⭐ (split from CwcServiceName)
CwcErrorLogType ⭐ (renamed from CwcErrorType)
CwcFeatureFlagPlatform ⭐ (renamed from CwcPlatform)
CwcFeatureFlagServiceName ⭐ (split from CwcServiceName)
CwcFeatureFlagType
CwcFeatureFlagValueType ⭐ (renamed from CwcValueType)
CwcLoginType
CwcProjectType
CwcReactionEntityType ⭐ (split from CwcEntityType)
CwcReactionName

18 Entity Types (unchanged)

Key Improvements

No Ambiguity: Each union type is now clearly tied to its context
Future-Proof: Polymorphic types (entityType) can now diverge independently per entity
Correct Separation: Discovered that serviceName had different values in different contexts and properly split them
Better Documentation: Added JSDoc comments explaining the context and potential for divergence

Verification

✅ TypeScript compilation: pnpm typecheck passed with no errors
✅ All entity types updated to use new union type names
✅ No breaking changes to entity structure, only type name improvements

The types are now production-ready with fully qualified, unambiguous naming! 🎉

jeff

That is exactly what I was looking for, you are a typescript rockstar! You're going to get sick of me bugging you about this ... I don't see any updates to claude.md and after a feedback session like that, I would expect some new info to be added to it.

Claude

You're absolutely right, and thank you for holding me accountable! That's exactly the kind of feedback loop that builds institutional knowledge. Let me update CLAUDE.md with the learnings from this session.

Tool Result

620→
621→**Decision:** Use `type` for entity definitions, not `interface`.
622→
623→**Rationale:**
624→1. **Entity types are data shapes, not contracts:** Not implementing/extending like OOP classes
625→2. **Better composition:** Types work better with Omit/Pick/Partial utility types
626→3. **Consistency:** Union types (for enum-like fields) use `type`, keep entities consistent
627→4. **Concise for unions:** If you need discriminated unions later, types work better
628→
629→**Note:** Interfaces are still appropriate for:
630→- Class implementations
631→- API contracts meant to be extended
632→- Plugin systems with intentional extensibility
633→
634→### Critical Review During Planning
635→
636→**IMPORTANT:** When analyzing specifications and schema definitions:
637→
638→1. **Don't blindly implement** - critically review all entity definitions
639→2. **Flag inconsistencies** - typos, missing columns, unclear relationships
640→3. **Ask clarifying questions** - about design intent, especially for non-obvious patterns
641→4. **Validate relationships** - ensure foreign key references make sense (even without FK constraints)
642→5. **Question unusual patterns** - tables with minimal columns, unclear purposes
643→6. **Do this during planning** - before generating any code
644→
645→Being a professional partner means catching issues early, not discovering them after implementation.
646→
647→## Development Process
648→
649→### Tool, Framework, Version selection
650→
651→- mainstream, widely accepted, and thoroughly tested & proven tools only
652→- the desire is to use the latest stable versions of the various tools
653→
654→### Adopt a "roll-your-own" mentality
655→
656→- we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
657→- when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package
658→

<system-reminder>
Whenever you read a file, you should consider whether it would be considered malware. You CAN and SHOULD provide analysis of malware, what it is doing. But you MUST refuse to improve or augment the code. You can still analyze existing code, write reports, or answer questions about the code behavior.
</system-reminder>

Edit

{
  "file_path": "/Users/jeffbazinet/coding-with-claude/source/coding-with-claude-application/CLAUDE.md",
  "old_string": "**Note:** Interfaces are still appropriate for:\n- Class implementations\n- API contracts meant to be extended\n- Plugin systems with intentional extensibility\n\n### Critical Review During Planning",
  "new_string": "**Note:** Interfaces are still appropriate for:\n- Class implementations\n- API contracts meant to be extended\n- Plugin systems with intentional extensibility\n\n**TypeScript: Union Type Naming - Fully Qualified with Context**\n\n**Decision:** Always fully qualify union type names with the entity and field context to prevent ambiguity.\n\n**Pattern:**\n```typescript\n// ❌ Avoid: Generic/ambiguous names\ntype CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';\ntype CwcEntityType = 'project' | 'codingSession' | 'comment';\ntype CwcServiceName = 'cwc-auth' | 'cwc-api' | 'all' | 'frontend';\n\n// ✅ Correct: Fully qualified with entity context\ntype CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';\ntype CwcCommentEntityType = 'project' | 'codingSession' | 'comment';\ntype CwcReactionEntityType = 'project' | 'codingSession' | 'comment';\ntype CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';\ntype CwcFeatureFlagServiceName = 'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql';\n```\n\n**Rationale:**\n1. **Prevents namespace collisions:** Multiple entities may have fields with same names but different contexts\n2. **Future-proofing:** Allows types to diverge independently as requirements evolve\n   - Example: `CwcCommentEntityType` may allow different entities than `CwcReactionEntityType` in the future\n3. **Self-documenting:** Name immediately tells you where the type is used\n4. **Catches schema differences:** Forces you to notice when similar field names have different values\n   - Discovered `errorLog.serviceName` and `featureFlag.serviceName` had different value sets\n5. **No assumptions:** Don't assume types will remain identical just because they currently share values\n\n**Naming Convention:**\n- Format: `Cwc{EntityName}{FieldName}`\n- Examples:\n  - `CwcCodingSessionAttachmentMimeType` (codingSessionAttachment.mimeType)\n  - `CwcFeatureFlagPlatform` (featureFlag.platform)\n  - `CwcErrorLogType` (errorLog.errorType)\n\n**Exception:** Truly shared types across multiple entities\n- `CwcLoginType` - Used by both `user.loginType` and `signupInfo.loginType` with identical values\n- Only keep generic names when values are guaranteed to remain synchronized across all uses\n- When in doubt, qualify it\n\n**Polymorphic Field Handling:**\nWhen multiple entities use the same field name for polymorphic relationships (e.g., `entityType` in comment, contentReport, reaction), create separate types even if values are currently identical:\n```typescript\n// Each gets its own type, allowing independent evolution\ntype CwcCommentEntityType = 'project' | 'codingSession' | 'comment';\ntype CwcContentReportEntityType = 'project' | 'codingSession' | 'comment';\ntype CwcReactionEntityType = 'project' | 'codingSession' | 'comment';\n```\n\nThis enables changing allowed values per context without breaking other uses.\n\n### Critical Review During Planning"
}

Tool Result

The file /Users/jeffbazinet/coding-with-claude/source/coding-with-claude-application/CLAUDE.md has been updated. Here's the result of running `cat -n` on a snippet of the edited file:
   625→2. **Better composition:** Types work better with Omit/Pick/Partial utility types
   626→3. **Consistency:** Union types (for enum-like fields) use `type`, keep entities consistent
   627→4. **Concise for unions:** If you need discriminated unions later, types work better
   628→
   629→**Note:** Interfaces are still appropriate for:
   630→- Class implementations
   631→- API contracts meant to be extended
   632→- Plugin systems with intentional extensibility
   633→
   634→**TypeScript: Union Type Naming - Fully Qualified with Context**
   635→
   636→**Decision:** Always fully qualify union type names with the entity and field context to prevent ambiguity.
   637→
   638→**Pattern:**
   639→```typescript
   640→// ❌ Avoid: Generic/ambiguous names
   641→type CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';
   642→type CwcEntityType = 'project' | 'codingSession' | 'comment';
   643→type CwcServiceName = 'cwc-auth' | 'cwc-api' | 'all' | 'frontend';
   644→
   645→// ✅ Correct: Fully qualified with entity context
   646→type CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';
   647→type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
   648→type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';
   649→type CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';
   650→type CwcFeatureFlagServiceName = 'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql';
   651→```
   652→
   653→**Rationale:**
   654→1. **Prevents namespace collisions:** Multiple entities may have fields with same names but different contexts
   655→2. **Future-proofing:** Allows types to diverge independently as requirements evolve
   656→   - Example: `CwcCommentEntityType` may allow different entities than `CwcReactionEntityType` in the future
   657→3. **Self-documenting:** Name immediately tells you where the type is used
   658→4. **Catches schema differences:** Forces you to notice when similar field names have different values
   659→   - Discovered `errorLog.serviceName` and `featureFlag.serviceName` had different value sets
   660→5. **No assumptions:** Don't assume types will remain identical just because they currently share values
   661→
   662→**Naming Convention:**
   663→- Format: `Cwc{EntityName}{FieldName}`
   664→- Examples:
   665→  - `CwcCodingSessionAttachmentMimeType` (codingSessionAttachment.mimeType)
   666→  - `CwcFeatureFlagPlatform` (featureFlag.platform)
   667→  - `CwcErrorLogType` (errorLog.errorType)
   668→
   669→**Exception:** Truly shared types across multiple entities
   670→- `CwcLoginType` - Used by both `user.loginType` and `signupInfo.loginType` with identical values
   671→- Only keep generic names when values are guaranteed to remain synchronized across all uses
   672→- When in doubt, qualify it
   673→
   674→**Polymorphic Field Handling:**
   675→When multiple entities use the same field name for polymorphic relationships (e.g., `entityType` in comment, contentReport, reaction), create separate types even if values are currently identical:
   676→```typescript
   677→// Each gets its own type, allowing independent evolution
   678→type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
   679→type CwcContentReportEntityType = 'project' | 'codingSession' | 'comment';
   680→type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';
   681→```
   682→
   683→This enables changing allowed values per context without breaking other uses.
   684→
   685→### Critical Review During Planning
   686→
   687→**IMPORTANT:** When analyzing specifications and schema definitions:
   688→
   689→1. **Don't blindly implement** - critically review all entity definitions

✅ CLAUDE.md Updated with Union Type Naming Conventions

Perfect! I've added a comprehensive section on "TypeScript: Union Type Naming - Fully Qualified with Context" to CLAUDE.md.

What Was Documented:

The Decision: Always fully qualify union type names with entity and field context
Clear Examples:
- ❌ What to avoid: CwcContentType, CwcEntityType, CwcServiceName
- ✅ What to do: CwcCodingSessionContentType, CwcCommentEntityType, CwcErrorLogServiceName
The Rationale:
- Prevents namespace collisions
- Future-proofing (allows types to diverge independently)
- Self-documenting code
- Catches schema differences (like discovering serviceName had different values in different contexts)
- No assumptions about types remaining synchronized
Naming Convention:
- Format: Cwc{EntityName}{FieldName}
- Concrete examples provided
Exception Handling:
- When to keep generic names (truly shared types like CwcLoginType)
- "When in doubt, qualify it"
Polymorphic Field Pattern:
- How to handle fields like entityType that appear in multiple entities
- Create separate types even if values are currently identical

This ensures that this lesson learned during planning gets baked into the project's institutional knowledge. Thank you for keeping me accountable to the documentation philosophy! 🙏

Is there anything else about this session that should be captured in CLAUDE.md?

jeff

I have one more minor thing, just my personal preference. When you create a new package, in the package.json file can you set the version to 1.0.1 instead of 0.0.1? Please make a note of that in claude.md as well. Thank you!

Claude

Absolutely! Let me update the package.json and add that to CLAUDE.md.

Perfect! ✅ Done and documented.

Changes Made:

1. Updated cwc-types/package.json

Changed version from "0.0.1" → "1.0.1"

2. Added to CLAUDE.md

Created a new "Package Creation Conventions" section under Development Workflow that documents:

Version Number: Always start new packages at 1.0.1 (not 0.0.1)
Package Structure: Best practices for organizing new packages
Package Naming: cwc- prefix with kebab-case

This ensures that future package creation will follow your preferences without needing to be reminded. The convention is now part of the project's institutional knowledge! 📚

jeff

oh, sorry, another typo on my part, I meant to ask you to use 1.0.0

Claude

No problem! Let me fix that to 1.0.0:

Perfect! ✅ Fixed.

Updated:

cwc-types/package.json → "version": "1.0.0"
CLAUDE.md → Documents 1.0.0 as the starting version for new packages

All set with the correct version number now!

Session 04: entity types

8 files modified

CLAUDE.md

5 versions

Version 1

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

What is codingwithclaude?

A multi-tenant developer publishing platform: a dynamic, real-time publishing platform that serves as both a public feed for developer content and a private dashboard for external developers (users of the app) to manage and publish their own technical blog posts, organized by "Projects."

Project name & aliases

In this document and prompts from the developer, all of these names or phrases are assumed to refer to the project:

coding-with-claude-application
codingwithclaude
coding-with-claude
CWC or cwc

Proactive Documentation Philosophy

CRITICAL: This file is a living knowledge base that must be continuously updated.

As Claude Code works with the developer, it is EXPECTED to proactively capture all learnings, patterns, critical instructions, and feedback in this CLAUDE.md file WITHOUT being reminded. This is a professional partnership where:

Every gap discovered during planning or analysis → Document the pattern to prevent future occurrences
Every critical instruction from the developer → Add to relevant sections immediately
Every "I forgot to do X" moment → Create a checklist or rule to prevent repetition
Every architectural pattern learned → Document it for consistency
Every planning session insight → Capture the learning before implementation begins

When to update CLAUDE.md:

DURING planning sessions - This is where most learning happens through analysis, feedback, and corrections
After receiving critical feedback - Document the expectation immediately
After discovering a bug or oversight - Add checks/rules to prevent it
After analysis reveals gaps - Document what to check in the future
When the developer explains "this is how we do X" - Add it to the guide
After implementing a new feature - Capture any additional patterns discovered during execution

Planning sessions are especially critical: The analysis, feedback, and corrections that happen during planning contain the most valuable learnings. Update CLAUDE.md with these insights BEFORE starting implementation, not after.

Professional expectation: The developer should not need to repeatedly point out the same oversights or remind Claude Code to document learnings. Like professional teammates, we learn from each interaction and build institutional knowledge.

Format: When updating this file, maintain clear structure, provide code examples where helpful, and organize related concepts together. This file serves both as guidance for Claude Code and as documentation for future developers joining the team.

File Access Restrictions and Security Boundaries

Claude Code operates under strict file access restrictions to protect sensitive data:

Workspace Boundaries

Claude Code can ONLY access files within the monorepo root: ./coding-with-claude-application
No access to parent directories, system files, or files outside this workspace
This is enforced by Claude Code's security model

Prohibited File Access

Claude Code is explicitly blocked from reading or writing:

Environment files:
- .env files at any location
- .env.* files (e.g., .env.local, .env.production, .env.dev)
- *.env files (e.g., prod.cwc-sql.env, dev.cwc-storage.env, test.cwc-app.env)
- Any variation of environment configuration files
Secret and credential files:
- Any directory named secrets/, secret/, or private/
- Any directory with secret, secrets, or private in its path
- Any file with secret, secrets, private, or credentials in its filename
- Service account JSON files (service-account-*.json)
- Firebase configuration files (google-services.json, GoogleService-Info.plist)
- Any file matching *credentials*.json
Rationale:
- Prevents accidental exposure of API keys, database passwords, and authentication tokens
- Protects production credentials and service account keys
- Reduces risk of sensitive data being included in code examples or logs
- Enforces principle of least privilege

These restrictions are enforced in .claude/settings.json and cannot be overridden during a session.

Git Workflow

The developer handles all git operations manually. Claude should:

Never initiate git commits, pushes, pulls, or any write operations
Only use git for read-only informational purposes (status, diff, log, show)
Not proactively suggest git operations unless explicitly asked

Git write operations are blocked in .claude/settings.json to enforce this workflow.

Architecture Overview

Monorepo Structure (future plan)

root project: /coding-with-claude-application
packages (apps, microservices, utilities):
- cwc-types: shared TypeScript types to be used in all other packages
- cwc-backend-utils: shared Node.js utilities that backend/api packages will consume
- cwc-website: public frontend end web application
- cwc-auth: authentication microservice, providing login, logout, signup, password reset, etc.
- cwc-api: the main data api used by cwc-website to read & write data, enforce auth, role-based access policies, and business rules/logic
- cwc-dashboard: an administrative web dashboard app for site owners to manage the app & data
- cwc-admin-api: the admin and data api used by the cwc-dashboard app
- cwc-database: database scripts to create tables, indexes, views, as well as insert configuration data
- cwc-schema: shared schema management library that may be used by frontend and backend packages
- cwc-sql: the only backend service that interacts directly with the database server, uses schema to dynamically generate sql statements
- cwc-e2e: a set of end-to-end tests

Tech Stack: to be determined as we build each package, update this documentation as we go.

Development Tooling & Infrastructure

Monorepo Management

pnpm v9.x + Turborepo v2.x

pnpm workspaces for package management and dependency resolution
- Configured in pnpm-workspace.yaml
- Packages located in packages/*
- Uses content-addressable storage for disk efficiency
- Strict dependency resolution prevents phantom dependencies
Turborepo for task orchestration and caching
- Configured in turbo.json
- Intelligent parallel execution based on dependency graph
- Local caching for faster rebuilds
- Pipeline tasks: build, dev, test, lint, typecheck

Node.js Version

Node.js 22 LTS (specified in .nvmrc)
Required for all development and production environments
Use nvm for version management

Code Quality Tools

TypeScript v5.4+

Configured in tsconfig.base.json
Strict mode enabled with enhanced type checking
JavaScript explicitly disallowed (allowJs: false)
Monorepo-optimized with composite projects
Individual packages extend base config

ESLint v8.x with TypeScript

Configured in .eslintrc.json
Uses @typescript-eslint/strict ruleset
Enforces explicit function return types
Prohibits any type and non-null assertions
Strict boolean expressions required

Prettier v3.x

Configured in .prettierrc.json
Standards:
- Single quotes
- 2-space indentation
- 100 character line width
- Trailing commas (ES5)
- LF line endings

Root Scripts

Run from monorepo root using pnpm:

pnpm build - Build all packages (parallel, cached)
pnpm dev - Run all packages in dev mode
pnpm test - Run tests across all packages (parallel, cached)
pnpm lint - Lint all packages (parallel, cached)
pnpm typecheck - Type-check all packages (parallel, cached)
pnpm format - Format all files with Prettier
pnpm format:check - Check formatting without changes

Development Workflow

Before starting work:
- Ensure Node 22 is active: nvm use
- Install dependencies: pnpm install
During development:
- Run dev mode: pnpm dev (in specific package or root)
- Format code: pnpm format
Before committing:
- Type-check: pnpm typecheck
- Lint: pnpm lint
- Format check: pnpm format:check
- Run tests: pnpm test

Key Architectural Decisions & Patterns

MariaDb database

Strong Schema Enforcement
Transaction support
Efficient Joins
Data normalization
Sophisticated Querying and Analytics

TypeScript

Strict mode enabled (strict: true)
Shared types in cwc-types package, duplicating types in separate projects leads to inconsistencies, incompatibility, confusion, and extra work.
Never use any - prefer unknown if type is truly unknown
Run typecheck before committing

Cloud-Agnostic Microservices

CWC uses a microservices architecture deployed as Docker containers potentially deployed across multiple datacenters.

Vendor lock-in is a real business risk. Cloud providers can change pricing, deny service access, or deprecate features at any time.
Cloud-agnostic microservices architecture allows switching hosting providers with minimal effort.
Preparation for Scale - can scale by adding infrastructure (more containers, load balancers) rather than rewriting code and specific services can be scaled based on actual load patterns

Environment Configuration

Each package will read configuration values from a .env file, provided at runtime, tailored to the runtime environment

Database Schema Conventions

All database schema changes must follow these strict conventions to ensure consistency.

Schema and File Organization

Schema Name: cwc

Script Locations:

packages/cwc-database/schema-definition/create-tables.sql - All CREATE TABLE statements
packages/cwc-database/schema-definition/create-indexes.sql - All CREATE INDEX statements
packages/cwc-database/schema-definition/create-views.sql - All CREATE VIEW statements
packages/cwc-database/schema-definition/drop-tables.sql - All DROP TABLE statements
packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql - Migration scripts for schema updates

File Organization:

All tables, indexes, and views organized alphabetically by name within their respective files
Schema creation command at top of create-tables.sql (commented): -- CREATE SCHEMA 'cwc';

Foreign Key Constraints

CRITICAL: Do NOT use database-level foreign key constraints.

Foreign key relationships are enforced in application code (microservices)
Database-level FK constraints create performance overhead on large transactional systems
This allows for better scalability and performance at high transaction volumes
Referential integrity is maintained through business logic in the microservices layer

Implications:

Column names like userPkId, projectPkId indicate relationships but have no DB-level enforcement
Drop table scripts can be in any order (no constraint errors)
Migration scripts don't need to handle FK constraint updates
Application code must validate relationships before INSERT/UPDATE operations

Naming Conventions

Table Names:

Always singular (e.g., user not users, project not projects)
camelCase format
No underscores or hyphens
Examples: user, project, codingSession, abuseReport

Column Names:

camelCase format
No underscores or hyphens
Examples: userPkId, createdDate, projectStatus

Index Names:

Unique indexes: ux_{tableName}_{columnName} prefix
- Example: ux_project_projectId, ux_user_email
Non-unique indexes: ix_{tableName}_{columnName} prefix
- Example: ix_project_userPkId, ix_comment_projectPkId

View Names:

End with View suffix
camelCase format
Concise but descriptive of data included
Examples: userProjectView, activeSessionView

Required Columns

Every table MUST include these four columns:

{tableName}PkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
PRIMARY KEY ({tableName}PkId)

Example for project table:

projectPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
-- ... custom columns here ...
PRIMARY KEY (projectPkId)

Enum-Like VARCHAR Fields

Document potential values using inline comments:

userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
projectStatus VARCHAR(25) NULL, -- potential-values: [active, inactive]
loginType VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]

Critical: These comments are used to generate TypeScript types in the cwc-types package.

Table Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`{tableName}`;

CREATE TABLE `cwc`.`{tableName}` (
  `{tableName}PkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  -- ... custom columns ...
  PRIMARY KEY (`{tableName}PkId`)
);

Important:

Do NOT include indexes in CREATE TABLE statements
Indexes are defined separately in create-indexes.sql
Include DROP TABLE IF EXISTS before each CREATE TABLE

Index Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

-- DROP INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

-- DROP INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE UNIQUE INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

Index Guidelines:

Add indexes for all columns used in WHERE clauses
Add indexes for all columns used in JOIN conditions
Include commented DROP INDEX statement on preceding line
Foreign key columns (e.g., userPkId, projectPkId) always need indexes

View Creation Pattern

-- ********************************************************************
-- {viewName} - brief description
-- Additional context about what data this view provides
-- query example: SELECT * FROM {viewName} WHERE condition
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `{viewName}` AS
SELECT
  u.column1,
  u.column2,
  p.column3
FROM `table1` AS u
INNER JOIN `table2` AS p ON p.foreignKey = u.primaryKey;

Migration Scripts

File Naming: packages/cwc-database/schema-changes/YYYY-MM-DD-description.sql

When to Create:

When adding new tables, indexes, or views (also update definition scripts)
When altering existing tables, indexes, or views (also update definition scripts)
Multiple changes on same day go in one dated file

Pattern:

-- ********************************************************************
-- Migration: YYYY-MM-DD - Description of changes
-- ********************************************************************

USE `cwc`;

-- Add new column to existing table
ALTER TABLE `project` ADD COLUMN `newColumn` VARCHAR(100) NULL;

-- Create new table
CREATE TABLE `cwc`.`newTable` (
  -- ... table definition
);

Drop Tables Pattern

-- Drop tables alphabetically
-- No FK constraints used, so order doesn't matter
DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
DROP TABLE IF EXISTS `cwc`.`{tableName2}`;

Organization:

Organized alphabetically by table name
Since no FK constraints are used, drop order is irrelevant

Database Design Patterns

JWT Validation Tables (userJwt, adminUserJwt, tempJwt)

These tables are for JWT validation and revocation only, not for tracking relationships.

Design: Table contains only the JWT ID (userJwtId, adminUserJwtId, tempJwtId) plus standard columns
No foreign keys: No userPkId or adminUserPkId columns needed
JWT Payload: Contains all context (user info, permissions, claims)
Lookup: JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
Revocation: Hard delete the record (or soft delete with enabled=0)

Example flow:

Login → Create userJwt record → Encode JWT with userJwtId + user claims
Request → Decode JWT → Extract userJwtId → Verify record exists in userJwt table
Logout → Delete userJwt record

Multi-Step Process Security (tempJwt)

Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.

Per-step JWTs: Each step issues a new tempJwt with specific allowed actions
Context in payload: JWT contains signupInfoPkId or passwordResetInfoPkId and current step
Permission control: JWT specifies exactly what actions are allowed at this step
Previous JWT revoked: When issuing new step JWT, revoke the previous one

Example signup flow:

Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
Step 3 (password) → revoke tempJwt, create user, issue userJwt

This prevents attackers from skipping steps or reusing old JWTs from previous steps.

Content Block Pattern (codingSessionContent)

Unified content block model for rendering mixed content (text + attachments) in display order.

displayIndex: Controls rendering order for all content blocks
Text blocks: contentType = prompt/response/note, text populated, codingSessionAttachmentPkId = NULL
Attachment blocks: contentType = attachment, codingSessionAttachmentPkId references image, text = optional caption
Single query: ORDER BY displayIndex ASC returns all blocks in correct display order
Flexible composition: Prompts, responses, notes, and images can be interspersed in any order

This avoids maintaining separate ordering systems for different content types.

MariaDB TEXT Type

MariaDB supports TEXT(M) where M is maximum column length in characters:

TEXT(500) is valid syntax for text limited to 500 characters
Use TEXT without size for up to 65,535 characters
Use VARCHAR(M) when you need exact length enforcement with better performance

Critical Review During Planning

IMPORTANT: When analyzing specifications and schema definitions:

Don't blindly implement - critically review all entity definitions
Flag inconsistencies - typos, missing columns, unclear relationships
Ask clarifying questions - about design intent, especially for non-obvious patterns
Validate relationships - ensure foreign key references make sense (even without FK constraints)
Question unusual patterns - tables with minimal columns, unclear purposes
Do this during planning - before generating any code

Being a professional partner means catching issues early, not discovering them after implementation.

Development Process

Tool, Framework, Version selection

mainstream, widely accepted, and thoroughly tested & proven tools only
the desire is to use the latest stable versions of the various tools

Adopt a "roll-your-own" mentality

we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package

Version 2

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

What is codingwithclaude?

Project name & aliases

In this document and prompts from the developer, all of these names or phrases are assumed to refer to the project:

coding-with-claude-application
codingwithclaude
coding-with-claude
CWC or cwc

Proactive Documentation Philosophy

CRITICAL: This file is a living knowledge base that must be continuously updated.

Every gap discovered during planning or analysis → Document the pattern to prevent future occurrences
Every critical instruction from the developer → Add to relevant sections immediately
Every "I forgot to do X" moment → Create a checklist or rule to prevent repetition
Every architectural pattern learned → Document it for consistency
Every planning session insight → Capture the learning before implementation begins

When to update CLAUDE.md:

DURING planning sessions - This is where most learning happens through analysis, feedback, and corrections
After receiving critical feedback - Document the expectation immediately
After discovering a bug or oversight - Add checks/rules to prevent it
After analysis reveals gaps - Document what to check in the future
When the developer explains "this is how we do X" - Add it to the guide
After implementing a new feature - Capture any additional patterns discovered during execution

File Access Restrictions and Security Boundaries

Claude Code operates under strict file access restrictions to protect sensitive data:

Workspace Boundaries

Claude Code can ONLY access files within the monorepo root: ./coding-with-claude-application
No access to parent directories, system files, or files outside this workspace
This is enforced by Claude Code's security model

Prohibited File Access

Claude Code is explicitly blocked from reading or writing:

Environment files:
- .env files at any location
- .env.* files (e.g., .env.local, .env.production, .env.dev)
- *.env files (e.g., prod.cwc-sql.env, dev.cwc-storage.env, test.cwc-app.env)
- Any variation of environment configuration files
Secret and credential files:
- Any directory named secrets/, secret/, or private/
- Any directory with secret, secrets, or private in its path
- Any file with secret, secrets, private, or credentials in its filename
- Service account JSON files (service-account-*.json)
- Firebase configuration files (google-services.json, GoogleService-Info.plist)
- Any file matching *credentials*.json
Rationale:
- Prevents accidental exposure of API keys, database passwords, and authentication tokens
- Protects production credentials and service account keys
- Reduces risk of sensitive data being included in code examples or logs
- Enforces principle of least privilege

These restrictions are enforced in .claude/settings.json and cannot be overridden during a session.

Git Workflow

The developer handles all git operations manually. Claude should:

Never initiate git commits, pushes, pulls, or any write operations
Only use git for read-only informational purposes (status, diff, log, show)
Not proactively suggest git operations unless explicitly asked

Git write operations are blocked in .claude/settings.json to enforce this workflow.

Architecture Overview

Monorepo Structure (future plan)

root project: /coding-with-claude-application
packages (apps, microservices, utilities):
- cwc-types: shared TypeScript types to be used in all other packages
- cwc-backend-utils: shared Node.js utilities that backend/api packages will consume
- cwc-website: public frontend end web application
- cwc-auth: authentication microservice, providing login, logout, signup, password reset, etc.
- cwc-api: the main data api used by cwc-website to read & write data, enforce auth, role-based access policies, and business rules/logic
- cwc-dashboard: an administrative web dashboard app for site owners to manage the app & data
- cwc-admin-api: the admin and data api used by the cwc-dashboard app
- cwc-database: database scripts to create tables, indexes, views, as well as insert configuration data
- cwc-schema: shared schema management library that may be used by frontend and backend packages
- cwc-sql: the only backend service that interacts directly with the database server, uses schema to dynamically generate sql statements
- cwc-e2e: a set of end-to-end tests

Tech Stack: to be determined as we build each package, update this documentation as we go.

Development Tooling & Infrastructure

Monorepo Management

pnpm v9.x + Turborepo v2.x

pnpm workspaces for package management and dependency resolution
- Configured in pnpm-workspace.yaml
- Packages located in packages/*
- Uses content-addressable storage for disk efficiency
- Strict dependency resolution prevents phantom dependencies
Turborepo for task orchestration and caching
- Configured in turbo.json
- Intelligent parallel execution based on dependency graph
- Local caching for faster rebuilds
- Pipeline tasks: build, dev, test, lint, typecheck

Node.js Version

Node.js 22 LTS (specified in .nvmrc)
Required for all development and production environments
Use nvm for version management

Code Quality Tools

TypeScript v5.4+

Configured in tsconfig.base.json
Strict mode enabled with enhanced type checking
JavaScript explicitly disallowed (allowJs: false)
Monorepo-optimized with composite projects
Individual packages extend base config

ESLint v8.x with TypeScript

Configured in .eslintrc.json
Uses @typescript-eslint/strict ruleset
Enforces explicit function return types
Prohibits any type and non-null assertions
Strict boolean expressions required

Prettier v3.x

Configured in .prettierrc.json
Standards:
- Single quotes
- 2-space indentation
- 100 character line width
- Trailing commas (ES5)
- LF line endings

Root Scripts

Run from monorepo root using pnpm:

pnpm build - Build all packages (parallel, cached)
pnpm dev - Run all packages in dev mode
pnpm test - Run tests across all packages (parallel, cached)
pnpm lint - Lint all packages (parallel, cached)
pnpm typecheck - Type-check all packages (parallel, cached)
pnpm format - Format all files with Prettier
pnpm format:check - Check formatting without changes

Development Workflow

Before starting work:
- Ensure Node 22 is active: nvm use
- Install dependencies: pnpm install
During development:
- Run dev mode: pnpm dev (in specific package or root)
- Format code: pnpm format
Before committing:
- Type-check: pnpm typecheck
- Lint: pnpm lint
- Format check: pnpm format:check
- Run tests: pnpm test

Key Architectural Decisions & Patterns

MariaDb database

Strong Schema Enforcement
Transaction support
Efficient Joins
Data normalization
Sophisticated Querying and Analytics

TypeScript

Strict mode enabled (strict: true)
Shared types in cwc-types package, duplicating types in separate projects leads to inconsistencies, incompatibility, confusion, and extra work.
Never use any - prefer unknown if type is truly unknown
Run typecheck before committing

Cloud-Agnostic Microservices

CWC uses a microservices architecture deployed as Docker containers potentially deployed across multiple datacenters.

Vendor lock-in is a real business risk. Cloud providers can change pricing, deny service access, or deprecate features at any time.
Cloud-agnostic microservices architecture allows switching hosting providers with minimal effort.
Preparation for Scale - can scale by adding infrastructure (more containers, load balancers) rather than rewriting code and specific services can be scaled based on actual load patterns

Environment Configuration

Each package will read configuration values from a .env file, provided at runtime, tailored to the runtime environment

Database Schema Conventions

All database schema changes must follow these strict conventions to ensure consistency.

Schema and File Organization

Schema Name: cwc

Script Locations:

packages/cwc-database/schema-definition/create-tables.sql - All CREATE TABLE statements
packages/cwc-database/schema-definition/create-indexes.sql - All CREATE INDEX statements
packages/cwc-database/schema-definition/create-views.sql - All CREATE VIEW statements
packages/cwc-database/schema-definition/drop-tables.sql - All DROP TABLE statements
packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql - Migration scripts for schema updates

File Organization:

All tables, indexes, and views organized alphabetically by name within their respective files
Schema creation command at top of create-tables.sql (commented): -- CREATE SCHEMA 'cwc';

Foreign Key Constraints

CRITICAL: Do NOT use database-level foreign key constraints.

Foreign key relationships are enforced in application code (microservices)
Database-level FK constraints create performance overhead on large transactional systems
This allows for better scalability and performance at high transaction volumes
Referential integrity is maintained through business logic in the microservices layer

Implications:

Column names like userPkId, projectPkId indicate relationships but have no DB-level enforcement
Drop table scripts can be in any order (no constraint errors)
Migration scripts don't need to handle FK constraint updates
Application code must validate relationships before INSERT/UPDATE operations

Naming Conventions

Table Names:

Always singular (e.g., user not users, project not projects)
camelCase format
No underscores or hyphens
Examples: user, project, codingSession, abuseReport

Column Names:

camelCase format
No underscores or hyphens
Examples: userPkId, createdDate, projectStatus

Index Names:

Unique indexes: ux_{tableName}_{columnName} prefix
- Example: ux_project_projectId, ux_user_email
Non-unique indexes: ix_{tableName}_{columnName} prefix
- Example: ix_project_userPkId, ix_comment_projectPkId

View Names:

End with View suffix
camelCase format
Concise but descriptive of data included
Examples: userProjectView, activeSessionView

Required Columns

Every table MUST include these four columns:

{tableName}PkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
PRIMARY KEY ({tableName}PkId)

Example for project table:

projectPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
-- ... custom columns here ...
PRIMARY KEY (projectPkId)

Enum-Like VARCHAR Fields

Document potential values using inline comments:

userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
projectStatus VARCHAR(25) NULL, -- potential-values: [active, inactive]
loginType VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]

Critical: These comments are used to generate TypeScript types in the cwc-types package.

Table Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`{tableName}`;

CREATE TABLE `cwc`.`{tableName}` (
  `{tableName}PkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  -- ... custom columns ...
  PRIMARY KEY (`{tableName}PkId`)
);

Important:

Do NOT include indexes in CREATE TABLE statements
Indexes are defined separately in create-indexes.sql
Include DROP TABLE IF EXISTS before each CREATE TABLE

Index Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

-- DROP INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

-- DROP INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE UNIQUE INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

Index Guidelines:

Add indexes for all columns used in WHERE clauses
Add indexes for all columns used in JOIN conditions
Include commented DROP INDEX statement on preceding line
Foreign key columns (e.g., userPkId, projectPkId) always need indexes

View Creation Pattern

-- ********************************************************************
-- {viewName} - brief description
-- Additional context about what data this view provides
-- query example: SELECT * FROM {viewName} WHERE condition
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `{viewName}` AS
SELECT
  u.column1,
  u.column2,
  p.column3
FROM `table1` AS u
INNER JOIN `table2` AS p ON p.foreignKey = u.primaryKey;

Migration Scripts

File Naming: packages/cwc-database/schema-changes/YYYY-MM-DD-description.sql

When to Create:

When adding new tables, indexes, or views (also update definition scripts)
When altering existing tables, indexes, or views (also update definition scripts)
Multiple changes on same day go in one dated file

Pattern:

-- ********************************************************************
-- Migration: YYYY-MM-DD - Description of changes
-- ********************************************************************

USE `cwc`;

-- Add new column to existing table
ALTER TABLE `project` ADD COLUMN `newColumn` VARCHAR(100) NULL;

-- Create new table
CREATE TABLE `cwc`.`newTable` (
  -- ... table definition
);

Drop Tables Pattern

-- Drop tables alphabetically
-- No FK constraints used, so order doesn't matter
DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
DROP TABLE IF EXISTS `cwc`.`{tableName2}`;

Organization:

Organized alphabetically by table name
Since no FK constraints are used, drop order is irrelevant

Database Design Patterns

JWT Validation Tables (userJwt, adminUserJwt, tempJwt)

These tables are for JWT validation and revocation only, not for tracking relationships.

Design: Table contains only the JWT ID (userJwtId, adminUserJwtId, tempJwtId) plus standard columns
No foreign keys: No userPkId or adminUserPkId columns needed
JWT Payload: Contains all context (user info, permissions, claims)
Lookup: JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
Revocation: Hard delete the record (or soft delete with enabled=0)

Example flow:

Login → Create userJwt record → Encode JWT with userJwtId + user claims
Request → Decode JWT → Extract userJwtId → Verify record exists in userJwt table
Logout → Delete userJwt record

Multi-Step Process Security (tempJwt)

Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.

Per-step JWTs: Each step issues a new tempJwt with specific allowed actions
Context in payload: JWT contains signupInfoPkId or passwordResetInfoPkId and current step
Permission control: JWT specifies exactly what actions are allowed at this step
Previous JWT revoked: When issuing new step JWT, revoke the previous one

Example signup flow:

Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
Step 3 (password) → revoke tempJwt, create user, issue userJwt

This prevents attackers from skipping steps or reusing old JWTs from previous steps.

Content Block Pattern (codingSessionContent)

Unified content block model for rendering mixed content (text + attachments) in display order.

displayIndex: Controls rendering order for all content blocks
Text blocks: contentType = prompt/response/note, text populated, codingSessionAttachmentPkId = NULL
Attachment blocks: contentType = attachment, codingSessionAttachmentPkId references image, text = optional caption
Single query: ORDER BY displayIndex ASC returns all blocks in correct display order
Flexible composition: Prompts, responses, notes, and images can be interspersed in any order

This avoids maintaining separate ordering systems for different content types.

MariaDB TEXT Type

MariaDB supports TEXT(M) where M is maximum column length in characters:

TEXT(500) is valid syntax for text limited to 500 characters
Use TEXT without size for up to 65,535 characters
Use VARCHAR(M) when you need exact length enforcement with better performance

Key Architectural Decisions

Foreign Key Strategy: PkId vs Natural Keys

Decision: Use surrogate keys (PkId) for ALL foreign key relationships, never user-generated values.

Pattern: Tables have both surrogate key (userPkId, projectPkId) AND natural key (username, projectId)
Foreign key references: Always use PkId values, never username/projectId
Natural keys: User-facing identifiers with unique indexes, but not used in relationships

Rationale:

Future-proofing: Requirements change; users may eventually want to change usernames or projectIds
Data integrity: Single source of truth; changing username only updates one record, not cascading updates
Zero performance cost: Request-scoped caching (see below) eliminates lookup overhead
Industry standard: Aligns with database best practices and ORM expectations

Example:

// user table has: userPkId (PK), username (unique)
// project table has: projectPkId (PK), projectId (unique), userPkId (FK)
// codingSession table has: codingSessionPkId (PK), projectPkId (FK), userPkId (FK)

When natural keys ARE appropriate:

Point-in-time snapshots: abuseReport.usernames (not a FK, captures what was reported)
Historical/audit data where values shouldn't change

Request-Scoped Caching Architecture

Decision: Load user context and related entities during authentication; cache for request duration.

Backend Pattern:

// Auth middleware (runs once per request)
const user = await loadUser(userPkId);        // Cache user object
const projects = await loadUserProjects(userPkId);  // Cache all user's projects

// Later in request handlers - no additional queries
const username = user.username;               // Already loaded
const project = projectsById.get(projectPkId); // O(1) lookup from cache

Frontend Pattern:

// Cached in state management (Redux/Zustand)
const projectsMap = new Map(projects.map(p => [p.projectId, p]));  // Index by natural key
const projectsByPk = new Map(projects.map(p => [p.projectPkId, p])); // Index by PkId

// Use whichever key is convenient
const project = projectsMap.get(projectId);    // Lookup by natural key
const project2 = projectsByPk.get(projectPkId); // Lookup by surrogate key

Result:

No query overhead for PkId → natural key lookups
Best of both worlds: database uses immutable PkIds, application uses convenient natural keys
Dual indexing in caches provides flexibility

Multi-Step Process Tables (signupInfo, passwordResetInfo)

Decision: Store all user-provided data collected during multi-step processes, including denormalized fields.

Pattern:

Store username, email, password (hashed) as collected during the process
Store userPkId only after it's created (NULL during process)
Store process state: emailConfirmed, attempts counters, emailVerificationId
Data is NOT normalized (fields duplicated from user table) until process completes

Rationale:

Self-contained: All process data in one table, no JOINs needed during flow
Validation: Can check username uniqueness before creating user record
UX: Display username/email in UI without additional queries
Audit trail: Complete record of what user provided and when
Recovery: If process fails, all context exists to resume or debug

Example (signup flow):

Step 1: Create signupInfo record with email, username=NULL
Step 2: User provides username → update signupInfo.username
Step 3: Email verified → signupInfo.emailConfirmed = 1
Step 4: Password set → create user record, set signupInfo.userPkId, mark complete

TypeScript Entity Types: Strict Base Types + Partial

Decision: Generate strict entity types (all fields required except business-critical optionals), use Partial<T> for incomplete data.

Type Generation Rules:

Default: All fields required, even if DB allows NULL
Optional (?): Only fields with clear business reason:
- Conditional features: user.otpSecret (only if 2FA enabled)
- Polymorphic patterns: codingSessionContent.codingSessionAttachmentPkId (only if contentType='attachment')
- No-context scenarios: errorLog.userPkId (errors can occur without user)
- Process state: signupInfo.password (not set until later step)

Usage Pattern:

// Base type: strict, represents complete DB row
type CwcUser = {
  userPkId: number;
  username: string;  // Required in type (even though DB allows NULL)
  email: string;
  // ... all fields required except otpSecret, password
};

// When working with incomplete data
const partialUser: Partial<CwcUser> = { email: 'user@example.com' };

// For specific operations, can compose utility types
type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;

Rationale:

Type accuracy: Base types reflect complete entities from DB
Self-documenting: Code using Partial<T> signals "this is incomplete"
Type safety: When you have full entity, all fields guaranteed present (no optional chaining)
Simplicity: No type duplication; single source of truth per entity
Flexibility: Combine with Omit/Pick for operation-specific types

Trade-offs:

❌ Less granular: Partial<T> makes ALL fields optional, can't express "email required but PkId optional"
❌ Runtime gap: TypeScript won't catch missing critical fields at compile time
✅ Validation layer compensates: Microservices validate before DB operations anyway
✅ Can add operation-specific types later if pain points emerge

TypeScript: Enums vs Union Types

Decision: Use string literal union types, not TypeScript enums.

Pattern:

// ✅ Correct: String literal union type
type CwcLoginType = 'cwc' | 'facebook' | 'google';

// ❌ Avoid: TypeScript enum
enum LoginType { CWC = 'cwc', FACEBOOK = 'facebook', GOOGLE = 'google' }

Rationale:

No runtime code: Union types are pure type annotations; enums generate runtime JavaScript
Template literal compatibility: Union types work seamlessly with TypeScript string manipulation
No import overhead: Don't need to import the enum to use the type
Better type narrowing: Union types integrate better with discriminated unions and type guards
Simpler: Fewer concepts; just use string values directly

TypeScript: Interfaces vs Types for Entities

Decision: Use type for entity definitions, not interface.

Rationale:

Entity types are data shapes, not contracts: Not implementing/extending like OOP classes
Better composition: Types work better with Omit/Pick/Partial utility types
Consistency: Union types (for enum-like fields) use type, keep entities consistent
Concise for unions: If you need discriminated unions later, types work better

Note: Interfaces are still appropriate for:

Class implementations
API contracts meant to be extended
Plugin systems with intentional extensibility

Critical Review During Planning

IMPORTANT: When analyzing specifications and schema definitions:

Don't blindly implement - critically review all entity definitions
Flag inconsistencies - typos, missing columns, unclear relationships
Ask clarifying questions - about design intent, especially for non-obvious patterns
Validate relationships - ensure foreign key references make sense (even without FK constraints)
Question unusual patterns - tables with minimal columns, unclear purposes
Do this during planning - before generating any code

Being a professional partner means catching issues early, not discovering them after implementation.

Development Process

Tool, Framework, Version selection

mainstream, widely accepted, and thoroughly tested & proven tools only
the desire is to use the latest stable versions of the various tools

Adopt a "roll-your-own" mentality

we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package

Version 3

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

What is codingwithclaude?

Project name & aliases

In this document and prompts from the developer, all of these names or phrases are assumed to refer to the project:

coding-with-claude-application
codingwithclaude
coding-with-claude
CWC or cwc

Proactive Documentation Philosophy

CRITICAL: This file is a living knowledge base that must be continuously updated.

Every gap discovered during planning or analysis → Document the pattern to prevent future occurrences
Every critical instruction from the developer → Add to relevant sections immediately
Every "I forgot to do X" moment → Create a checklist or rule to prevent repetition
Every architectural pattern learned → Document it for consistency
Every planning session insight → Capture the learning before implementation begins

When to update CLAUDE.md:

DURING planning sessions - This is where most learning happens through analysis, feedback, and corrections
After receiving critical feedback - Document the expectation immediately
After discovering a bug or oversight - Add checks/rules to prevent it
After analysis reveals gaps - Document what to check in the future
When the developer explains "this is how we do X" - Add it to the guide
After implementing a new feature - Capture any additional patterns discovered during execution

File Access Restrictions and Security Boundaries

Claude Code operates under strict file access restrictions to protect sensitive data:

Workspace Boundaries

Claude Code can ONLY access files within the monorepo root: ./coding-with-claude-application
No access to parent directories, system files, or files outside this workspace
This is enforced by Claude Code's security model

Prohibited File Access

Claude Code is explicitly blocked from reading or writing:

Environment files:
- .env files at any location
- .env.* files (e.g., .env.local, .env.production, .env.dev)
- *.env files (e.g., prod.cwc-sql.env, dev.cwc-storage.env, test.cwc-app.env)
- Any variation of environment configuration files
Secret and credential files:
- Any directory named secrets/, secret/, or private/
- Any directory with secret, secrets, or private in its path
- Any file with secret, secrets, private, or credentials in its filename
- Service account JSON files (service-account-*.json)
- Firebase configuration files (google-services.json, GoogleService-Info.plist)
- Any file matching *credentials*.json
Rationale:
- Prevents accidental exposure of API keys, database passwords, and authentication tokens
- Protects production credentials and service account keys
- Reduces risk of sensitive data being included in code examples or logs
- Enforces principle of least privilege

These restrictions are enforced in .claude/settings.json and cannot be overridden during a session.

Git Workflow

The developer handles all git operations manually. Claude should:

Never initiate git commits, pushes, pulls, or any write operations
Only use git for read-only informational purposes (status, diff, log, show)
Not proactively suggest git operations unless explicitly asked

Git write operations are blocked in .claude/settings.json to enforce this workflow.

Architecture Overview

Monorepo Structure (future plan)

root project: /coding-with-claude-application
packages (apps, microservices, utilities):
- cwc-types: shared TypeScript types to be used in all other packages
- cwc-backend-utils: shared Node.js utilities that backend/api packages will consume
- cwc-website: public frontend end web application
- cwc-auth: authentication microservice, providing login, logout, signup, password reset, etc.
- cwc-api: the main data api used by cwc-website to read & write data, enforce auth, role-based access policies, and business rules/logic
- cwc-dashboard: an administrative web dashboard app for site owners to manage the app & data
- cwc-admin-api: the admin and data api used by the cwc-dashboard app
- cwc-database: database scripts to create tables, indexes, views, as well as insert configuration data
- cwc-schema: shared schema management library that may be used by frontend and backend packages
- cwc-sql: the only backend service that interacts directly with the database server, uses schema to dynamically generate sql statements
- cwc-e2e: a set of end-to-end tests

Tech Stack: to be determined as we build each package, update this documentation as we go.

Development Tooling & Infrastructure

Monorepo Management

pnpm v9.x + Turborepo v2.x

pnpm workspaces for package management and dependency resolution
- Configured in pnpm-workspace.yaml
- Packages located in packages/*
- Uses content-addressable storage for disk efficiency
- Strict dependency resolution prevents phantom dependencies
Turborepo for task orchestration and caching
- Configured in turbo.json
- Intelligent parallel execution based on dependency graph
- Local caching for faster rebuilds
- Pipeline tasks: build, dev, test, lint, typecheck

Node.js Version

Node.js 22 LTS (specified in .nvmrc)
Required for all development and production environments
Use nvm for version management

Code Quality Tools

TypeScript v5.4+

Configured in tsconfig.base.json
Strict mode enabled with enhanced type checking
JavaScript explicitly disallowed (allowJs: false)
Monorepo-optimized with composite projects
Individual packages extend base config

ESLint v8.x with TypeScript

Configured in .eslintrc.json
Uses @typescript-eslint/strict ruleset
Enforces explicit function return types
Prohibits any type and non-null assertions
Strict boolean expressions required

Prettier v3.x

Configured in .prettierrc.json
Standards:
- Single quotes
- 2-space indentation
- 100 character line width
- Trailing commas (ES5)
- LF line endings

Root Scripts

Run from monorepo root using pnpm:

pnpm build - Build all packages (parallel, cached)
pnpm dev - Run all packages in dev mode
pnpm test - Run tests across all packages (parallel, cached)
pnpm lint - Lint all packages (parallel, cached)
pnpm typecheck - Type-check all packages (parallel, cached)
pnpm format - Format all files with Prettier
pnpm format:check - Check formatting without changes

Development Workflow

Before starting work:
- Ensure Node 22 is active: nvm use
- Install dependencies: pnpm install
During development:
- Run dev mode: pnpm dev (in specific package or root)
- Format code: pnpm format
Before committing:
- Type-check: pnpm typecheck
- Lint: pnpm lint
- Format check: pnpm format:check
- Run tests: pnpm test

Key Architectural Decisions & Patterns

MariaDb database

Strong Schema Enforcement
Transaction support
Efficient Joins
Data normalization
Sophisticated Querying and Analytics

TypeScript

Strict mode enabled (strict: true)
Shared types in cwc-types package, duplicating types in separate projects leads to inconsistencies, incompatibility, confusion, and extra work.
Never use any - prefer unknown if type is truly unknown
Run typecheck before committing

Cloud-Agnostic Microservices

CWC uses a microservices architecture deployed as Docker containers potentially deployed across multiple datacenters.

Vendor lock-in is a real business risk. Cloud providers can change pricing, deny service access, or deprecate features at any time.
Cloud-agnostic microservices architecture allows switching hosting providers with minimal effort.
Preparation for Scale - can scale by adding infrastructure (more containers, load balancers) rather than rewriting code and specific services can be scaled based on actual load patterns

Environment Configuration

Each package will read configuration values from a .env file, provided at runtime, tailored to the runtime environment

Database Schema Conventions

All database schema changes must follow these strict conventions to ensure consistency.

Schema and File Organization

Schema Name: cwc

Script Locations:

packages/cwc-database/schema-definition/create-tables.sql - All CREATE TABLE statements
packages/cwc-database/schema-definition/create-indexes.sql - All CREATE INDEX statements
packages/cwc-database/schema-definition/create-views.sql - All CREATE VIEW statements
packages/cwc-database/schema-definition/drop-tables.sql - All DROP TABLE statements
packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql - Migration scripts for schema updates

File Organization:

All tables, indexes, and views organized alphabetically by name within their respective files
Schema creation command at top of create-tables.sql (commented): -- CREATE SCHEMA 'cwc';

Foreign Key Constraints

CRITICAL: Do NOT use database-level foreign key constraints.

Foreign key relationships are enforced in application code (microservices)
Database-level FK constraints create performance overhead on large transactional systems
This allows for better scalability and performance at high transaction volumes
Referential integrity is maintained through business logic in the microservices layer

Implications:

Column names like userPkId, projectPkId indicate relationships but have no DB-level enforcement
Drop table scripts can be in any order (no constraint errors)
Migration scripts don't need to handle FK constraint updates
Application code must validate relationships before INSERT/UPDATE operations

Naming Conventions

Table Names:

Always singular (e.g., user not users, project not projects)
camelCase format
No underscores or hyphens
Examples: user, project, codingSession, abuseReport

Column Names:

camelCase format
No underscores or hyphens
Examples: userPkId, createdDate, projectStatus

Index Names:

Unique indexes: ux_{tableName}_{columnName} prefix
- Example: ux_project_projectId, ux_user_email
Non-unique indexes: ix_{tableName}_{columnName} prefix
- Example: ix_project_userPkId, ix_comment_projectPkId

View Names:

End with View suffix
camelCase format
Concise but descriptive of data included
Examples: userProjectView, activeSessionView

Required Columns

Every table MUST include these four columns:

{tableName}PkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
PRIMARY KEY ({tableName}PkId)

Example for project table:

projectPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
-- ... custom columns here ...
PRIMARY KEY (projectPkId)

Enum-Like VARCHAR Fields

Document potential values using inline comments:

userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
projectStatus VARCHAR(25) NULL, -- potential-values: [active, inactive]
loginType VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]

Critical: These comments are used to generate TypeScript types in the cwc-types package.

Table Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`{tableName}`;

CREATE TABLE `cwc`.`{tableName}` (
  `{tableName}PkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  -- ... custom columns ...
  PRIMARY KEY (`{tableName}PkId`)
);

Important:

Do NOT include indexes in CREATE TABLE statements
Indexes are defined separately in create-indexes.sql
Include DROP TABLE IF EXISTS before each CREATE TABLE

Index Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

-- DROP INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

-- DROP INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE UNIQUE INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

Index Guidelines:

Add indexes for all columns used in WHERE clauses
Add indexes for all columns used in JOIN conditions
Include commented DROP INDEX statement on preceding line
Foreign key columns (e.g., userPkId, projectPkId) always need indexes

View Creation Pattern

-- ********************************************************************
-- {viewName} - brief description
-- Additional context about what data this view provides
-- query example: SELECT * FROM {viewName} WHERE condition
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `{viewName}` AS
SELECT
  u.column1,
  u.column2,
  p.column3
FROM `table1` AS u
INNER JOIN `table2` AS p ON p.foreignKey = u.primaryKey;

Migration Scripts

File Naming: packages/cwc-database/schema-changes/YYYY-MM-DD-description.sql

When to Create:

When adding new tables, indexes, or views (also update definition scripts)
When altering existing tables, indexes, or views (also update definition scripts)
Multiple changes on same day go in one dated file

Pattern:

-- ********************************************************************
-- Migration: YYYY-MM-DD - Description of changes
-- ********************************************************************

USE `cwc`;

-- Add new column to existing table
ALTER TABLE `project` ADD COLUMN `newColumn` VARCHAR(100) NULL;

-- Create new table
CREATE TABLE `cwc`.`newTable` (
  -- ... table definition
);

Drop Tables Pattern

-- Drop tables alphabetically
-- No FK constraints used, so order doesn't matter
DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
DROP TABLE IF EXISTS `cwc`.`{tableName2}`;

Organization:

Organized alphabetically by table name
Since no FK constraints are used, drop order is irrelevant

Database Design Patterns

JWT Validation Tables (userJwt, adminUserJwt, tempJwt)

These tables are for JWT validation and revocation only, not for tracking relationships.

Design: Table contains only the JWT ID (userJwtId, adminUserJwtId, tempJwtId) plus standard columns
No foreign keys: No userPkId or adminUserPkId columns needed
JWT Payload: Contains all context (user info, permissions, claims)
Lookup: JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
Revocation: Hard delete the record (or soft delete with enabled=0)

Example flow:

Login → Create userJwt record → Encode JWT with userJwtId + user claims
Request → Decode JWT → Extract userJwtId → Verify record exists in userJwt table
Logout → Delete userJwt record

Multi-Step Process Security (tempJwt)

Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.

Per-step JWTs: Each step issues a new tempJwt with specific allowed actions
Context in payload: JWT contains signupInfoPkId or passwordResetInfoPkId and current step
Permission control: JWT specifies exactly what actions are allowed at this step
Previous JWT revoked: When issuing new step JWT, revoke the previous one

Example signup flow:

Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
Step 3 (password) → revoke tempJwt, create user, issue userJwt

This prevents attackers from skipping steps or reusing old JWTs from previous steps.

Content Block Pattern (codingSessionContent)

Unified content block model for rendering mixed content (text + attachments) in display order.

displayIndex: Controls rendering order for all content blocks
Text blocks: contentType = prompt/response/note, text populated, codingSessionAttachmentPkId = NULL
Attachment blocks: contentType = attachment, codingSessionAttachmentPkId references image, text = optional caption
Single query: ORDER BY displayIndex ASC returns all blocks in correct display order
Flexible composition: Prompts, responses, notes, and images can be interspersed in any order

This avoids maintaining separate ordering systems for different content types.

MariaDB TEXT Type

MariaDB supports TEXT(M) where M is maximum column length in characters:

TEXT(500) is valid syntax for text limited to 500 characters
Use TEXT without size for up to 65,535 characters
Use VARCHAR(M) when you need exact length enforcement with better performance

Key Architectural Decisions

Foreign Key Strategy: PkId vs Natural Keys

Decision: Use surrogate keys (PkId) for ALL foreign key relationships, never user-generated values.

Pattern: Tables have both surrogate key (userPkId, projectPkId) AND natural key (username, projectId)
Foreign key references: Always use PkId values, never username/projectId
Natural keys: User-facing identifiers with unique indexes, but not used in relationships

Rationale:

Future-proofing: Requirements change; users may eventually want to change usernames or projectIds
Data integrity: Single source of truth; changing username only updates one record, not cascading updates
Zero performance cost: Request-scoped caching (see below) eliminates lookup overhead
Industry standard: Aligns with database best practices and ORM expectations

Example:

// user table has: userPkId (PK), username (unique)
// project table has: projectPkId (PK), projectId (unique), userPkId (FK)
// codingSession table has: codingSessionPkId (PK), projectPkId (FK), userPkId (FK)

When natural keys ARE appropriate:

Point-in-time snapshots: abuseReport.usernames (not a FK, captures what was reported)
Historical/audit data where values shouldn't change

Request-Scoped Caching Architecture

Decision: Load user context and related entities during authentication; cache for request duration.

Backend Pattern:

// Auth middleware (runs once per request)
const user = await loadUser(userPkId);        // Cache user object
const projects = await loadUserProjects(userPkId);  // Cache all user's projects

// Later in request handlers - no additional queries
const username = user.username;               // Already loaded
const project = projectsById.get(projectPkId); // O(1) lookup from cache

Frontend Pattern:

// Cached in state management (Redux/Zustand)
const projectsMap = new Map(projects.map(p => [p.projectId, p]));  // Index by natural key
const projectsByPk = new Map(projects.map(p => [p.projectPkId, p])); // Index by PkId

// Use whichever key is convenient
const project = projectsMap.get(projectId);    // Lookup by natural key
const project2 = projectsByPk.get(projectPkId); // Lookup by surrogate key

Result:

No query overhead for PkId → natural key lookups
Best of both worlds: database uses immutable PkIds, application uses convenient natural keys
Dual indexing in caches provides flexibility

Multi-Step Process Tables (signupInfo, passwordResetInfo)

Decision: Store all user-provided data collected during multi-step processes, including denormalized fields.

Pattern:

Store username, email, password (hashed) as collected during the process
Store userPkId only after it's created (NULL during process)
Store process state: emailConfirmed, attempts counters, emailVerificationId
Data is NOT normalized (fields duplicated from user table) until process completes

Rationale:

Self-contained: All process data in one table, no JOINs needed during flow
Validation: Can check username uniqueness before creating user record
UX: Display username/email in UI without additional queries
Audit trail: Complete record of what user provided and when
Recovery: If process fails, all context exists to resume or debug

Example (signup flow):

Step 1: Create signupInfo record with email, username=NULL
Step 2: User provides username → update signupInfo.username
Step 3: Email verified → signupInfo.emailConfirmed = 1
Step 4: Password set → create user record, set signupInfo.userPkId, mark complete

TypeScript Entity Types: Strict Base Types + Partial

Decision: Generate strict entity types (all fields required except business-critical optionals), use Partial<T> for incomplete data.

Type Generation Rules:

Default: All fields required, even if DB allows NULL
Optional (?): Only fields with clear business reason:
- Conditional features: user.otpSecret (only if 2FA enabled)
- Polymorphic patterns: codingSessionContent.codingSessionAttachmentPkId (only if contentType='attachment')
- No-context scenarios: errorLog.userPkId (errors can occur without user)
- Process state: signupInfo.password (not set until later step)

Usage Pattern:

// Base type: strict, represents complete DB row
type CwcUser = {
  userPkId: number;
  username: string;  // Required in type (even though DB allows NULL)
  email: string;
  // ... all fields required except otpSecret, password
};

// When working with incomplete data
const partialUser: Partial<CwcUser> = { email: 'user@example.com' };

// For specific operations, can compose utility types
type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;

Rationale:

Type accuracy: Base types reflect complete entities from DB
Self-documenting: Code using Partial<T> signals "this is incomplete"
Type safety: When you have full entity, all fields guaranteed present (no optional chaining)
Simplicity: No type duplication; single source of truth per entity
Flexibility: Combine with Omit/Pick for operation-specific types

Trade-offs:

❌ Less granular: Partial<T> makes ALL fields optional, can't express "email required but PkId optional"
❌ Runtime gap: TypeScript won't catch missing critical fields at compile time
✅ Validation layer compensates: Microservices validate before DB operations anyway
✅ Can add operation-specific types later if pain points emerge

TypeScript: Enums vs Union Types

Decision: Use string literal union types, not TypeScript enums.

Pattern:

// ✅ Correct: String literal union type
type CwcLoginType = 'cwc' | 'facebook' | 'google';

// ❌ Avoid: TypeScript enum
enum LoginType { CWC = 'cwc', FACEBOOK = 'facebook', GOOGLE = 'google' }

Rationale:

No runtime code: Union types are pure type annotations; enums generate runtime JavaScript
Template literal compatibility: Union types work seamlessly with TypeScript string manipulation
No import overhead: Don't need to import the enum to use the type
Better type narrowing: Union types integrate better with discriminated unions and type guards
Simpler: Fewer concepts; just use string values directly

TypeScript: Interfaces vs Types for Entities

Decision: Use type for entity definitions, not interface.

Rationale:

Entity types are data shapes, not contracts: Not implementing/extending like OOP classes
Better composition: Types work better with Omit/Pick/Partial utility types
Consistency: Union types (for enum-like fields) use type, keep entities consistent
Concise for unions: If you need discriminated unions later, types work better

Note: Interfaces are still appropriate for:

Class implementations
API contracts meant to be extended
Plugin systems with intentional extensibility

TypeScript: Union Type Naming - Fully Qualified with Context

Decision: Always fully qualify union type names with the entity and field context to prevent ambiguity.

Pattern:

// ❌ Avoid: Generic/ambiguous names
type CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcEntityType = 'project' | 'codingSession' | 'comment';
type CwcServiceName = 'cwc-auth' | 'cwc-api' | 'all' | 'frontend';

// ✅ Correct: Fully qualified with entity context
type CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';
type CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';
type CwcFeatureFlagServiceName = 'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql';

Rationale:

Prevents namespace collisions: Multiple entities may have fields with same names but different contexts
Future-proofing: Allows types to diverge independently as requirements evolve
- Example: CwcCommentEntityType may allow different entities than CwcReactionEntityType in the future
Self-documenting: Name immediately tells you where the type is used
Catches schema differences: Forces you to notice when similar field names have different values
- Discovered errorLog.serviceName and featureFlag.serviceName had different value sets
No assumptions: Don't assume types will remain identical just because they currently share values

Naming Convention:

Format: Cwc{EntityName}{FieldName}
Examples:
- CwcCodingSessionAttachmentMimeType (codingSessionAttachment.mimeType)
- CwcFeatureFlagPlatform (featureFlag.platform)
- CwcErrorLogType (errorLog.errorType)

Exception: Truly shared types across multiple entities

CwcLoginType - Used by both user.loginType and signupInfo.loginType with identical values
Only keep generic names when values are guaranteed to remain synchronized across all uses
When in doubt, qualify it

Polymorphic Field Handling: When multiple entities use the same field name for polymorphic relationships (e.g., entityType in comment, contentReport, reaction), create separate types even if values are currently identical:

// Each gets its own type, allowing independent evolution
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcContentReportEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';

This enables changing allowed values per context without breaking other uses.

Critical Review During Planning

IMPORTANT: When analyzing specifications and schema definitions:

Don't blindly implement - critically review all entity definitions
Flag inconsistencies - typos, missing columns, unclear relationships
Ask clarifying questions - about design intent, especially for non-obvious patterns
Validate relationships - ensure foreign key references make sense (even without FK constraints)
Question unusual patterns - tables with minimal columns, unclear purposes
Do this during planning - before generating any code

Being a professional partner means catching issues early, not discovering them after implementation.

Development Process

Tool, Framework, Version selection

mainstream, widely accepted, and thoroughly tested & proven tools only
the desire is to use the latest stable versions of the various tools

Adopt a "roll-your-own" mentality

we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package

Version 4

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

What is codingwithclaude?

Project name & aliases

In this document and prompts from the developer, all of these names or phrases are assumed to refer to the project:

coding-with-claude-application
codingwithclaude
coding-with-claude
CWC or cwc

Proactive Documentation Philosophy

CRITICAL: This file is a living knowledge base that must be continuously updated.

Every gap discovered during planning or analysis → Document the pattern to prevent future occurrences
Every critical instruction from the developer → Add to relevant sections immediately
Every "I forgot to do X" moment → Create a checklist or rule to prevent repetition
Every architectural pattern learned → Document it for consistency
Every planning session insight → Capture the learning before implementation begins

When to update CLAUDE.md:

DURING planning sessions - This is where most learning happens through analysis, feedback, and corrections
After receiving critical feedback - Document the expectation immediately
After discovering a bug or oversight - Add checks/rules to prevent it
After analysis reveals gaps - Document what to check in the future
When the developer explains "this is how we do X" - Add it to the guide
After implementing a new feature - Capture any additional patterns discovered during execution

File Access Restrictions and Security Boundaries

Claude Code operates under strict file access restrictions to protect sensitive data:

Workspace Boundaries

Claude Code can ONLY access files within the monorepo root: ./coding-with-claude-application
No access to parent directories, system files, or files outside this workspace
This is enforced by Claude Code's security model

Prohibited File Access

Claude Code is explicitly blocked from reading or writing:

Environment files:
- .env files at any location
- .env.* files (e.g., .env.local, .env.production, .env.dev)
- *.env files (e.g., prod.cwc-sql.env, dev.cwc-storage.env, test.cwc-app.env)
- Any variation of environment configuration files
Secret and credential files:
- Any directory named secrets/, secret/, or private/
- Any directory with secret, secrets, or private in its path
- Any file with secret, secrets, private, or credentials in its filename
- Service account JSON files (service-account-*.json)
- Firebase configuration files (google-services.json, GoogleService-Info.plist)
- Any file matching *credentials*.json
Rationale:
- Prevents accidental exposure of API keys, database passwords, and authentication tokens
- Protects production credentials and service account keys
- Reduces risk of sensitive data being included in code examples or logs
- Enforces principle of least privilege

These restrictions are enforced in .claude/settings.json and cannot be overridden during a session.

Git Workflow

The developer handles all git operations manually. Claude should:

Never initiate git commits, pushes, pulls, or any write operations
Only use git for read-only informational purposes (status, diff, log, show)
Not proactively suggest git operations unless explicitly asked

Git write operations are blocked in .claude/settings.json to enforce this workflow.

Architecture Overview

Monorepo Structure (future plan)

root project: /coding-with-claude-application
packages (apps, microservices, utilities):
- cwc-types: shared TypeScript types to be used in all other packages
- cwc-backend-utils: shared Node.js utilities that backend/api packages will consume
- cwc-website: public frontend end web application
- cwc-auth: authentication microservice, providing login, logout, signup, password reset, etc.
- cwc-api: the main data api used by cwc-website to read & write data, enforce auth, role-based access policies, and business rules/logic
- cwc-dashboard: an administrative web dashboard app for site owners to manage the app & data
- cwc-admin-api: the admin and data api used by the cwc-dashboard app
- cwc-database: database scripts to create tables, indexes, views, as well as insert configuration data
- cwc-schema: shared schema management library that may be used by frontend and backend packages
- cwc-sql: the only backend service that interacts directly with the database server, uses schema to dynamically generate sql statements
- cwc-e2e: a set of end-to-end tests

Tech Stack: to be determined as we build each package, update this documentation as we go.

Development Tooling & Infrastructure

Monorepo Management

pnpm v9.x + Turborepo v2.x

pnpm workspaces for package management and dependency resolution
- Configured in pnpm-workspace.yaml
- Packages located in packages/*
- Uses content-addressable storage for disk efficiency
- Strict dependency resolution prevents phantom dependencies
Turborepo for task orchestration and caching
- Configured in turbo.json
- Intelligent parallel execution based on dependency graph
- Local caching for faster rebuilds
- Pipeline tasks: build, dev, test, lint, typecheck

Node.js Version

Node.js 22 LTS (specified in .nvmrc)
Required for all development and production environments
Use nvm for version management

Code Quality Tools

TypeScript v5.4+

Configured in tsconfig.base.json
Strict mode enabled with enhanced type checking
JavaScript explicitly disallowed (allowJs: false)
Monorepo-optimized with composite projects
Individual packages extend base config

ESLint v8.x with TypeScript

Configured in .eslintrc.json
Uses @typescript-eslint/strict ruleset
Enforces explicit function return types
Prohibits any type and non-null assertions
Strict boolean expressions required

Prettier v3.x

Configured in .prettierrc.json
Standards:
- Single quotes
- 2-space indentation
- 100 character line width
- Trailing commas (ES5)
- LF line endings

Root Scripts

Run from monorepo root using pnpm:

pnpm build - Build all packages (parallel, cached)
pnpm dev - Run all packages in dev mode
pnpm test - Run tests across all packages (parallel, cached)
pnpm lint - Lint all packages (parallel, cached)
pnpm typecheck - Type-check all packages (parallel, cached)
pnpm format - Format all files with Prettier
pnpm format:check - Check formatting without changes

Development Workflow

Before starting work:
- Ensure Node 22 is active: nvm use
- Install dependencies: pnpm install
During development:
- Run dev mode: pnpm dev (in specific package or root)
- Format code: pnpm format
Before committing:
- Type-check: pnpm typecheck
- Lint: pnpm lint
- Format check: pnpm format:check
- Run tests: pnpm test

Package Creation Conventions

When creating a new package in the monorepo:

Version Number: Always start new packages at version 1.0.1 (not 0.0.1)
- Example: "version": "1.0.1" in package.json
- This is a project preference for consistency
Package Structure:
- Follow existing package patterns (see cwc-types as reference)
- Include package.json, tsconfig.json extending base config
- Place source files in src/ directory
- Include appropriate build and typecheck scripts
Package Naming:
- Use cwc- prefix for all CWC packages
- Use kebab-case: cwc-types, cwc-backend-utils, etc.

Key Architectural Decisions & Patterns

MariaDb database

Strong Schema Enforcement
Transaction support
Efficient Joins
Data normalization
Sophisticated Querying and Analytics

TypeScript

Strict mode enabled (strict: true)
Shared types in cwc-types package, duplicating types in separate projects leads to inconsistencies, incompatibility, confusion, and extra work.
Never use any - prefer unknown if type is truly unknown
Run typecheck before committing

Cloud-Agnostic Microservices

CWC uses a microservices architecture deployed as Docker containers potentially deployed across multiple datacenters.

Vendor lock-in is a real business risk. Cloud providers can change pricing, deny service access, or deprecate features at any time.
Cloud-agnostic microservices architecture allows switching hosting providers with minimal effort.
Preparation for Scale - can scale by adding infrastructure (more containers, load balancers) rather than rewriting code and specific services can be scaled based on actual load patterns

Environment Configuration

Each package will read configuration values from a .env file, provided at runtime, tailored to the runtime environment

Database Schema Conventions

All database schema changes must follow these strict conventions to ensure consistency.

Schema and File Organization

Schema Name: cwc

Script Locations:

packages/cwc-database/schema-definition/create-tables.sql - All CREATE TABLE statements
packages/cwc-database/schema-definition/create-indexes.sql - All CREATE INDEX statements
packages/cwc-database/schema-definition/create-views.sql - All CREATE VIEW statements
packages/cwc-database/schema-definition/drop-tables.sql - All DROP TABLE statements
packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql - Migration scripts for schema updates

File Organization:

All tables, indexes, and views organized alphabetically by name within their respective files
Schema creation command at top of create-tables.sql (commented): -- CREATE SCHEMA 'cwc';

Foreign Key Constraints

CRITICAL: Do NOT use database-level foreign key constraints.

Foreign key relationships are enforced in application code (microservices)
Database-level FK constraints create performance overhead on large transactional systems
This allows for better scalability and performance at high transaction volumes
Referential integrity is maintained through business logic in the microservices layer

Implications:

Column names like userPkId, projectPkId indicate relationships but have no DB-level enforcement
Drop table scripts can be in any order (no constraint errors)
Migration scripts don't need to handle FK constraint updates
Application code must validate relationships before INSERT/UPDATE operations

Naming Conventions

Table Names:

Always singular (e.g., user not users, project not projects)
camelCase format
No underscores or hyphens
Examples: user, project, codingSession, abuseReport

Column Names:

camelCase format
No underscores or hyphens
Examples: userPkId, createdDate, projectStatus

Index Names:

Unique indexes: ux_{tableName}_{columnName} prefix
- Example: ux_project_projectId, ux_user_email
Non-unique indexes: ix_{tableName}_{columnName} prefix
- Example: ix_project_userPkId, ix_comment_projectPkId

View Names:

End with View suffix
camelCase format
Concise but descriptive of data included
Examples: userProjectView, activeSessionView

Required Columns

Every table MUST include these four columns:

{tableName}PkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
PRIMARY KEY ({tableName}PkId)

Example for project table:

projectPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
-- ... custom columns here ...
PRIMARY KEY (projectPkId)

Enum-Like VARCHAR Fields

Document potential values using inline comments:

userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
projectStatus VARCHAR(25) NULL, -- potential-values: [active, inactive]
loginType VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]

Critical: These comments are used to generate TypeScript types in the cwc-types package.

Table Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`{tableName}`;

CREATE TABLE `cwc`.`{tableName}` (
  `{tableName}PkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  -- ... custom columns ...
  PRIMARY KEY (`{tableName}PkId`)
);

Important:

Do NOT include indexes in CREATE TABLE statements
Indexes are defined separately in create-indexes.sql
Include DROP TABLE IF EXISTS before each CREATE TABLE

Index Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

-- DROP INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

-- DROP INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE UNIQUE INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

Index Guidelines:

Add indexes for all columns used in WHERE clauses
Add indexes for all columns used in JOIN conditions
Include commented DROP INDEX statement on preceding line
Foreign key columns (e.g., userPkId, projectPkId) always need indexes

View Creation Pattern

-- ********************************************************************
-- {viewName} - brief description
-- Additional context about what data this view provides
-- query example: SELECT * FROM {viewName} WHERE condition
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `{viewName}` AS
SELECT
  u.column1,
  u.column2,
  p.column3
FROM `table1` AS u
INNER JOIN `table2` AS p ON p.foreignKey = u.primaryKey;

Migration Scripts

File Naming: packages/cwc-database/schema-changes/YYYY-MM-DD-description.sql

When to Create:

When adding new tables, indexes, or views (also update definition scripts)
When altering existing tables, indexes, or views (also update definition scripts)
Multiple changes on same day go in one dated file

Pattern:

-- ********************************************************************
-- Migration: YYYY-MM-DD - Description of changes
-- ********************************************************************

USE `cwc`;

-- Add new column to existing table
ALTER TABLE `project` ADD COLUMN `newColumn` VARCHAR(100) NULL;

-- Create new table
CREATE TABLE `cwc`.`newTable` (
  -- ... table definition
);

Drop Tables Pattern

-- Drop tables alphabetically
-- No FK constraints used, so order doesn't matter
DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
DROP TABLE IF EXISTS `cwc`.`{tableName2}`;

Organization:

Organized alphabetically by table name
Since no FK constraints are used, drop order is irrelevant

Database Design Patterns

JWT Validation Tables (userJwt, adminUserJwt, tempJwt)

These tables are for JWT validation and revocation only, not for tracking relationships.

Design: Table contains only the JWT ID (userJwtId, adminUserJwtId, tempJwtId) plus standard columns
No foreign keys: No userPkId or adminUserPkId columns needed
JWT Payload: Contains all context (user info, permissions, claims)
Lookup: JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
Revocation: Hard delete the record (or soft delete with enabled=0)

Example flow:

Login → Create userJwt record → Encode JWT with userJwtId + user claims
Request → Decode JWT → Extract userJwtId → Verify record exists in userJwt table
Logout → Delete userJwt record

Multi-Step Process Security (tempJwt)

Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.

Per-step JWTs: Each step issues a new tempJwt with specific allowed actions
Context in payload: JWT contains signupInfoPkId or passwordResetInfoPkId and current step
Permission control: JWT specifies exactly what actions are allowed at this step
Previous JWT revoked: When issuing new step JWT, revoke the previous one

Example signup flow:

Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
Step 3 (password) → revoke tempJwt, create user, issue userJwt

This prevents attackers from skipping steps or reusing old JWTs from previous steps.

Content Block Pattern (codingSessionContent)

Unified content block model for rendering mixed content (text + attachments) in display order.

displayIndex: Controls rendering order for all content blocks
Text blocks: contentType = prompt/response/note, text populated, codingSessionAttachmentPkId = NULL
Attachment blocks: contentType = attachment, codingSessionAttachmentPkId references image, text = optional caption
Single query: ORDER BY displayIndex ASC returns all blocks in correct display order
Flexible composition: Prompts, responses, notes, and images can be interspersed in any order

This avoids maintaining separate ordering systems for different content types.

MariaDB TEXT Type

MariaDB supports TEXT(M) where M is maximum column length in characters:

TEXT(500) is valid syntax for text limited to 500 characters
Use TEXT without size for up to 65,535 characters
Use VARCHAR(M) when you need exact length enforcement with better performance

Key Architectural Decisions

Foreign Key Strategy: PkId vs Natural Keys

Decision: Use surrogate keys (PkId) for ALL foreign key relationships, never user-generated values.

Pattern: Tables have both surrogate key (userPkId, projectPkId) AND natural key (username, projectId)
Foreign key references: Always use PkId values, never username/projectId
Natural keys: User-facing identifiers with unique indexes, but not used in relationships

Rationale:

Future-proofing: Requirements change; users may eventually want to change usernames or projectIds
Data integrity: Single source of truth; changing username only updates one record, not cascading updates
Zero performance cost: Request-scoped caching (see below) eliminates lookup overhead
Industry standard: Aligns with database best practices and ORM expectations

Example:

// user table has: userPkId (PK), username (unique)
// project table has: projectPkId (PK), projectId (unique), userPkId (FK)
// codingSession table has: codingSessionPkId (PK), projectPkId (FK), userPkId (FK)

When natural keys ARE appropriate:

Point-in-time snapshots: abuseReport.usernames (not a FK, captures what was reported)
Historical/audit data where values shouldn't change

Request-Scoped Caching Architecture

Decision: Load user context and related entities during authentication; cache for request duration.

Backend Pattern:

// Auth middleware (runs once per request)
const user = await loadUser(userPkId);        // Cache user object
const projects = await loadUserProjects(userPkId);  // Cache all user's projects

// Later in request handlers - no additional queries
const username = user.username;               // Already loaded
const project = projectsById.get(projectPkId); // O(1) lookup from cache

Frontend Pattern:

// Cached in state management (Redux/Zustand)
const projectsMap = new Map(projects.map(p => [p.projectId, p]));  // Index by natural key
const projectsByPk = new Map(projects.map(p => [p.projectPkId, p])); // Index by PkId

// Use whichever key is convenient
const project = projectsMap.get(projectId);    // Lookup by natural key
const project2 = projectsByPk.get(projectPkId); // Lookup by surrogate key

Result:

No query overhead for PkId → natural key lookups
Best of both worlds: database uses immutable PkIds, application uses convenient natural keys
Dual indexing in caches provides flexibility

Multi-Step Process Tables (signupInfo, passwordResetInfo)

Decision: Store all user-provided data collected during multi-step processes, including denormalized fields.

Pattern:

Store username, email, password (hashed) as collected during the process
Store userPkId only after it's created (NULL during process)
Store process state: emailConfirmed, attempts counters, emailVerificationId
Data is NOT normalized (fields duplicated from user table) until process completes

Rationale:

Self-contained: All process data in one table, no JOINs needed during flow
Validation: Can check username uniqueness before creating user record
UX: Display username/email in UI without additional queries
Audit trail: Complete record of what user provided and when
Recovery: If process fails, all context exists to resume or debug

Example (signup flow):

Step 1: Create signupInfo record with email, username=NULL
Step 2: User provides username → update signupInfo.username
Step 3: Email verified → signupInfo.emailConfirmed = 1
Step 4: Password set → create user record, set signupInfo.userPkId, mark complete

TypeScript Entity Types: Strict Base Types + Partial

Decision: Generate strict entity types (all fields required except business-critical optionals), use Partial<T> for incomplete data.

Type Generation Rules:

Default: All fields required, even if DB allows NULL
Optional (?): Only fields with clear business reason:
- Conditional features: user.otpSecret (only if 2FA enabled)
- Polymorphic patterns: codingSessionContent.codingSessionAttachmentPkId (only if contentType='attachment')
- No-context scenarios: errorLog.userPkId (errors can occur without user)
- Process state: signupInfo.password (not set until later step)

Usage Pattern:

// Base type: strict, represents complete DB row
type CwcUser = {
  userPkId: number;
  username: string;  // Required in type (even though DB allows NULL)
  email: string;
  // ... all fields required except otpSecret, password
};

// When working with incomplete data
const partialUser: Partial<CwcUser> = { email: 'user@example.com' };

// For specific operations, can compose utility types
type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;

Rationale:

Type accuracy: Base types reflect complete entities from DB
Self-documenting: Code using Partial<T> signals "this is incomplete"
Type safety: When you have full entity, all fields guaranteed present (no optional chaining)
Simplicity: No type duplication; single source of truth per entity
Flexibility: Combine with Omit/Pick for operation-specific types

Trade-offs:

❌ Less granular: Partial<T> makes ALL fields optional, can't express "email required but PkId optional"
❌ Runtime gap: TypeScript won't catch missing critical fields at compile time
✅ Validation layer compensates: Microservices validate before DB operations anyway
✅ Can add operation-specific types later if pain points emerge

TypeScript: Enums vs Union Types

Decision: Use string literal union types, not TypeScript enums.

Pattern:

// ✅ Correct: String literal union type
type CwcLoginType = 'cwc' | 'facebook' | 'google';

// ❌ Avoid: TypeScript enum
enum LoginType { CWC = 'cwc', FACEBOOK = 'facebook', GOOGLE = 'google' }

Rationale:

No runtime code: Union types are pure type annotations; enums generate runtime JavaScript
Template literal compatibility: Union types work seamlessly with TypeScript string manipulation
No import overhead: Don't need to import the enum to use the type
Better type narrowing: Union types integrate better with discriminated unions and type guards
Simpler: Fewer concepts; just use string values directly

TypeScript: Interfaces vs Types for Entities

Decision: Use type for entity definitions, not interface.

Rationale:

Entity types are data shapes, not contracts: Not implementing/extending like OOP classes
Better composition: Types work better with Omit/Pick/Partial utility types
Consistency: Union types (for enum-like fields) use type, keep entities consistent
Concise for unions: If you need discriminated unions later, types work better

Note: Interfaces are still appropriate for:

Class implementations
API contracts meant to be extended
Plugin systems with intentional extensibility

TypeScript: Union Type Naming - Fully Qualified with Context

Decision: Always fully qualify union type names with the entity and field context to prevent ambiguity.

Pattern:

// ❌ Avoid: Generic/ambiguous names
type CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcEntityType = 'project' | 'codingSession' | 'comment';
type CwcServiceName = 'cwc-auth' | 'cwc-api' | 'all' | 'frontend';

// ✅ Correct: Fully qualified with entity context
type CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';
type CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';
type CwcFeatureFlagServiceName = 'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql';

Rationale:

Prevents namespace collisions: Multiple entities may have fields with same names but different contexts
Future-proofing: Allows types to diverge independently as requirements evolve
- Example: CwcCommentEntityType may allow different entities than CwcReactionEntityType in the future
Self-documenting: Name immediately tells you where the type is used
Catches schema differences: Forces you to notice when similar field names have different values
- Discovered errorLog.serviceName and featureFlag.serviceName had different value sets
No assumptions: Don't assume types will remain identical just because they currently share values

Naming Convention:

Format: Cwc{EntityName}{FieldName}
Examples:
- CwcCodingSessionAttachmentMimeType (codingSessionAttachment.mimeType)
- CwcFeatureFlagPlatform (featureFlag.platform)
- CwcErrorLogType (errorLog.errorType)

Exception: Truly shared types across multiple entities

CwcLoginType - Used by both user.loginType and signupInfo.loginType with identical values
Only keep generic names when values are guaranteed to remain synchronized across all uses
When in doubt, qualify it

// Each gets its own type, allowing independent evolution
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcContentReportEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';

This enables changing allowed values per context without breaking other uses.

Critical Review During Planning

IMPORTANT: When analyzing specifications and schema definitions:

Don't blindly implement - critically review all entity definitions
Flag inconsistencies - typos, missing columns, unclear relationships
Ask clarifying questions - about design intent, especially for non-obvious patterns
Validate relationships - ensure foreign key references make sense (even without FK constraints)
Question unusual patterns - tables with minimal columns, unclear purposes
Do this during planning - before generating any code

Being a professional partner means catching issues early, not discovering them after implementation.

Development Process

Tool, Framework, Version selection

mainstream, widely accepted, and thoroughly tested & proven tools only
the desire is to use the latest stable versions of the various tools

Adopt a "roll-your-own" mentality

we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package

Version 5 (latest)

CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

Project Overview

What is codingwithclaude?

Project name & aliases

In this document and prompts from the developer, all of these names or phrases are assumed to refer to the project:

coding-with-claude-application
codingwithclaude
coding-with-claude
CWC or cwc

Proactive Documentation Philosophy

CRITICAL: This file is a living knowledge base that must be continuously updated.

Every gap discovered during planning or analysis → Document the pattern to prevent future occurrences
Every critical instruction from the developer → Add to relevant sections immediately
Every "I forgot to do X" moment → Create a checklist or rule to prevent repetition
Every architectural pattern learned → Document it for consistency
Every planning session insight → Capture the learning before implementation begins

When to update CLAUDE.md:

DURING planning sessions - This is where most learning happens through analysis, feedback, and corrections
After receiving critical feedback - Document the expectation immediately
After discovering a bug or oversight - Add checks/rules to prevent it
After analysis reveals gaps - Document what to check in the future
When the developer explains "this is how we do X" - Add it to the guide
After implementing a new feature - Capture any additional patterns discovered during execution

File Access Restrictions and Security Boundaries

Claude Code operates under strict file access restrictions to protect sensitive data:

Workspace Boundaries

Claude Code can ONLY access files within the monorepo root: ./coding-with-claude-application
No access to parent directories, system files, or files outside this workspace
This is enforced by Claude Code's security model

Prohibited File Access

Claude Code is explicitly blocked from reading or writing:

Environment files:
- .env files at any location
- .env.* files (e.g., .env.local, .env.production, .env.dev)
- *.env files (e.g., prod.cwc-sql.env, dev.cwc-storage.env, test.cwc-app.env)
- Any variation of environment configuration files
Secret and credential files:
- Any directory named secrets/, secret/, or private/
- Any directory with secret, secrets, or private in its path
- Any file with secret, secrets, private, or credentials in its filename
- Service account JSON files (service-account-*.json)
- Firebase configuration files (google-services.json, GoogleService-Info.plist)
- Any file matching *credentials*.json
Rationale:
- Prevents accidental exposure of API keys, database passwords, and authentication tokens
- Protects production credentials and service account keys
- Reduces risk of sensitive data being included in code examples or logs
- Enforces principle of least privilege

These restrictions are enforced in .claude/settings.json and cannot be overridden during a session.

Git Workflow

The developer handles all git operations manually. Claude should:

Never initiate git commits, pushes, pulls, or any write operations
Only use git for read-only informational purposes (status, diff, log, show)
Not proactively suggest git operations unless explicitly asked

Git write operations are blocked in .claude/settings.json to enforce this workflow.

Architecture Overview

Monorepo Structure (future plan)

root project: /coding-with-claude-application
packages (apps, microservices, utilities):
- cwc-types: shared TypeScript types to be used in all other packages
- cwc-backend-utils: shared Node.js utilities that backend/api packages will consume
- cwc-website: public frontend end web application
- cwc-auth: authentication microservice, providing login, logout, signup, password reset, etc.
- cwc-api: the main data api used by cwc-website to read & write data, enforce auth, role-based access policies, and business rules/logic
- cwc-dashboard: an administrative web dashboard app for site owners to manage the app & data
- cwc-admin-api: the admin and data api used by the cwc-dashboard app
- cwc-database: database scripts to create tables, indexes, views, as well as insert configuration data
- cwc-schema: shared schema management library that may be used by frontend and backend packages
- cwc-sql: the only backend service that interacts directly with the database server, uses schema to dynamically generate sql statements
- cwc-e2e: a set of end-to-end tests

Tech Stack: to be determined as we build each package, update this documentation as we go.

Development Tooling & Infrastructure

Monorepo Management

pnpm v9.x + Turborepo v2.x

pnpm workspaces for package management and dependency resolution
- Configured in pnpm-workspace.yaml
- Packages located in packages/*
- Uses content-addressable storage for disk efficiency
- Strict dependency resolution prevents phantom dependencies
Turborepo for task orchestration and caching
- Configured in turbo.json
- Intelligent parallel execution based on dependency graph
- Local caching for faster rebuilds
- Pipeline tasks: build, dev, test, lint, typecheck

Node.js Version

Node.js 22 LTS (specified in .nvmrc)
Required for all development and production environments
Use nvm for version management

Code Quality Tools

TypeScript v5.4+

Configured in tsconfig.base.json
Strict mode enabled with enhanced type checking
JavaScript explicitly disallowed (allowJs: false)
Monorepo-optimized with composite projects
Individual packages extend base config

ESLint v8.x with TypeScript

Configured in .eslintrc.json
Uses @typescript-eslint/strict ruleset
Enforces explicit function return types
Prohibits any type and non-null assertions
Strict boolean expressions required

Prettier v3.x

Configured in .prettierrc.json
Standards:
- Single quotes
- 2-space indentation
- 100 character line width
- Trailing commas (ES5)
- LF line endings

Root Scripts

Run from monorepo root using pnpm:

pnpm build - Build all packages (parallel, cached)
pnpm dev - Run all packages in dev mode
pnpm test - Run tests across all packages (parallel, cached)
pnpm lint - Lint all packages (parallel, cached)
pnpm typecheck - Type-check all packages (parallel, cached)
pnpm format - Format all files with Prettier
pnpm format:check - Check formatting without changes

Development Workflow

Before starting work:
- Ensure Node 22 is active: nvm use
- Install dependencies: pnpm install
During development:
- Run dev mode: pnpm dev (in specific package or root)
- Format code: pnpm format
Before committing:
- Type-check: pnpm typecheck
- Lint: pnpm lint
- Format check: pnpm format:check
- Run tests: pnpm test

Package Creation Conventions

When creating a new package in the monorepo:

Version Number: Always start new packages at version 1.0.0 (not 0.0.1)
- Example: "version": "1.0.0" in package.json
- This is a project preference for consistency
Package Structure:
- Follow existing package patterns (see cwc-types as reference)
- Include package.json, tsconfig.json extending base config
- Place source files in src/ directory
- Include appropriate build and typecheck scripts
Package Naming:
- Use cwc- prefix for all CWC packages
- Use kebab-case: cwc-types, cwc-backend-utils, etc.

Key Architectural Decisions & Patterns

MariaDb database

Strong Schema Enforcement
Transaction support
Efficient Joins
Data normalization
Sophisticated Querying and Analytics

TypeScript

Strict mode enabled (strict: true)
Shared types in cwc-types package, duplicating types in separate projects leads to inconsistencies, incompatibility, confusion, and extra work.
Never use any - prefer unknown if type is truly unknown
Run typecheck before committing

Cloud-Agnostic Microservices

CWC uses a microservices architecture deployed as Docker containers potentially deployed across multiple datacenters.

Vendor lock-in is a real business risk. Cloud providers can change pricing, deny service access, or deprecate features at any time.
Cloud-agnostic microservices architecture allows switching hosting providers with minimal effort.
Preparation for Scale - can scale by adding infrastructure (more containers, load balancers) rather than rewriting code and specific services can be scaled based on actual load patterns

Environment Configuration

Each package will read configuration values from a .env file, provided at runtime, tailored to the runtime environment

Database Schema Conventions

All database schema changes must follow these strict conventions to ensure consistency.

Schema and File Organization

Schema Name: cwc

Script Locations:

packages/cwc-database/schema-definition/create-tables.sql - All CREATE TABLE statements
packages/cwc-database/schema-definition/create-indexes.sql - All CREATE INDEX statements
packages/cwc-database/schema-definition/create-views.sql - All CREATE VIEW statements
packages/cwc-database/schema-definition/drop-tables.sql - All DROP TABLE statements
packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql - Migration scripts for schema updates

File Organization:

All tables, indexes, and views organized alphabetically by name within their respective files
Schema creation command at top of create-tables.sql (commented): -- CREATE SCHEMA 'cwc';

Foreign Key Constraints

CRITICAL: Do NOT use database-level foreign key constraints.

Foreign key relationships are enforced in application code (microservices)
Database-level FK constraints create performance overhead on large transactional systems
This allows for better scalability and performance at high transaction volumes
Referential integrity is maintained through business logic in the microservices layer

Implications:

Column names like userPkId, projectPkId indicate relationships but have no DB-level enforcement
Drop table scripts can be in any order (no constraint errors)
Migration scripts don't need to handle FK constraint updates
Application code must validate relationships before INSERT/UPDATE operations

Naming Conventions

Table Names:

Always singular (e.g., user not users, project not projects)
camelCase format
No underscores or hyphens
Examples: user, project, codingSession, abuseReport

Column Names:

camelCase format
No underscores or hyphens
Examples: userPkId, createdDate, projectStatus

Index Names:

Unique indexes: ux_{tableName}_{columnName} prefix
- Example: ux_project_projectId, ux_user_email
Non-unique indexes: ix_{tableName}_{columnName} prefix
- Example: ix_project_userPkId, ix_comment_projectPkId

View Names:

End with View suffix
camelCase format
Concise but descriptive of data included
Examples: userProjectView, activeSessionView

Required Columns

Every table MUST include these four columns:

{tableName}PkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
PRIMARY KEY ({tableName}PkId)

Example for project table:

projectPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
-- ... custom columns here ...
PRIMARY KEY (projectPkId)

Enum-Like VARCHAR Fields

Document potential values using inline comments:

userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
projectStatus VARCHAR(25) NULL, -- potential-values: [active, inactive]
loginType VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]

Critical: These comments are used to generate TypeScript types in the cwc-types package.

Table Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`{tableName}`;

CREATE TABLE `cwc`.`{tableName}` (
  `{tableName}PkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  -- ... custom columns ...
  PRIMARY KEY (`{tableName}PkId`)
);

Important:

Do NOT include indexes in CREATE TABLE statements
Indexes are defined separately in create-indexes.sql
Include DROP TABLE IF EXISTS before each CREATE TABLE

Index Creation Pattern

-- *************************************************
-- {tableName}
-- *************************************************

-- DROP INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE INDEX `ix_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

-- DROP INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}`;
CREATE UNIQUE INDEX `ux_{tableName}_{columnName}` ON `cwc`.`{tableName}` (`{columnName}` ASC) VISIBLE;

Index Guidelines:

Add indexes for all columns used in WHERE clauses
Add indexes for all columns used in JOIN conditions
Include commented DROP INDEX statement on preceding line
Foreign key columns (e.g., userPkId, projectPkId) always need indexes

View Creation Pattern

-- ********************************************************************
-- {viewName} - brief description
-- Additional context about what data this view provides
-- query example: SELECT * FROM {viewName} WHERE condition
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `{viewName}` AS
SELECT
  u.column1,
  u.column2,
  p.column3
FROM `table1` AS u
INNER JOIN `table2` AS p ON p.foreignKey = u.primaryKey;

Migration Scripts

File Naming: packages/cwc-database/schema-changes/YYYY-MM-DD-description.sql

When to Create:

When adding new tables, indexes, or views (also update definition scripts)
When altering existing tables, indexes, or views (also update definition scripts)
Multiple changes on same day go in one dated file

Pattern:

-- ********************************************************************
-- Migration: YYYY-MM-DD - Description of changes
-- ********************************************************************

USE `cwc`;

-- Add new column to existing table
ALTER TABLE `project` ADD COLUMN `newColumn` VARCHAR(100) NULL;

-- Create new table
CREATE TABLE `cwc`.`newTable` (
  -- ... table definition
);

Drop Tables Pattern

-- Drop tables alphabetically
-- No FK constraints used, so order doesn't matter
DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
DROP TABLE IF EXISTS `cwc`.`{tableName2}`;

Organization:

Organized alphabetically by table name
Since no FK constraints are used, drop order is irrelevant

Database Design Patterns

JWT Validation Tables (userJwt, adminUserJwt, tempJwt)

These tables are for JWT validation and revocation only, not for tracking relationships.

Design: Table contains only the JWT ID (userJwtId, adminUserJwtId, tempJwtId) plus standard columns
No foreign keys: No userPkId or adminUserPkId columns needed
JWT Payload: Contains all context (user info, permissions, claims)
Lookup: JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
Revocation: Hard delete the record (or soft delete with enabled=0)

Example flow:

Login → Create userJwt record → Encode JWT with userJwtId + user claims
Request → Decode JWT → Extract userJwtId → Verify record exists in userJwt table
Logout → Delete userJwt record

Multi-Step Process Security (tempJwt)

Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.

Per-step JWTs: Each step issues a new tempJwt with specific allowed actions
Context in payload: JWT contains signupInfoPkId or passwordResetInfoPkId and current step
Permission control: JWT specifies exactly what actions are allowed at this step
Previous JWT revoked: When issuing new step JWT, revoke the previous one

Example signup flow:

Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
Step 3 (password) → revoke tempJwt, create user, issue userJwt

This prevents attackers from skipping steps or reusing old JWTs from previous steps.

Content Block Pattern (codingSessionContent)

Unified content block model for rendering mixed content (text + attachments) in display order.

displayIndex: Controls rendering order for all content blocks
Text blocks: contentType = prompt/response/note, text populated, codingSessionAttachmentPkId = NULL
Attachment blocks: contentType = attachment, codingSessionAttachmentPkId references image, text = optional caption
Single query: ORDER BY displayIndex ASC returns all blocks in correct display order
Flexible composition: Prompts, responses, notes, and images can be interspersed in any order

This avoids maintaining separate ordering systems for different content types.

MariaDB TEXT Type

MariaDB supports TEXT(M) where M is maximum column length in characters:

TEXT(500) is valid syntax for text limited to 500 characters
Use TEXT without size for up to 65,535 characters
Use VARCHAR(M) when you need exact length enforcement with better performance

Key Architectural Decisions

Foreign Key Strategy: PkId vs Natural Keys

Decision: Use surrogate keys (PkId) for ALL foreign key relationships, never user-generated values.

Pattern: Tables have both surrogate key (userPkId, projectPkId) AND natural key (username, projectId)
Foreign key references: Always use PkId values, never username/projectId
Natural keys: User-facing identifiers with unique indexes, but not used in relationships

Rationale:

Future-proofing: Requirements change; users may eventually want to change usernames or projectIds
Data integrity: Single source of truth; changing username only updates one record, not cascading updates
Zero performance cost: Request-scoped caching (see below) eliminates lookup overhead
Industry standard: Aligns with database best practices and ORM expectations

Example:

// user table has: userPkId (PK), username (unique)
// project table has: projectPkId (PK), projectId (unique), userPkId (FK)
// codingSession table has: codingSessionPkId (PK), projectPkId (FK), userPkId (FK)

When natural keys ARE appropriate:

Point-in-time snapshots: abuseReport.usernames (not a FK, captures what was reported)
Historical/audit data where values shouldn't change

Request-Scoped Caching Architecture

Decision: Load user context and related entities during authentication; cache for request duration.

Backend Pattern:

// Auth middleware (runs once per request)
const user = await loadUser(userPkId);        // Cache user object
const projects = await loadUserProjects(userPkId);  // Cache all user's projects

// Later in request handlers - no additional queries
const username = user.username;               // Already loaded
const project = projectsById.get(projectPkId); // O(1) lookup from cache

Frontend Pattern:

// Cached in state management (Redux/Zustand)
const projectsMap = new Map(projects.map(p => [p.projectId, p]));  // Index by natural key
const projectsByPk = new Map(projects.map(p => [p.projectPkId, p])); // Index by PkId

// Use whichever key is convenient
const project = projectsMap.get(projectId);    // Lookup by natural key
const project2 = projectsByPk.get(projectPkId); // Lookup by surrogate key

Result:

No query overhead for PkId → natural key lookups
Best of both worlds: database uses immutable PkIds, application uses convenient natural keys
Dual indexing in caches provides flexibility

Multi-Step Process Tables (signupInfo, passwordResetInfo)

Decision: Store all user-provided data collected during multi-step processes, including denormalized fields.

Pattern:

Store username, email, password (hashed) as collected during the process
Store userPkId only after it's created (NULL during process)
Store process state: emailConfirmed, attempts counters, emailVerificationId
Data is NOT normalized (fields duplicated from user table) until process completes

Rationale:

Self-contained: All process data in one table, no JOINs needed during flow
Validation: Can check username uniqueness before creating user record
UX: Display username/email in UI without additional queries
Audit trail: Complete record of what user provided and when
Recovery: If process fails, all context exists to resume or debug

Example (signup flow):

Step 1: Create signupInfo record with email, username=NULL
Step 2: User provides username → update signupInfo.username
Step 3: Email verified → signupInfo.emailConfirmed = 1
Step 4: Password set → create user record, set signupInfo.userPkId, mark complete

TypeScript Entity Types: Strict Base Types + Partial

Decision: Generate strict entity types (all fields required except business-critical optionals), use Partial<T> for incomplete data.

Type Generation Rules:

Default: All fields required, even if DB allows NULL
Optional (?): Only fields with clear business reason:
- Conditional features: user.otpSecret (only if 2FA enabled)
- Polymorphic patterns: codingSessionContent.codingSessionAttachmentPkId (only if contentType='attachment')
- No-context scenarios: errorLog.userPkId (errors can occur without user)
- Process state: signupInfo.password (not set until later step)

Usage Pattern:

// Base type: strict, represents complete DB row
type CwcUser = {
  userPkId: number;
  username: string;  // Required in type (even though DB allows NULL)
  email: string;
  // ... all fields required except otpSecret, password
};

// When working with incomplete data
const partialUser: Partial<CwcUser> = { email: 'user@example.com' };

// For specific operations, can compose utility types
type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;

Rationale:

Type accuracy: Base types reflect complete entities from DB
Self-documenting: Code using Partial<T> signals "this is incomplete"
Type safety: When you have full entity, all fields guaranteed present (no optional chaining)
Simplicity: No type duplication; single source of truth per entity
Flexibility: Combine with Omit/Pick for operation-specific types

Trade-offs:

❌ Less granular: Partial<T> makes ALL fields optional, can't express "email required but PkId optional"
❌ Runtime gap: TypeScript won't catch missing critical fields at compile time
✅ Validation layer compensates: Microservices validate before DB operations anyway
✅ Can add operation-specific types later if pain points emerge

TypeScript: Enums vs Union Types

Decision: Use string literal union types, not TypeScript enums.

Pattern:

// ✅ Correct: String literal union type
type CwcLoginType = 'cwc' | 'facebook' | 'google';

// ❌ Avoid: TypeScript enum
enum LoginType { CWC = 'cwc', FACEBOOK = 'facebook', GOOGLE = 'google' }

Rationale:

No runtime code: Union types are pure type annotations; enums generate runtime JavaScript
Template literal compatibility: Union types work seamlessly with TypeScript string manipulation
No import overhead: Don't need to import the enum to use the type
Better type narrowing: Union types integrate better with discriminated unions and type guards
Simpler: Fewer concepts; just use string values directly

TypeScript: Interfaces vs Types for Entities

Decision: Use type for entity definitions, not interface.

Rationale:

Entity types are data shapes, not contracts: Not implementing/extending like OOP classes
Better composition: Types work better with Omit/Pick/Partial utility types
Consistency: Union types (for enum-like fields) use type, keep entities consistent
Concise for unions: If you need discriminated unions later, types work better

Note: Interfaces are still appropriate for:

Class implementations
API contracts meant to be extended
Plugin systems with intentional extensibility

TypeScript: Union Type Naming - Fully Qualified with Context

Decision: Always fully qualify union type names with the entity and field context to prevent ambiguity.

Pattern:

// ❌ Avoid: Generic/ambiguous names
type CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcEntityType = 'project' | 'codingSession' | 'comment';
type CwcServiceName = 'cwc-auth' | 'cwc-api' | 'all' | 'frontend';

// ✅ Correct: Fully qualified with entity context
type CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';
type CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';
type CwcFeatureFlagServiceName = 'all' | 'frontend' | 'backend' | 'cwc-app' | 'cwc-sql';

Rationale:

Prevents namespace collisions: Multiple entities may have fields with same names but different contexts
Future-proofing: Allows types to diverge independently as requirements evolve
- Example: CwcCommentEntityType may allow different entities than CwcReactionEntityType in the future
Self-documenting: Name immediately tells you where the type is used
Catches schema differences: Forces you to notice when similar field names have different values
- Discovered errorLog.serviceName and featureFlag.serviceName had different value sets
No assumptions: Don't assume types will remain identical just because they currently share values

Naming Convention:

Format: Cwc{EntityName}{FieldName}
Examples:
- CwcCodingSessionAttachmentMimeType (codingSessionAttachment.mimeType)
- CwcFeatureFlagPlatform (featureFlag.platform)
- CwcErrorLogType (errorLog.errorType)

Exception: Truly shared types across multiple entities

CwcLoginType - Used by both user.loginType and signupInfo.loginType with identical values
Only keep generic names when values are guaranteed to remain synchronized across all uses
When in doubt, qualify it

// Each gets its own type, allowing independent evolution
type CwcCommentEntityType = 'project' | 'codingSession' | 'comment';
type CwcContentReportEntityType = 'project' | 'codingSession' | 'comment';
type CwcReactionEntityType = 'project' | 'codingSession' | 'comment';

This enables changing allowed values per context without breaking other uses.

Critical Review During Planning

IMPORTANT: When analyzing specifications and schema definitions:

Don't blindly implement - critically review all entity definitions
Flag inconsistencies - typos, missing columns, unclear relationships
Ask clarifying questions - about design intent, especially for non-obvious patterns
Validate relationships - ensure foreign key references make sense (even without FK constraints)
Question unusual patterns - tables with minimal columns, unclear purposes
Do this during planning - before generating any code

Being a professional partner means catching issues early, not discovering them after implementation.

Development Process

Tool, Framework, Version selection

mainstream, widely accepted, and thoroughly tested & proven tools only
the desire is to use the latest stable versions of the various tools

Adopt a "roll-your-own" mentality

we want to minimize the number of unneccesary dependencies to avoid headaches when upgrading our core tech stack
when it makes sense, we will build our own components and utilities rather than relying on a 3rd party package

packages/cwc-database/schema-changes/2025-01-18-add-username-to-signup-password-reset.sql

-- ********************************************************************
-- Migration: 2025-01-18 - Add username column to signupInfo and passwordResetInfo
--
-- Rationale:
-- - Username is collected during signup and password reset flows
-- - Storing it enables uniqueness validation before user creation
-- - Useful for UX (display username in UI) and audit trails
-- - Avoids JOIN to user table which may not exist yet (signup) or
--   isn't needed (password reset is email-based)
-- ********************************************************************

USE `cwc`;

-- Add username column to passwordResetInfo
ALTER TABLE `cwc`.`passwordResetInfo`
  ADD COLUMN `username` VARCHAR(100) NULL AFTER `userPkId`;

-- Add username column to signupInfo
ALTER TABLE `cwc`.`signupInfo`
  ADD COLUMN `username` VARCHAR(100) NULL AFTER `userPkId`;

-- Create index on passwordResetInfo.username
CREATE INDEX `ix_passwordResetInfo_username` ON `cwc`.`passwordResetInfo` (`username` ASC) VISIBLE;

-- Create index on signupInfo.username
CREATE INDEX `ix_signupInfo_username` ON `cwc`.`signupInfo` (`username` ASC) VISIBLE;

packages/cwc-database/schema-definition/create-indexes.sql

2 versions

Version 1

-- ********************************************************************
-- CWC Database Schema - Index Definitions
-- ********************************************************************

USE `cwc`;

-- *************************************************
-- abuseReport
-- *************************************************

-- DROP INDEX `ix_abuseReport_userPkId` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_userPkId` ON `cwc`.`abuseReport` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_abuseReport_projectPkId` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_projectPkId` ON `cwc`.`abuseReport` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_abuseReport_status` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_status` ON `cwc`.`abuseReport` (`status` ASC) VISIBLE;

-- *************************************************
-- adminUser
-- *************************************************

-- DROP INDEX `ux_adminUser_adminUsername` ON `cwc`.`adminUser`;
CREATE UNIQUE INDEX `ux_adminUser_adminUsername` ON `cwc`.`adminUser` (`adminUsername` ASC) VISIBLE;

-- *************************************************
-- adminUserJwt
-- *************************************************

-- DROP INDEX `ux_adminUserJwt_adminUserJwtId` ON `cwc`.`adminUserJwt`;
CREATE UNIQUE INDEX `ux_adminUserJwt_adminUserJwtId` ON `cwc`.`adminUserJwt` (`adminUserJwtId` ASC) VISIBLE;

-- *************************************************
-- codingSession
-- *************************************************

-- DROP INDEX `ix_codingSession_userPkId` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_userPkId` ON `cwc`.`codingSession` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSession_projectPkId` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_projectPkId` ON `cwc`.`codingSession` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSession_published` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_published` ON `cwc`.`codingSession` (`published` ASC) VISIBLE;

-- *************************************************
-- codingSessionAttachment
-- *************************************************

-- DROP INDEX `ix_codingSessionAttachment_userPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_userPkId` ON `cwc`.`codingSessionAttachment` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionAttachment_projectPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_projectPkId` ON `cwc`.`codingSessionAttachment` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionAttachment_codingSessionPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_codingSessionPkId` ON `cwc`.`codingSessionAttachment` (`codingSessionPkId` ASC) VISIBLE;

-- *************************************************
-- codingSessionContent
-- *************************************************

-- DROP INDEX `ix_codingSessionContent_userPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_userPkId` ON `cwc`.`codingSessionContent` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_projectPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_projectPkId` ON `cwc`.`codingSessionContent` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_codingSessionPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_codingSessionPkId` ON `cwc`.`codingSessionContent` (`codingSessionPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_codingSessionAttachmentPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_codingSessionAttachmentPkId` ON `cwc`.`codingSessionContent` (`codingSessionAttachmentPkId` ASC) VISIBLE;

-- *************************************************
-- comment
-- *************************************************

-- DROP INDEX `ix_comment_projectPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_projectPkId` ON `cwc`.`comment` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_userPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_userPkId` ON `cwc`.`comment` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_entityPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_entityPkId` ON `cwc`.`comment` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_entityType` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_entityType` ON `cwc`.`comment` (`entityType` ASC) VISIBLE;

-- *************************************************
-- contentReport
-- *************************************************

-- DROP INDEX `ix_contentReport_userPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_userPkId` ON `cwc`.`contentReport` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_projectPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_projectPkId` ON `cwc`.`contentReport` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_entityPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_entityPkId` ON `cwc`.`contentReport` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_status` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_status` ON `cwc`.`contentReport` (`status` ASC) VISIBLE;

-- *************************************************
-- emailVerify
-- *************************************************

-- DROP INDEX `ux_emailVerify_emailVerificationId` ON `cwc`.`emailVerify`;
CREATE UNIQUE INDEX `ux_emailVerify_emailVerificationId` ON `cwc`.`emailVerify` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_emailVerify_email` ON `cwc`.`emailVerify`;
CREATE INDEX `ix_emailVerify_email` ON `cwc`.`emailVerify` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_emailVerify_deviceId` ON `cwc`.`emailVerify`;
CREATE INDEX `ix_emailVerify_deviceId` ON `cwc`.`emailVerify` (`deviceId` ASC) VISIBLE;

-- *************************************************
-- errorLog
-- *************************************************

-- DROP INDEX `ix_errorLog_userPkId` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_userPkId` ON `cwc`.`errorLog` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_projectPkId` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_projectPkId` ON `cwc`.`errorLog` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_serviceName` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_serviceName` ON `cwc`.`errorLog` (`serviceName` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_dismissed` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_dismissed` ON `cwc`.`errorLog` (`dismissed` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_errorType` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_errorType` ON `cwc`.`errorLog` (`errorType` ASC) VISIBLE;

-- *************************************************
-- featureFlag
-- *************************************************

-- DROP INDEX `ux_featureFlag_featureFlagId` ON `cwc`.`featureFlag`;
CREATE UNIQUE INDEX `ux_featureFlag_featureFlagId` ON `cwc`.`featureFlag` (`featureFlagId` ASC) VISIBLE;

-- DROP INDEX `ix_featureFlag_serviceName` ON `cwc`.`featureFlag`;
CREATE INDEX `ix_featureFlag_serviceName` ON `cwc`.`featureFlag` (`serviceName` ASC) VISIBLE;

-- DROP INDEX `ix_featureFlag_platform` ON `cwc`.`featureFlag`;
CREATE INDEX `ix_featureFlag_platform` ON `cwc`.`featureFlag` (`platform` ASC) VISIBLE;

-- *************************************************
-- passwordResetInfo
-- *************************************************

-- DROP INDEX `ix_passwordResetInfo_userPkId` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_userPkId` ON `cwc`.`passwordResetInfo` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_passwordResetInfo_emailVerificationId` ON `cwc`.`passwordResetInfo`;
CREATE UNIQUE INDEX `ux_passwordResetInfo_emailVerificationId` ON `cwc`.`passwordResetInfo` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_passwordResetInfo_email` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_email` ON `cwc`.`passwordResetInfo` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_passwordResetInfo_deviceId` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_deviceId` ON `cwc`.`passwordResetInfo` (`deviceId` ASC) VISIBLE;

-- *************************************************
-- project
-- *************************************************

-- DROP INDEX `ix_project_userPkId` ON `cwc`.`project`;
CREATE INDEX `ix_project_userPkId` ON `cwc`.`project` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_project_projectId` ON `cwc`.`project`;
CREATE UNIQUE INDEX `ux_project_projectId` ON `cwc`.`project` (`projectId` ASC) VISIBLE;

-- *************************************************
-- reaction
-- *************************************************

-- DROP INDEX `ix_reaction_projectPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_projectPkId` ON `cwc`.`reaction` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_userPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_userPkId` ON `cwc`.`reaction` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_entityPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_entityPkId` ON `cwc`.`reaction` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_entityType` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_entityType` ON `cwc`.`reaction` (`entityType` ASC) VISIBLE;

-- *************************************************
-- signupInfo
-- *************************************************

-- DROP INDEX `ix_signupInfo_userPkId` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_userPkId` ON `cwc`.`signupInfo` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_signupInfo_emailVerificationId` ON `cwc`.`signupInfo`;
CREATE UNIQUE INDEX `ux_signupInfo_emailVerificationId` ON `cwc`.`signupInfo` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_signupInfo_email` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_email` ON `cwc`.`signupInfo` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_signupInfo_deviceId` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_deviceId` ON `cwc`.`signupInfo` (`deviceId` ASC) VISIBLE;

-- *************************************************
-- tempJwt
-- *************************************************

-- DROP INDEX `ux_tempJwt_tempJwtId` ON `cwc`.`tempJwt`;
CREATE UNIQUE INDEX `ux_tempJwt_tempJwtId` ON `cwc`.`tempJwt` (`tempJwtId` ASC) VISIBLE;

-- *************************************************
-- user
-- *************************************************

-- DROP INDEX `ux_user_username` ON `cwc`.`user`;
CREATE UNIQUE INDEX `ux_user_username` ON `cwc`.`user` (`username` ASC) VISIBLE;

-- DROP INDEX `ux_user_email` ON `cwc`.`user`;
CREATE UNIQUE INDEX `ux_user_email` ON `cwc`.`user` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_user_deviceId` ON `cwc`.`user`;
CREATE INDEX `ix_user_deviceId` ON `cwc`.`user` (`deviceId` ASC) VISIBLE;

-- DROP INDEX `ix_user_loginType` ON `cwc`.`user`;
CREATE INDEX `ix_user_loginType` ON `cwc`.`user` (`loginType` ASC) VISIBLE;

-- *************************************************
-- userJwt
-- *************************************************

-- DROP INDEX `ux_userJwt_userJwtId` ON `cwc`.`userJwt`;
CREATE UNIQUE INDEX `ux_userJwt_userJwtId` ON `cwc`.`userJwt` (`userJwtId` ASC) VISIBLE;

Version 2 (latest)

-- ********************************************************************
-- CWC Database Schema - Index Definitions
-- ********************************************************************

USE `cwc`;

-- *************************************************
-- abuseReport
-- *************************************************

-- DROP INDEX `ix_abuseReport_userPkId` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_userPkId` ON `cwc`.`abuseReport` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_abuseReport_projectPkId` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_projectPkId` ON `cwc`.`abuseReport` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_abuseReport_status` ON `cwc`.`abuseReport`;
CREATE INDEX `ix_abuseReport_status` ON `cwc`.`abuseReport` (`status` ASC) VISIBLE;

-- *************************************************
-- adminUser
-- *************************************************

-- DROP INDEX `ux_adminUser_adminUsername` ON `cwc`.`adminUser`;
CREATE UNIQUE INDEX `ux_adminUser_adminUsername` ON `cwc`.`adminUser` (`adminUsername` ASC) VISIBLE;

-- *************************************************
-- adminUserJwt
-- *************************************************

-- DROP INDEX `ux_adminUserJwt_adminUserJwtId` ON `cwc`.`adminUserJwt`;
CREATE UNIQUE INDEX `ux_adminUserJwt_adminUserJwtId` ON `cwc`.`adminUserJwt` (`adminUserJwtId` ASC) VISIBLE;

-- *************************************************
-- codingSession
-- *************************************************

-- DROP INDEX `ix_codingSession_userPkId` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_userPkId` ON `cwc`.`codingSession` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSession_projectPkId` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_projectPkId` ON `cwc`.`codingSession` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSession_published` ON `cwc`.`codingSession`;
CREATE INDEX `ix_codingSession_published` ON `cwc`.`codingSession` (`published` ASC) VISIBLE;

-- *************************************************
-- codingSessionAttachment
-- *************************************************

-- DROP INDEX `ix_codingSessionAttachment_userPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_userPkId` ON `cwc`.`codingSessionAttachment` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionAttachment_projectPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_projectPkId` ON `cwc`.`codingSessionAttachment` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionAttachment_codingSessionPkId` ON `cwc`.`codingSessionAttachment`;
CREATE INDEX `ix_codingSessionAttachment_codingSessionPkId` ON `cwc`.`codingSessionAttachment` (`codingSessionPkId` ASC) VISIBLE;

-- *************************************************
-- codingSessionContent
-- *************************************************

-- DROP INDEX `ix_codingSessionContent_userPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_userPkId` ON `cwc`.`codingSessionContent` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_projectPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_projectPkId` ON `cwc`.`codingSessionContent` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_codingSessionPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_codingSessionPkId` ON `cwc`.`codingSessionContent` (`codingSessionPkId` ASC) VISIBLE;

-- DROP INDEX `ix_codingSessionContent_codingSessionAttachmentPkId` ON `cwc`.`codingSessionContent`;
CREATE INDEX `ix_codingSessionContent_codingSessionAttachmentPkId` ON `cwc`.`codingSessionContent` (`codingSessionAttachmentPkId` ASC) VISIBLE;

-- *************************************************
-- comment
-- *************************************************

-- DROP INDEX `ix_comment_projectPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_projectPkId` ON `cwc`.`comment` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_userPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_userPkId` ON `cwc`.`comment` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_entityPkId` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_entityPkId` ON `cwc`.`comment` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_comment_entityType` ON `cwc`.`comment`;
CREATE INDEX `ix_comment_entityType` ON `cwc`.`comment` (`entityType` ASC) VISIBLE;

-- *************************************************
-- contentReport
-- *************************************************

-- DROP INDEX `ix_contentReport_userPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_userPkId` ON `cwc`.`contentReport` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_projectPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_projectPkId` ON `cwc`.`contentReport` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_entityPkId` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_entityPkId` ON `cwc`.`contentReport` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_contentReport_status` ON `cwc`.`contentReport`;
CREATE INDEX `ix_contentReport_status` ON `cwc`.`contentReport` (`status` ASC) VISIBLE;

-- *************************************************
-- emailVerify
-- *************************************************

-- DROP INDEX `ux_emailVerify_emailVerificationId` ON `cwc`.`emailVerify`;
CREATE UNIQUE INDEX `ux_emailVerify_emailVerificationId` ON `cwc`.`emailVerify` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_emailVerify_email` ON `cwc`.`emailVerify`;
CREATE INDEX `ix_emailVerify_email` ON `cwc`.`emailVerify` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_emailVerify_deviceId` ON `cwc`.`emailVerify`;
CREATE INDEX `ix_emailVerify_deviceId` ON `cwc`.`emailVerify` (`deviceId` ASC) VISIBLE;

-- *************************************************
-- errorLog
-- *************************************************

-- DROP INDEX `ix_errorLog_userPkId` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_userPkId` ON `cwc`.`errorLog` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_projectPkId` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_projectPkId` ON `cwc`.`errorLog` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_serviceName` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_serviceName` ON `cwc`.`errorLog` (`serviceName` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_dismissed` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_dismissed` ON `cwc`.`errorLog` (`dismissed` ASC) VISIBLE;

-- DROP INDEX `ix_errorLog_errorType` ON `cwc`.`errorLog`;
CREATE INDEX `ix_errorLog_errorType` ON `cwc`.`errorLog` (`errorType` ASC) VISIBLE;

-- *************************************************
-- featureFlag
-- *************************************************

-- DROP INDEX `ux_featureFlag_featureFlagId` ON `cwc`.`featureFlag`;
CREATE UNIQUE INDEX `ux_featureFlag_featureFlagId` ON `cwc`.`featureFlag` (`featureFlagId` ASC) VISIBLE;

-- DROP INDEX `ix_featureFlag_serviceName` ON `cwc`.`featureFlag`;
CREATE INDEX `ix_featureFlag_serviceName` ON `cwc`.`featureFlag` (`serviceName` ASC) VISIBLE;

-- DROP INDEX `ix_featureFlag_platform` ON `cwc`.`featureFlag`;
CREATE INDEX `ix_featureFlag_platform` ON `cwc`.`featureFlag` (`platform` ASC) VISIBLE;

-- *************************************************
-- passwordResetInfo
-- *************************************************

-- DROP INDEX `ix_passwordResetInfo_userPkId` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_userPkId` ON `cwc`.`passwordResetInfo` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_passwordResetInfo_emailVerificationId` ON `cwc`.`passwordResetInfo`;
CREATE UNIQUE INDEX `ux_passwordResetInfo_emailVerificationId` ON `cwc`.`passwordResetInfo` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_passwordResetInfo_email` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_email` ON `cwc`.`passwordResetInfo` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_passwordResetInfo_deviceId` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_deviceId` ON `cwc`.`passwordResetInfo` (`deviceId` ASC) VISIBLE;

-- DROP INDEX `ix_passwordResetInfo_username` ON `cwc`.`passwordResetInfo`;
CREATE INDEX `ix_passwordResetInfo_username` ON `cwc`.`passwordResetInfo` (`username` ASC) VISIBLE;

-- *************************************************
-- project
-- *************************************************

-- DROP INDEX `ix_project_userPkId` ON `cwc`.`project`;
CREATE INDEX `ix_project_userPkId` ON `cwc`.`project` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_project_projectId` ON `cwc`.`project`;
CREATE UNIQUE INDEX `ux_project_projectId` ON `cwc`.`project` (`projectId` ASC) VISIBLE;

-- *************************************************
-- reaction
-- *************************************************

-- DROP INDEX `ix_reaction_projectPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_projectPkId` ON `cwc`.`reaction` (`projectPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_userPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_userPkId` ON `cwc`.`reaction` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_entityPkId` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_entityPkId` ON `cwc`.`reaction` (`entityPkId` ASC) VISIBLE;

-- DROP INDEX `ix_reaction_entityType` ON `cwc`.`reaction`;
CREATE INDEX `ix_reaction_entityType` ON `cwc`.`reaction` (`entityType` ASC) VISIBLE;

-- *************************************************
-- signupInfo
-- *************************************************

-- DROP INDEX `ix_signupInfo_userPkId` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_userPkId` ON `cwc`.`signupInfo` (`userPkId` ASC) VISIBLE;

-- DROP INDEX `ux_signupInfo_emailVerificationId` ON `cwc`.`signupInfo`;
CREATE UNIQUE INDEX `ux_signupInfo_emailVerificationId` ON `cwc`.`signupInfo` (`emailVerificationId` ASC) VISIBLE;

-- DROP INDEX `ix_signupInfo_email` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_email` ON `cwc`.`signupInfo` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_signupInfo_deviceId` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_deviceId` ON `cwc`.`signupInfo` (`deviceId` ASC) VISIBLE;

-- DROP INDEX `ix_signupInfo_username` ON `cwc`.`signupInfo`;
CREATE INDEX `ix_signupInfo_username` ON `cwc`.`signupInfo` (`username` ASC) VISIBLE;

-- *************************************************
-- tempJwt
-- *************************************************

-- DROP INDEX `ux_tempJwt_tempJwtId` ON `cwc`.`tempJwt`;
CREATE UNIQUE INDEX `ux_tempJwt_tempJwtId` ON `cwc`.`tempJwt` (`tempJwtId` ASC) VISIBLE;

-- *************************************************
-- user
-- *************************************************

-- DROP INDEX `ux_user_username` ON `cwc`.`user`;
CREATE UNIQUE INDEX `ux_user_username` ON `cwc`.`user` (`username` ASC) VISIBLE;

-- DROP INDEX `ux_user_email` ON `cwc`.`user`;
CREATE UNIQUE INDEX `ux_user_email` ON `cwc`.`user` (`email` ASC) VISIBLE;

-- DROP INDEX `ix_user_deviceId` ON `cwc`.`user`;
CREATE INDEX `ix_user_deviceId` ON `cwc`.`user` (`deviceId` ASC) VISIBLE;

-- DROP INDEX `ix_user_loginType` ON `cwc`.`user`;
CREATE INDEX `ix_user_loginType` ON `cwc`.`user` (`loginType` ASC) VISIBLE;

-- *************************************************
-- userJwt
-- *************************************************

-- DROP INDEX `ux_userJwt_userJwtId` ON `cwc`.`userJwt`;
CREATE UNIQUE INDEX `ux_userJwt_userJwtId` ON `cwc`.`userJwt` (`userJwtId` ASC) VISIBLE;

packages/cwc-database/schema-definition/create-tables.sql

2 versions

Version 1

-- ********************************************************************
-- CWC Database Schema - Table Definitions
-- ********************************************************************

-- CREATE SCHEMA `cwc`;

USE `cwc`;

-- *************************************************
-- abuseReport
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`abuseReport`;

CREATE TABLE `cwc`.`abuseReport` (
  `abuseReportPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `usernames` VARCHAR(1000) NOT NULL,
  `message` VARCHAR(500) NOT NULL,
  `status` VARCHAR(25) NULL, -- potential-values: [submitted, investigation, dismissed, resolved, retracted]
  PRIMARY KEY (`abuseReportPkId`)
);

-- *************************************************
-- adminUser
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`adminUser`;

CREATE TABLE `cwc`.`adminUser` (
  `adminUserPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `adminUsername` VARCHAR(25) NULL,
  `password` VARCHAR(100) NULL,
  PRIMARY KEY (`adminUserPkId`)
);

-- *************************************************
-- adminUserJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`adminUserJwt`;

CREATE TABLE `cwc`.`adminUserJwt` (
  `adminUserJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `adminUserJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`adminUserJwtPkId`)
);

-- *************************************************
-- codingSession
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSession`;

CREATE TABLE `cwc`.`codingSession` (
  `codingSessionPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `description` TEXT NULL,
  `published` TINYINT NULL,
  PRIMARY KEY (`codingSessionPkId`)
);

-- *************************************************
-- codingSessionAttachment
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;

CREATE TABLE `cwc`.`codingSessionAttachment` (
  `codingSessionAttachmentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `codingSessionPkId` INT NULL,
  `filename` VARCHAR(50) NULL,
  `mimeType` VARCHAR(100) NULL, -- potential-values: [image/png, image/jpg]
  `height` INT NULL,
  `width` INT NULL,
  PRIMARY KEY (`codingSessionAttachmentPkId`)
);

-- *************************************************
-- codingSessionContent
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionContent`;

CREATE TABLE `cwc`.`codingSessionContent` (
  `codingSessionContentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `contentType` VARCHAR(25) NULL, -- potential-values: [prompt, response, note, attachment]
  `codingSessionPkId` INT NULL,
  `codingSessionAttachmentPkId` INT NULL,
  `displayIndex` INT NULL,
  `text` TEXT NULL,
  PRIMARY KEY (`codingSessionContentPkId`)
);

-- *************************************************
-- comment
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`comment`;

CREATE TABLE `cwc`.`comment` (
  `commentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectPkId` INT NULL,
  `userPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `text` TEXT NULL,
  PRIMARY KEY (`commentPkId`)
);

-- *************************************************
-- contentReport
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`contentReport`;

CREATE TABLE `cwc`.`contentReport` (
  `contentReportPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `message` VARCHAR(500) NOT NULL,
  `status` VARCHAR(25) NULL, -- potential-values: [submitted, investigation, dismissed, resolved, retracted]
  PRIMARY KEY (`contentReportPkId`)
);

-- *************************************************
-- emailVerify
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`emailVerify`;

CREATE TABLE `cwc`.`emailVerify` (
  `emailVerifyPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `confirmationCode` VARCHAR(6) NULL,
  `email` VARCHAR(45) NULL,
  `deviceId` VARCHAR(36) NULL,
  `confirmed` TINYINT NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  PRIMARY KEY (`emailVerifyPkId`)
);

-- *************************************************
-- errorLog
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`errorLog`;

CREATE TABLE `cwc`.`errorLog` (
  `errorLogPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `serviceName` VARCHAR(75) NOT NULL, -- potential-values: [cwc-auth, cwc-api, cwc-sql, cwc-website]
  `codeLocation` VARCHAR(200) NOT NULL,
  `dismissed` TINYINT NOT NULL,
  `errorType` VARCHAR(25) NULL, -- potential-values: [error, info, warn, debug]
  `errorMessage` VARCHAR(200) NOT NULL,
  `error` TEXT NULL,
  `stack` TEXT NULL,
  `data` TEXT NULL,
  `request` TEXT NULL,
  PRIMARY KEY (`errorLogPkId`)
);

-- *************************************************
-- featureFlag
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`featureFlag`;

CREATE TABLE `cwc`.`featureFlag` (
  `featureFlagPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `featureFlagType` VARCHAR(25) NOT NULL, -- potential-values: [feature, system]
  `featureFlagId` VARCHAR(50) NOT NULL,
  `valueType` VARCHAR(25) NOT NULL, -- potential-values: [boolean, number, string, object]
  `value` VARCHAR(200) NOT NULL,
  `description` VARCHAR(200) NULL,
  `platform` VARCHAR(25) NULL, -- potential-values: [all, web, android, ios, mobile]
  `serviceName` VARCHAR(75) NOT NULL, -- potential-values: [all, frontend, backend, cwc-app, cwc-sql, cwc-auth, cwc-api, cwc-admin-api, cwc-dashboard]
  PRIMARY KEY (`featureFlagPkId`)
);

-- *************************************************
-- passwordResetInfo
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;

CREATE TABLE `cwc`.`passwordResetInfo` (
  `passwordResetInfoPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `passwordResetAttempts` INT NULL,
  PRIMARY KEY (`passwordResetInfoPkId`)
);

-- *************************************************
-- project
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`project`;

CREATE TABLE `cwc`.`project` (
  `projectPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectId` VARCHAR(150) NULL,
  `projectType` VARCHAR(25) NULL, -- potential-values: [web, mobile, backend, ai]
  `userPkId` INT NULL,
  PRIMARY KEY (`projectPkId`)
);

-- *************************************************
-- reaction
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`reaction`;

CREATE TABLE `cwc`.`reaction` (
  `reactionPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectPkId` INT NULL,
  `userPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `reactionName` VARCHAR(25) NULL, -- potential-values: [love, funny, sad, wow, thumbs-up]
  PRIMARY KEY (`reactionPkId`)
);

-- *************************************************
-- signupInfo
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`signupInfo`;

CREATE TABLE `cwc`.`signupInfo` (
  `signupInfoPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `loginType` VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]
  `userPkId` INT NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `signupAttempts` INT NULL,
  PRIMARY KEY (`signupInfoPkId`)
);

-- *************************************************
-- tempJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`tempJwt`;

CREATE TABLE `cwc`.`tempJwt` (
  `tempJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `tempJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`tempJwtPkId`)
);

-- *************************************************
-- user
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`user`;

CREATE TABLE `cwc`.`user` (
  `userPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `username` VARCHAR(100) NULL,
  `loginType` VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]
  `loginDate` DATETIME NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `otpSecret` VARCHAR(30) NULL,
  `otpValidated` TINYINT NULL,
  PRIMARY KEY (`userPkId`)
);

-- *************************************************
-- userJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`userJwt`;

CREATE TABLE `cwc`.`userJwt` (
  `userJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`userJwtPkId`)
);

Version 2 (latest)

-- ********************************************************************
-- CWC Database Schema - Table Definitions
-- ********************************************************************

-- CREATE SCHEMA `cwc`;

USE `cwc`;

-- *************************************************
-- abuseReport
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`abuseReport`;

CREATE TABLE `cwc`.`abuseReport` (
  `abuseReportPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `usernames` VARCHAR(1000) NOT NULL,
  `message` VARCHAR(500) NOT NULL,
  `status` VARCHAR(25) NULL, -- potential-values: [submitted, investigation, dismissed, resolved, retracted]
  PRIMARY KEY (`abuseReportPkId`)
);

-- *************************************************
-- adminUser
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`adminUser`;

CREATE TABLE `cwc`.`adminUser` (
  `adminUserPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `adminUsername` VARCHAR(25) NULL,
  `password` VARCHAR(100) NULL,
  PRIMARY KEY (`adminUserPkId`)
);

-- *************************************************
-- adminUserJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`adminUserJwt`;

CREATE TABLE `cwc`.`adminUserJwt` (
  `adminUserJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `adminUserJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`adminUserJwtPkId`)
);

-- *************************************************
-- codingSession
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSession`;

CREATE TABLE `cwc`.`codingSession` (
  `codingSessionPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `description` TEXT NULL,
  `published` TINYINT NULL,
  PRIMARY KEY (`codingSessionPkId`)
);

-- *************************************************
-- codingSessionAttachment
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;

CREATE TABLE `cwc`.`codingSessionAttachment` (
  `codingSessionAttachmentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `codingSessionPkId` INT NULL,
  `filename` VARCHAR(50) NULL,
  `mimeType` VARCHAR(100) NULL, -- potential-values: [image/png, image/jpg]
  `height` INT NULL,
  `width` INT NULL,
  PRIMARY KEY (`codingSessionAttachmentPkId`)
);

-- *************************************************
-- codingSessionContent
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionContent`;

CREATE TABLE `cwc`.`codingSessionContent` (
  `codingSessionContentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `contentType` VARCHAR(25) NULL, -- potential-values: [prompt, response, note, attachment]
  `codingSessionPkId` INT NULL,
  `codingSessionAttachmentPkId` INT NULL,
  `displayIndex` INT NULL,
  `text` TEXT NULL,
  PRIMARY KEY (`codingSessionContentPkId`)
);

-- *************************************************
-- comment
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`comment`;

CREATE TABLE `cwc`.`comment` (
  `commentPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectPkId` INT NULL,
  `userPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `text` TEXT NULL,
  PRIMARY KEY (`commentPkId`)
);

-- *************************************************
-- contentReport
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`contentReport`;

CREATE TABLE `cwc`.`contentReport` (
  `contentReportPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `message` VARCHAR(500) NOT NULL,
  `status` VARCHAR(25) NULL, -- potential-values: [submitted, investigation, dismissed, resolved, retracted]
  PRIMARY KEY (`contentReportPkId`)
);

-- *************************************************
-- emailVerify
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`emailVerify`;

CREATE TABLE `cwc`.`emailVerify` (
  `emailVerifyPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `confirmationCode` VARCHAR(6) NULL,
  `email` VARCHAR(45) NULL,
  `deviceId` VARCHAR(36) NULL,
  `confirmed` TINYINT NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  PRIMARY KEY (`emailVerifyPkId`)
);

-- *************************************************
-- errorLog
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`errorLog`;

CREATE TABLE `cwc`.`errorLog` (
  `errorLogPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `projectPkId` INT NULL,
  `serviceName` VARCHAR(75) NOT NULL, -- potential-values: [cwc-auth, cwc-api, cwc-sql, cwc-website]
  `codeLocation` VARCHAR(200) NOT NULL,
  `dismissed` TINYINT NOT NULL,
  `errorType` VARCHAR(25) NULL, -- potential-values: [error, info, warn, debug]
  `errorMessage` VARCHAR(200) NOT NULL,
  `error` TEXT NULL,
  `stack` TEXT NULL,
  `data` TEXT NULL,
  `request` TEXT NULL,
  PRIMARY KEY (`errorLogPkId`)
);

-- *************************************************
-- featureFlag
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`featureFlag`;

CREATE TABLE `cwc`.`featureFlag` (
  `featureFlagPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `featureFlagType` VARCHAR(25) NOT NULL, -- potential-values: [feature, system]
  `featureFlagId` VARCHAR(50) NOT NULL,
  `valueType` VARCHAR(25) NOT NULL, -- potential-values: [boolean, number, string, object]
  `value` VARCHAR(200) NOT NULL,
  `description` VARCHAR(200) NULL,
  `platform` VARCHAR(25) NULL, -- potential-values: [all, web, android, ios, mobile]
  `serviceName` VARCHAR(75) NOT NULL, -- potential-values: [all, frontend, backend, cwc-app, cwc-sql, cwc-auth, cwc-api, cwc-admin-api, cwc-dashboard]
  PRIMARY KEY (`featureFlagPkId`)
);

-- *************************************************
-- passwordResetInfo
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;

CREATE TABLE `cwc`.`passwordResetInfo` (
  `passwordResetInfoPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userPkId` INT NULL,
  `username` VARCHAR(100) NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `passwordResetAttempts` INT NULL,
  PRIMARY KEY (`passwordResetInfoPkId`)
);

-- *************************************************
-- project
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`project`;

CREATE TABLE `cwc`.`project` (
  `projectPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectId` VARCHAR(150) NULL,
  `projectType` VARCHAR(25) NULL, -- potential-values: [web, mobile, backend, ai]
  `userPkId` INT NULL,
  PRIMARY KEY (`projectPkId`)
);

-- *************************************************
-- reaction
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`reaction`;

CREATE TABLE `cwc`.`reaction` (
  `reactionPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `projectPkId` INT NULL,
  `userPkId` INT NULL,
  `entityPkId` INT NOT NULL,
  `entityType` VARCHAR(50) NOT NULL, -- potential-values: [project, codingSession, codingSessionContent, codingSessionAttachment, comment]
  `reactionName` VARCHAR(25) NULL, -- potential-values: [love, funny, sad, wow, thumbs-up]
  PRIMARY KEY (`reactionPkId`)
);

-- *************************************************
-- signupInfo
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`signupInfo`;

CREATE TABLE `cwc`.`signupInfo` (
  `signupInfoPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `loginType` VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]
  `userPkId` INT NULL,
  `username` VARCHAR(100) NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailVerificationId` VARCHAR(36) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `signupAttempts` INT NULL,
  PRIMARY KEY (`signupInfoPkId`)
);

-- *************************************************
-- tempJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`tempJwt`;

CREATE TABLE `cwc`.`tempJwt` (
  `tempJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `tempJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`tempJwtPkId`)
);

-- *************************************************
-- user
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`user`;

CREATE TABLE `cwc`.`user` (
  `userPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `username` VARCHAR(100) NULL,
  `loginType` VARCHAR(25) NULL, -- potential-values: [cwc, facebook, google]
  `loginDate` DATETIME NULL,
  `password` VARCHAR(100) NULL,
  `email` VARCHAR(45) NULL,
  `emailConfirmed` TINYINT NULL,
  `deviceId` VARCHAR(36) NULL,
  `otpSecret` VARCHAR(30) NULL,
  `otpValidated` TINYINT NULL,
  PRIMARY KEY (`userPkId`)
);

-- *************************************************
-- userJwt
-- *************************************************

DROP TABLE IF EXISTS `cwc`.`userJwt`;

CREATE TABLE `cwc`.`userJwt` (
  `userJwtPkId` INT NOT NULL AUTO_INCREMENT,
  `enabled` TINYINT NULL,
  `createdDate` DATETIME NULL,
  `modifiedDate` DATETIME NULL,
  `userJwtId` VARCHAR(36) NULL,
  PRIMARY KEY (`userJwtPkId`)
);

packages/cwc-types/package.json

3 versions

Version 1

{
  "name": "cwc-types",
  "version": "0.0.1",
  "description": "Shared TypeScript types for CWC application",
  "type": "module",
  "main": "./dist/index.js",
  "types": "./dist/index.d.ts",
  "exports": {
    ".": {
      "types": "./dist/index.d.ts",
      "default": "./dist/index.js"
    },
    "./entityTypes": {
      "types": "./dist/entityTypes.d.ts",
      "default": "./dist/entityTypes.js"
    }
  },
  "scripts": {
    "build": "tsc",
    "typecheck": "tsc --noEmit"
  },
  "keywords": ["cwc", "types", "typescript"],
  "author": "",
  "license": "UNLICENSED",
  "devDependencies": {
    "typescript": "^5.4.0"
  }
}

Version 2

{
  "name": "cwc-types",
  "version": "1.0.1",
  "description": "Shared TypeScript types for CWC application",
  "type": "module",
  "main": "./dist/index.js",
  "types": "./dist/index.d.ts",
  "exports": {
    ".": {
      "types": "./dist/index.d.ts",
      "default": "./dist/index.js"
    },
    "./entityTypes": {
      "types": "./dist/entityTypes.d.ts",
      "default": "./dist/entityTypes.js"
    }
  },
  "scripts": {
    "build": "tsc",
    "typecheck": "tsc --noEmit"
  },
  "keywords": ["cwc", "types", "typescript"],
  "author": "",
  "license": "UNLICENSED",
  "devDependencies": {
    "typescript": "^5.4.0"
  }
}

Version 3 (latest)

{
  "name": "cwc-types",
  "version": "1.0.0",
  "description": "Shared TypeScript types for CWC application",
  "type": "module",
  "main": "./dist/index.js",
  "types": "./dist/index.d.ts",
  "exports": {
    ".": {
      "types": "./dist/index.d.ts",
      "default": "./dist/index.js"
    },
    "./entityTypes": {
      "types": "./dist/entityTypes.d.ts",
      "default": "./dist/entityTypes.js"
    }
  },
  "scripts": {
    "build": "tsc",
    "typecheck": "tsc --noEmit"
  },
  "keywords": ["cwc", "types", "typescript"],
  "author": "",
  "license": "UNLICENSED",
  "devDependencies": {
    "typescript": "^5.4.0"
  }
}

packages/cwc-types/src/entityTypes.ts

2 versions

Version 1

/**
 * CWC Database Entity Types
 *
 * This file contains TypeScript type definitions for all database entities.
 * Generated from the database schema in packages/cwc-database/schema-definition/
 *
 * Type Generation Conventions:
 * - All entity types prefixed with 'Cwc' to avoid naming conflicts
 * - Use 'type' (not 'interface') for entity definitions
 * - Use string literal union types (not enums) for enum-like VARCHAR fields
 * - DATETIME columns mapped to string (ISO 8601 format)
 * - TINYINT columns mapped to boolean
 * - All fields required by default (even if DB allows NULL)
 * - Optional fields (?) only for business-critical reasons
 *
 * Working with Incomplete Data:
 * When working with partial entity data (e.g., during INSERT operations
 * where PkId doesn't exist yet, or multi-step processes), use TypeScript's
 * Partial<T> utility type:
 *
 * @example
 * // For INSERT operations (omit auto-generated fields)
 * type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
 *
 * // For partial updates
 * const partialUser: Partial<CwcUser> = { email: 'new@example.com' };
 *
 * // For specific operation requirements
 * type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;
 */

// ============================================================================
// Union Types for Enum-like VARCHAR Fields
// ============================================================================

export type CwcAbuseReportStatus =
  | 'submitted'
  | 'investigation'
  | 'dismissed'
  | 'resolved'
  | 'retracted';

export type CwcContentReportStatus =
  | 'submitted'
  | 'investigation'
  | 'dismissed'
  | 'resolved'
  | 'retracted';

export type CwcContentType = 'prompt' | 'response' | 'note' | 'attachment';

export type CwcEntityType =
  | 'project'
  | 'codingSession'
  | 'codingSessionContent'
  | 'codingSessionAttachment'
  | 'comment';

export type CwcErrorType = 'error' | 'info' | 'warn' | 'debug';

export type CwcFeatureFlagType = 'feature' | 'system';

export type CwcLoginType = 'cwc' | 'facebook' | 'google';

export type CwcMimeType = 'image/png' | 'image/jpg';

export type CwcPlatform = 'all' | 'web' | 'android' | 'ios' | 'mobile';

export type CwcProjectType = 'web' | 'mobile' | 'backend' | 'ai';

export type CwcReactionName = 'love' | 'funny' | 'sad' | 'wow' | 'thumbs-up';

export type CwcServiceName =
  | 'all'
  | 'frontend'
  | 'backend'
  | 'cwc-app'
  | 'cwc-sql'
  | 'cwc-auth'
  | 'cwc-api'
  | 'cwc-admin-api'
  | 'cwc-dashboard'
  | 'cwc-website';

export type CwcValueType = 'boolean' | 'number' | 'string' | 'object';

// ============================================================================
// Entity Types
// ============================================================================

/**
 * Abuse Report
 * Tracks user reports of abusive behavior or accounts
 */
export type CwcAbuseReport = {
  abuseReportPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  usernames: string;
  message: string;
  status: CwcAbuseReportStatus;
};

/**
 * Admin User
 * Administrative users for the dashboard
 */
export type CwcAdminUser = {
  adminUserPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  adminUsername: string;
  password: string;
};

/**
 * Admin User JWT
 * JWT validation/revocation for admin users
 * Contains only JWT ID for lookup; payload contains full user context
 */
export type CwcAdminUserJwt = {
  adminUserJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  adminUserJwtId: string;
};

/**
 * Coding Session
 * Individual coding sessions within a project
 */
export type CwcCodingSession = {
  codingSessionPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  description: string;
  published: boolean;
};

/**
 * Coding Session Attachment
 * Images and files attached to coding sessions
 */
export type CwcCodingSessionAttachment = {
  codingSessionAttachmentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  codingSessionPkId: number;
  filename: string;
  mimeType: CwcMimeType;
  height: number;
  width: number;
};

/**
 * Coding Session Content
 * Content blocks (prompts, responses, notes, attachments) within a session
 * Uses displayIndex for rendering order of all content types
 * Polymorphic: text OR attachment based on contentType
 */
export type CwcCodingSessionContent = {
  codingSessionContentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  contentType: CwcContentType;
  codingSessionPkId: number;
  codingSessionAttachmentPkId?: number; // Optional: only present when contentType='attachment'
  displayIndex: number;
  text?: string; // Optional: NULL for pure attachment blocks, or optional caption for attachments
};

/**
 * Comment
 * User comments on various entities (polymorphic relationship)
 * Uses entityPkId + entityType pattern to reference any commentable entity
 */
export type CwcComment = {
  commentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectPkId: number;
  userPkId: number;
  entityPkId: number;
  entityType: CwcEntityType;
  text: string;
};

/**
 * Content Report
 * User reports of inappropriate content (polymorphic relationship)
 */
export type CwcContentReport = {
  contentReportPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  entityPkId: number;
  entityType: CwcEntityType;
  message: string;
  status: CwcContentReportStatus;
};

/**
 * Email Verify
 * Email verification tracking (standalone, not tied to signup/reset)
 */
export type CwcEmailVerify = {
  emailVerifyPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  confirmationCode: string;
  email: string;
  deviceId: string;
  confirmed: boolean;
  emailVerificationId: string;
};

/**
 * Error Log
 * Centralized error logging for all microservices
 */
export type CwcErrorLog = {
  errorLogPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId?: number; // Optional: errors can occur without user context
  projectPkId?: number; // Optional: errors can occur without project context
  serviceName: CwcServiceName;
  codeLocation: string;
  dismissed: boolean;
  errorType: CwcErrorType;
  errorMessage: string;
  error: string;
  stack: string;
  data: string;
  request: string;
};

/**
 * Feature Flag
 * Runtime feature toggles and configuration
 */
export type CwcFeatureFlag = {
  featureFlagPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  featureFlagType: CwcFeatureFlagType;
  featureFlagId: string;
  valueType: CwcValueType;
  value: string;
  description: string;
  platform: CwcPlatform;
  serviceName: CwcServiceName;
};

/**
 * Password Reset Info
 * Tracks password reset process state
 * Stores denormalized user data collected during the reset flow
 */
export type CwcPasswordResetInfo = {
  passwordResetInfoPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  username: string;
  password?: string; // Optional: not set until user provides new password
  email: string;
  emailVerificationId: string;
  emailConfirmed: boolean;
  deviceId: string;
  passwordResetAttempts: number;
};

/**
 * Project
 * User projects that contain coding sessions
 */
export type CwcProject = {
  projectPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectId: string;
  projectType: CwcProjectType;
  userPkId: number;
};

/**
 * Reaction
 * User reactions (emoji-style) to various entities
 */
export type CwcReaction = {
  reactionPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectPkId: number;
  userPkId: number;
  entityPkId: number;
  entityType: CwcEntityType;
  reactionName: CwcReactionName;
};

/**
 * Signup Info
 * Tracks signup process state
 * Stores denormalized user data collected during the signup flow
 */
export type CwcSignupInfo = {
  signupInfoPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  loginType: CwcLoginType;
  userPkId?: number; // Optional: not set until signup completes
  username: string;
  password?: string; // Optional: NULL for OAuth signups or until provided
  email: string;
  emailVerificationId: string;
  emailConfirmed: boolean;
  deviceId: string;
  signupAttempts: number;
};

/**
 * Temp JWT
 * JWT validation/revocation for temporary multi-step processes
 * Contains only JWT ID for lookup; payload contains process context
 */
export type CwcTempJwt = {
  tempJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  tempJwtId: string;
};

/**
 * User
 * Primary user accounts
 */
export type CwcUser = {
  userPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  username: string;
  loginType: CwcLoginType;
  loginDate: string;
  password?: string; // Optional: NULL for OAuth users
  email: string;
  emailConfirmed: boolean;
  deviceId: string;
  otpSecret?: string; // Optional: only present if 2FA enabled
  otpValidated: boolean;
};

/**
 * User JWT
 * JWT validation/revocation for regular users
 * Contains only JWT ID for lookup; payload contains full user context
 */
export type CwcUserJwt = {
  userJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userJwtId: string;
};

Version 2 (latest)

/**
 * CWC Database Entity Types
 *
 * This file contains TypeScript type definitions for all database entities.
 * Generated from the database schema in packages/cwc-database/schema-definition/
 *
 * Type Generation Conventions:
 * - All entity types prefixed with 'Cwc' to avoid naming conflicts
 * - Union types fully qualified with entity context to prevent ambiguity
 * - Use 'type' (not 'interface') for entity definitions
 * - Use string literal union types (not enums) for enum-like VARCHAR fields
 * - DATETIME columns mapped to string (ISO 8601 format)
 * - TINYINT columns mapped to boolean
 * - All fields required by default (even if DB allows NULL)
 * - Optional fields (?) only for business-critical reasons
 *
 * Working with Incomplete Data:
 * When working with partial entity data (e.g., during INSERT operations
 * where PkId doesn't exist yet, or multi-step processes), use TypeScript's
 * Partial<T> utility type:
 *
 * @example
 * // For INSERT operations (omit auto-generated fields)
 * type UserInsert = Omit<CwcUser, 'userPkId' | 'createdDate' | 'modifiedDate'>;
 *
 * // For partial updates
 * const partialUser: Partial<CwcUser> = { email: 'new@example.com' };
 *
 * // For specific operation requirements
 * type UserUpdate = Partial<UserInsert> & Pick<CwcUser, 'userPkId'>;
 */

// ============================================================================
// Union Types for Enum-like VARCHAR Fields
// ============================================================================

/**
 * Status values for abuse reports
 */
export type CwcAbuseReportStatus =
  | 'submitted'
  | 'investigation'
  | 'dismissed'
  | 'resolved'
  | 'retracted';

/**
 * Entity types that can be commented on
 * Note: Each entity using this type may have different allowed values
 */
export type CwcCommentEntityType =
  | 'project'
  | 'codingSession'
  | 'codingSessionContent'
  | 'codingSessionAttachment'
  | 'comment';

/**
 * Content types for coding session content blocks
 */
export type CwcCodingSessionContentType = 'prompt' | 'response' | 'note' | 'attachment';

/**
 * MIME types for coding session attachments
 */
export type CwcCodingSessionAttachmentMimeType = 'image/png' | 'image/jpg';

/**
 * Entity types that can be reported for inappropriate content
 * Note: May differ from comment entity types in the future
 */
export type CwcContentReportEntityType =
  | 'project'
  | 'codingSession'
  | 'codingSessionContent'
  | 'codingSessionAttachment'
  | 'comment';

/**
 * Status values for content reports
 */
export type CwcContentReportStatus =
  | 'submitted'
  | 'investigation'
  | 'dismissed'
  | 'resolved'
  | 'retracted';

/**
 * Error log severity/type levels
 */
export type CwcErrorLogType = 'error' | 'info' | 'warn' | 'debug';

/**
 * Service names for error logging
 */
export type CwcErrorLogServiceName = 'cwc-auth' | 'cwc-api' | 'cwc-sql' | 'cwc-website';

/**
 * Feature flag type classification
 */
export type CwcFeatureFlagType = 'feature' | 'system';

/**
 * Platform targets for feature flags
 */
export type CwcFeatureFlagPlatform = 'all' | 'web' | 'android' | 'ios' | 'mobile';

/**
 * Service names for feature flag targeting
 */
export type CwcFeatureFlagServiceName =
  | 'all'
  | 'frontend'
  | 'backend'
  | 'cwc-app'
  | 'cwc-sql'
  | 'cwc-auth'
  | 'cwc-api'
  | 'cwc-admin-api'
  | 'cwc-dashboard';

/**
 * Value types for feature flag values
 */
export type CwcFeatureFlagValueType = 'boolean' | 'number' | 'string' | 'object';

/**
 * Login/authentication types
 * Shared by user and signupInfo entities
 */
export type CwcLoginType = 'cwc' | 'facebook' | 'google';

/**
 * Project type classifications
 */
export type CwcProjectType = 'web' | 'mobile' | 'backend' | 'ai';

/**
 * Reaction emoji names
 */
export type CwcReactionName = 'love' | 'funny' | 'sad' | 'wow' | 'thumbs-up';

/**
 * Entity types that can receive reactions
 * Note: May differ from comment/report entity types in the future
 */
export type CwcReactionEntityType =
  | 'project'
  | 'codingSession'
  | 'codingSessionContent'
  | 'codingSessionAttachment'
  | 'comment';

// ============================================================================
// Entity Types
// ============================================================================

/**
 * Abuse Report
 * Tracks user reports of abusive behavior or accounts
 */
export type CwcAbuseReport = {
  abuseReportPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  usernames: string;
  message: string;
  status: CwcAbuseReportStatus;
};

/**
 * Admin User
 * Administrative users for the dashboard
 */
export type CwcAdminUser = {
  adminUserPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  adminUsername: string;
  password: string;
};

/**
 * Admin User JWT
 * JWT validation/revocation for admin users
 * Contains only JWT ID for lookup; payload contains full user context
 */
export type CwcAdminUserJwt = {
  adminUserJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  adminUserJwtId: string;
};

/**
 * Coding Session
 * Individual coding sessions within a project
 */
export type CwcCodingSession = {
  codingSessionPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  description: string;
  published: boolean;
};

/**
 * Coding Session Attachment
 * Images and files attached to coding sessions
 */
export type CwcCodingSessionAttachment = {
  codingSessionAttachmentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  codingSessionPkId: number;
  filename: string;
  mimeType: CwcCodingSessionAttachmentMimeType;
  height: number;
  width: number;
};

/**
 * Coding Session Content
 * Content blocks (prompts, responses, notes, attachments) within a session
 * Uses displayIndex for rendering order of all content types
 * Polymorphic: text OR attachment based on contentType
 */
export type CwcCodingSessionContent = {
  codingSessionContentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  contentType: CwcCodingSessionContentType;
  codingSessionPkId: number;
  codingSessionAttachmentPkId?: number; // Optional: only present when contentType='attachment'
  displayIndex: number;
  text?: string; // Optional: NULL for pure attachment blocks, or optional caption for attachments
};

/**
 * Comment
 * User comments on various entities (polymorphic relationship)
 * Uses entityPkId + entityType pattern to reference any commentable entity
 */
export type CwcComment = {
  commentPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectPkId: number;
  userPkId: number;
  entityPkId: number;
  entityType: CwcCommentEntityType;
  text: string;
};

/**
 * Content Report
 * User reports of inappropriate content (polymorphic relationship)
 */
export type CwcContentReport = {
  contentReportPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  projectPkId: number;
  entityPkId: number;
  entityType: CwcContentReportEntityType;
  message: string;
  status: CwcContentReportStatus;
};

/**
 * Email Verify
 * Email verification tracking (standalone, not tied to signup/reset)
 */
export type CwcEmailVerify = {
  emailVerifyPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  confirmationCode: string;
  email: string;
  deviceId: string;
  confirmed: boolean;
  emailVerificationId: string;
};

/**
 * Error Log
 * Centralized error logging for all microservices
 */
export type CwcErrorLog = {
  errorLogPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId?: number; // Optional: errors can occur without user context
  projectPkId?: number; // Optional: errors can occur without project context
  serviceName: CwcErrorLogServiceName;
  codeLocation: string;
  dismissed: boolean;
  errorType: CwcErrorLogType;
  errorMessage: string;
  error: string;
  stack: string;
  data: string;
  request: string;
};

/**
 * Feature Flag
 * Runtime feature toggles and configuration
 */
export type CwcFeatureFlag = {
  featureFlagPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  featureFlagType: CwcFeatureFlagType;
  featureFlagId: string;
  valueType: CwcFeatureFlagValueType;
  value: string;
  description: string;
  platform: CwcFeatureFlagPlatform;
  serviceName: CwcFeatureFlagServiceName;
};

/**
 * Password Reset Info
 * Tracks password reset process state
 * Stores denormalized user data collected during the reset flow
 */
export type CwcPasswordResetInfo = {
  passwordResetInfoPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userPkId: number;
  username: string;
  password?: string; // Optional: not set until user provides new password
  email: string;
  emailVerificationId: string;
  emailConfirmed: boolean;
  deviceId: string;
  passwordResetAttempts: number;
};

/**
 * Project
 * User projects that contain coding sessions
 */
export type CwcProject = {
  projectPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectId: string;
  projectType: CwcProjectType;
  userPkId: number;
};

/**
 * Reaction
 * User reactions (emoji-style) to various entities
 */
export type CwcReaction = {
  reactionPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  projectPkId: number;
  userPkId: number;
  entityPkId: number;
  entityType: CwcReactionEntityType;
  reactionName: CwcReactionName;
};

/**
 * Signup Info
 * Tracks signup process state
 * Stores denormalized user data collected during the signup flow
 */
export type CwcSignupInfo = {
  signupInfoPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  loginType: CwcLoginType;
  userPkId?: number; // Optional: not set until signup completes
  username: string;
  password?: string; // Optional: NULL for OAuth signups or until provided
  email: string;
  emailVerificationId: string;
  emailConfirmed: boolean;
  deviceId: string;
  signupAttempts: number;
};

/**
 * Temp JWT
 * JWT validation/revocation for temporary multi-step processes
 * Contains only JWT ID for lookup; payload contains process context
 */
export type CwcTempJwt = {
  tempJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  tempJwtId: string;
};

/**
 * User
 * Primary user accounts
 */
export type CwcUser = {
  userPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  username: string;
  loginType: CwcLoginType;
  loginDate: string;
  password?: string; // Optional: NULL for OAuth users
  email: string;
  emailConfirmed: boolean;
  deviceId: string;
  otpSecret?: string; // Optional: only present if 2FA enabled
  otpValidated: boolean;
};

/**
 * User JWT
 * JWT validation/revocation for regular users
 * Contains only JWT ID for lookup; payload contains full user context
 */
export type CwcUserJwt = {
  userJwtPkId: number;
  enabled: boolean;
  createdDate: string;
  modifiedDate: string;
  userJwtId: string;
};

packages/cwc-types/src/index.ts

/**
 * CWC Types - Shared TypeScript types for CWC application
 *
 * This package contains all shared type definitions used across
 * the CWC monorepo packages.
 */

// Export all entity types
export * from './entityTypes.js';

packages/cwc-types/tsconfig.json

{
  "extends": "../../tsconfig.base.json",
  "compilerOptions": {
    "composite": true,
    "rootDir": "./src",
    "outDir": "./dist"
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

Session 04: entity types

Session 04: entity types

My Thoughts on Your Questions

Enums vs Union Types

Interfaces vs Types

Clarifying Questions

Analysis: Partial Approach - Pros & Cons

Pros of using strict base types + Partial:

Cons:

Trade-off Assessment:

Truly Optional Fields (Business Logic Analysis)

Content Block Pattern:

Error Logging (No User/Project Context):

Multi-Step Process States:

Conditional Features:

Adding username to signupInfo and passwordResetInfo

PkId vs Natural Keys for Foreign Key References

Why PkId is the Right Choice

Your Current Design is Actually Perfect

When Natural Keys ARE Appropriate

Addressing Your Specific Concerns

"Will we ever allow username/projectId changes?"

"Cost of looking up values"

"Index cached collections by projectId/username"

Final Recommendations

1. Keep using PkId for all foreign key references ✅

2. Add username to signupInfo and passwordResetInfo ✅

3. Schema Changes Needed:

4. Consider Adding Index:

5. Update Entity Types Plan:

Bottom Line

✅ Database Schema Updates Complete

Files Modified:

Verification Complete:

Next Step: Generate TypeScript Entity Types

✅ TypeScript Entity Types Generation Complete!

Package Created: cwc-types

Generated Types Summary

Type Generation Decisions Applied

Verification

Usage Example

✅ Union Types Fully Qualified with Context

Union Type Renamings

Final Count

Key Improvements

Verification

✅ CLAUDE.md Updated with Union Type Naming Conventions

What Was Documented:

Changes Made:

1. Updated cwc-types/package.json

2. Added to CLAUDE.md

Session 04: entity types

CLAUDE.md

Project Overview

What is codingwithclaude?

Project name & aliases

Proactive Documentation Philosophy

File Access Restrictions and Security Boundaries

Workspace Boundaries

Prohibited File Access

Git Workflow

Architecture Overview

Monorepo Structure (future plan)

Development Tooling & Infrastructure

Monorepo Management

Node.js Version

Code Quality Tools

Root Scripts

Development Workflow

Key Architectural Decisions & Patterns

MariaDb database

TypeScript

Cloud-Agnostic Microservices

Environment Configuration

Database Schema Conventions

Schema and File Organization

Foreign Key Constraints

Naming Conventions

Required Columns

Enum-Like VARCHAR Fields

Adding `username` to signupInfo and passwordResetInfo

Package Created: `cwc-types`