Developer MessagesClaude MessagesShow Tool Use

jeff

I would like to work on defining the database schema next and creating the scripts needed to create the database tables. I have a pattern that works well for me, so let's be sure to always adhere to these conventions. When adding a new database table or making schema changes, following these rules ensures consistency across the database schema and makes it easier to work with. Once you commit these conventions, patterns, and rules to the claude.md file it will become our standard for database schema changes.

database guidelines

table names

always singular
camelCase
no underscores
no hyphens

the script to create all tables will be a single file organized alphabetically by {tableName}

when we run this script the first time we need to create the cwc schema, let's add this command at the top of the script and comment it out:
- CREATE SCHEMA cwc;
packages/cwc-database/schema-definition/create-tables.sql
Do NOT include indexes in CREATE TABLE statement, there is a separate script for indexes

the script to create indexes will be a single file organized alphabetically by {tableName}

packages/cwc-database/schema-definition/create-indexes.sql
unique index names will begin with the ux_ prefix, ux_{tableName}_{columnName}
non-unique index names will begin with the ix_ prefix, ix_{tableName}_{columnName}
indexes should be added for all columns that we use in where clauses and in joins
when creating an index also script a commented out DROP INDEX on the preceding line

the script to create views will be a single file organized alphabetically by {viewName}

packages/cwc-database/schema-definition/create-views.sql
view names with end with View
view names should be concise but accurate and descriptive of the type of data included

migration scripts / schema updates and sometimes associated data insert/update scripts :

located in packages/cwc-database/schema-changes/
file name includes the current date in YYYY-MM-DD format, (e.g.YYYY-MM-DD-schema-changes.sql)
example: packages/cwc-database/schema-changes/2025-11-17-schema-changes.sql
when we add a new table, index, or view, in addition to updating the definition scripts, add to a migration script
when we alter a table, index, or view, in addition to updating the definition scripts, add to a migration script
if we are making multiple schema changes on the same day, we will add them all into one script for that day

the script to drop all tables will be a single file organized alphabetically by {tableName}

packages/cwc-database/schema-definition/drop-tables.sql
keep the drop table script up to date with the ongoing schema changes

defining table columns

camelCase
no underscores or hyphens
some columns have defined possible values (enums), put a comment to indicate what values are expected such as:
userType VARCHAR(25) NULL, -- potential-values: [guest, projectOwner, siteAdmin]
this is important because we will use these values later on when we are creating our TypeScript types

all tables with have the following fields:

{tableName}PkId INT NOT NULL AUTO_INCREMENT
enabled TINYINT NULL
createdDate DATETIME NULL
modifiedDate DATETIME NULL

`project` table `CREATE TABLE` script example:

-- *************************************************
-- 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,
  `userPkId` INT NULL,  -- the user that owns the project
  `projectId` VARCHAR(150) NULL,   -- the id / name provided by the user
  -- ... custom columns
  `projectStatus` VARCHAR(25) NULL, -- potential-values: [active, inactive]
  PRIMARY KEY (`projectPkId`)
);


### `project` table `CREATE INDEX` script example:

```sql
-- *************************************************
-- 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;

`CREATE VIEW` script example:

-- ********************************************************************
-- userProjectView - joins user and project tables
-- returns all projects with some user information
-- query example: select * from userProjectView where username='jeff'
-- ********************************************************************

USE `cwc`;

CREATE OR REPLACE VIEW `userProjectView` AS
select
  u.username,
  u.userFirstName,
  u.userLastName,
  p.projectId,
  p.projectPkId,
  p.projectStatus,
from `user` as u
inner join `project` as p on p.userId = u.userId

coding-with-claude entities

The following list of entities is my first attempt at identifying the type of information that the database schema will need to store.
As we build the application it will become more clear what information will need to be stored

`abuseReport` (users can report other user's activity that they deem abusive)

abuseReportPkId INT NOT NULL AUTO_INCREMENT,
userPkId INT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
enabled TINYINT NULL,
projectPkId INT NULL,
usernames VARCHAR(1000) NOT NULL, -- usernames reported for abusive behavior
message VARCHAR(500) NOT NULL,
status VARCHAR(25) NULL, -- potential-values: [submitted, investigation, dismissed, resolved, retracted]

`adminUserJtw` (JWTs issued when an adminUser is authenticated)

adminUserJwtPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
adminUserJwtId VARCHAR(36) NULL, -- uuid

`adminUser` (adminUsers have access to the `cwc-dashboard` management app)

adminUserPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
adminUsername VARCHAR(25) NULL,
password VARCHAR(100) NULL, -- encrypted value is ~65 chars

`codingSessionContent` (content such as prompts, responses, notes, attachments provided by the project owner)

codingSessionContentPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
userPkId INT NULL,
projectPkId INT NULL,
contentType VARCHAR(25), -- potential-values: [prompt, response, note, attachment]
codingSessionPkId INT NULL,
codingSessionAttachmentPkId INT NULL,
displayIndex INT NULL, -- the order in which the content displays
text TEXT(20000) NULL,

`codingSessionAttachment` (a file that was uploaded as part of the codingSession content)

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, -- uuid + extension
mimeType VARCHAR(100) NULL, -- potential-values: [image/png, image/jpg]
height INT NULL,
width INT NULL,

`codingSession` (a project coding session that the project owner wants to share)

codingSessionPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
userPkId INT NULL,
projectPkId INT NULL,
description TEXT(500) NULL,
published TINYINT NULL,

`comment` (registered users can comment on various project and codingSession entities)

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(500) NULL,

`contentReport` (users can report inappropriate content)

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]

`emailVerify` (information required to perform the email verification process)

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, -- uuid
confirmed TINYINT NULL,
emailVerificationId VARCHAR(36) NULL, -- uuid

`errorLog` (apps and services record detailed error information to allow app admins to identify technical problems and help troubleshoot bugs and issues)

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, -- filename, function name
dismissed TINYINT NOT NULL, -- 0 is active, 1 is dismissed
errorType VARCHAR(25) NULL, -- potential-values: [error, info, warn, debug]
errorMessage VARCHAR(200) NOT NULL, -- custom error message
error TEXT NULL,
stack TEXT NULL,
data TEXT NULL, -- any other data relevant to the error
request TEXT NULL, -- the request to the service

`featureFlag` (feature flags allow us to enable, disable, or modify the behavior of certain features in the app)

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, -- examples: 'comments-enabled', 'themes-enabled', 'reactions-enabled'
valueType VARCHAR(25) NOT NULL, -- potential-values: [boolean, number, string, object]
value VARCHAR(200) NOT NULL, -- examples: 'true', '1', 'version 2.01', '{ web: false, android: true, ios: true }'
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]

`passwordResetInfo` (information collected during password reset process)

passwordResetInfoPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
userPkId INT NULL,
password VARCHAR(100) NULL, -- encrypted value is ~65 chars
email VARCHAR(45) NULL,
emailVerificationId VARCHAR(36) NULL, -- uuid
emailConfirmed TINYINT NULL,
deviceId VARCHAR(36) NULL, -- uuid
passwordResetAttempts INT NULL,

`project` (a software project like 'coding-with-claude' that a user (project owner) has created)

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,

`reaction` (registered users can react to various project and codingSession entities)

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]

`signupInfo` (information collected during signup process before the `user` is created)

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, -- encrypted value is ~65 chars
email VARCHAR(45) NULL,
emailVerificationId VARCHAR(36) NULL, -- uuid
emailConfirmed TINYINT NULL,
deviceId VARCHAR(36) NULL, -- uuid
signupAttempts INT NULL,

`tempJwt` (temporary JWTs issued during the user signup and password reset processes)

tempJwtPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
tempJwtId VARCHAR(36) NULL, -- uuid

`userJwt` (JWTs issued when a user is authenticated)

userJwtPkId INT NOT NULL AUTO_INCREMENT,
enabled TINYINT NULL,
createdDate DATETIME NULL,
modifiedDate DATETIME NULL,
userJwtId VARCHAR(36) NULL, -- uuid

`user` (registered user account information)

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, -- encrypted value is ~65 chars
email VARCHAR(45) NULL,
emailConfirmed TINYINT NULL,
deviceId VARCHAR(36) NULL, -- uuid
otpSecret VARCHAR(30) NULL,
otpValidated TINYINT NULL,

Claude

I'll help you define the database schema and create the necessary scripts following your conventions. Let me first explore the current state of the codebase to understand what exists and what needs to be created.

I've explored the codebase and understand the current state. Before I present a comprehensive plan, I have a few clarifying questions to ensure I create exactly what you need:

Claude is asking...

Initial viewsShould I create any database views initially, or leave create-views.sql as a placeholder for future views?

Create placeholder only- Create create-views.sql with just schema setup comments, no actual views yet

Create user/project views- Create useful views like userProjectView (joining user and project tables) as shown in your example

TypeScript typesShould I also create TypeScript type definitions in cwc-types package based on the database schema?

Yes, create types- Generate TypeScript interfaces/types for all 17 entities in cwc-types package

Not yet- Focus only on SQL scripts now, we'll add TypeScript types later

Migration scriptFor the initial migration script (2025-11-17-schema-changes.sql), what should it contain?

Tables + indexes- Include both CREATE TABLE and CREATE INDEX statements in the migration

Just reference others- Migration script references/includes the create-tables.sql and create-indexes.sql files

Package setupShould I create a package.json for cwc-database or keep it as just SQL scripts?

SQL scripts only- No package.json needed, just a directory with SQL files

Full package- Create package.json for potential future tooling (migration runners, etc.)

Developer Response

Q: Should I create any database views initially, or leave create-views.sql as a placeholder for future views?

A: "Create placeholder only"

Q: Should I also create TypeScript type definitions in cwc-types package based on the database schema?

A: "Not yet"

Q: For the initial migration script (2025-11-17-schema-changes.sql), what should it contain?

A: "for the initial schema creation, I don't think that we need a migration script - we can run create-tables.sql and create-indexes.sql"

Q: Should I create a package.json for cwc-database or keep it as just SQL scripts?

A: "SQL scripts only"

Perfect! Based on your answers, I now have a clear understanding of what needs to be done. Let me present the plan:

Exited plan mode

Plan approved

Excellent! Let me start by creating a todo list to track this work:

Perfect! I've successfully completed the database schema definition. Here's what was accomplished:

Summary

1. ✅ Updated CLAUDE.md

Added comprehensive "Database Schema Conventions" section (CLAUDE.md:223-403) documenting:

Schema and file organization rules
Naming conventions for tables, columns, indexes, and views
Required columns pattern for all tables
Enum-like VARCHAR field documentation standards
SQL script patterns with examples
Migration script guidelines

2. ✅ Created Directory Structure

packages/cwc-database/
├── schema-definition/
│   ├── create-tables.sql
│   ├── create-indexes.sql
│   ├── create-views.sql
│   └── drop-tables.sql
└── schema-changes/

3. ✅ Created create-tables.sql

All 17 tables defined in alphabetical order
Each table includes the 4 required columns (PkId, enabled, createdDate, modifiedDate)
Enum-like fields documented with -- potential-values: comments
Tables: abuseReport, adminUser, adminUserJwt, codingSession, codingSessionAttachment, codingSessionContent, comment, contentReport, emailVerify, errorLog, featureFlag, passwordResetInfo, project, reaction, signupInfo, tempJwt, user, userJwt

4. ✅ Created create-indexes.sql

Comprehensive indexes for all tables
Foreign key columns indexed (userPkId, projectPkId, etc.)
Unique indexes (ux_) for identifiers, emails, usernames
Non-unique indexes (ix_) for lookup columns
All indexes include commented DROP INDEX statements

5. ✅ Created create-views.sql

Placeholder file ready for future view definitions
Includes example pattern in comments

6. ✅ Created drop-tables.sql

Tables organized by dependency levels
Safe drop order to avoid FK constraint errors
6 levels from deepest dependencies to base tables

The database schema is now fully defined and ready to use. You can run create-tables.sql followed by create-indexes.sql to initialize the database.

jeff

Nice catch on the Safe drop order to avoid FK constraint errors but we are not going to apply FK constraints because on a large transactional sql database that can be a performance drag. the code in our microservices will need to enforce those relationships. Thank you for doing that!

Claude

You're welcome! That's an important architectural decision - let me document this in CLAUDE.md so we maintain this pattern consistently:

Tool Result

   223→## Database Schema Conventions
   224→
   225→**All database schema changes must follow these strict conventions to ensure consistency.**
   226→
   227→### Schema and File Organization
   228→
   229→**Schema Name:** `cwc`
   230→
   231→**Script Locations:**
   232→- `packages/cwc-database/schema-definition/create-tables.sql` - All CREATE TABLE statements
   233→- `packages/cwc-database/schema-definition/create-indexes.sql` - All CREATE INDEX statements
   234→- `packages/cwc-database/schema-definition/create-views.sql` - All CREATE VIEW statements
   235→- `packages/cwc-database/schema-definition/drop-tables.sql` - All DROP TABLE statements
   236→- `packages/cwc-database/schema-changes/YYYY-MM-DD-*.sql` - Migration scripts for schema updates
   237→
   238→**File Organization:**
   239→- All tables, indexes, and views organized **alphabetically by name** within their respective files
   240→- Schema creation command at top of create-tables.sql (commented): `-- CREATE SCHEMA 'cwc';`
   241→
   242→### Naming Conventions
   243→
   244→**Table Names:**
   245→- Always singular (e.g., `user` not `users`, `project` not `projects`)
   246→- camelCase format
   247→- No underscores or hyphens
   248→- Examples: `user`, `project`, `codingSession`, `abuseReport`
   249→
   250→**Column Names:**
   251→- camelCase format
   252→- No underscores or hyphens
   253→- Examples: `userPkId`, `createdDate`, `projectStatus`
   254→
   255→**Index Names:**
   256→- **Unique indexes:** `ux_{tableName}_{columnName}` prefix
   257→  - Example: `ux_project_projectId`, `ux_user_email`
   258→- **Non-unique indexes:** `ix_{tableName}_{columnName}` prefix
   259→  - Example: `ix_project_userPkId`, `ix_comment_projectPkId`
   260→
   261→**View Names:**
   262→- End with `View` suffix
   263→- camelCase format
   264→- Concise but descriptive of data included
   265→- Examples: `userProjectView`, `activeSessionView`
   266→
   267→### Required Columns
   268→
   269→**Every table MUST include these four columns:**
   270→
   271→```sql
   272→{tableName}PkId INT NOT NULL AUTO_INCREMENT,

<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/packages/cwc-database/schema-definition/drop-tables.sql",
  "old_string": "-- ********************************************************************\n-- CWC Database Schema - Drop All Tables\n-- Tables organized by dependency order to avoid FK constraint errors\n-- ********************************************************************\n\nUSE `cwc`;\n\n-- ********************************************************************\n-- Level 1: Tables with deepest dependencies (drop first)\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`codingSessionContent`;\n\n-- ********************************************************************\n-- Level 2: Tables depending on codingSession\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;\n\n-- ********************************************************************\n-- Level 3: Tables depending on project and/or user\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`abuseReport`;\nDROP TABLE IF EXISTS `cwc`.`codingSession`;\nDROP TABLE IF EXISTS `cwc`.`comment`;\nDROP TABLE IF EXISTS `cwc`.`contentReport`;\nDROP TABLE IF EXISTS `cwc`.`errorLog`;\nDROP TABLE IF EXISTS `cwc`.`reaction`;\n\n-- ********************************************************************\n-- Level 4: Tables depending on user\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;\nDROP TABLE IF EXISTS `cwc`.`project`;\nDROP TABLE IF EXISTS `cwc`.`signupInfo`;\n\n-- ********************************************************************\n-- Level 5: Tables with no foreign key dependencies\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`adminUserJwt`;\nDROP TABLE IF EXISTS `cwc`.`emailVerify`;\nDROP TABLE IF EXISTS `cwc`.`featureFlag`;\nDROP TABLE IF EXISTS `cwc`.`tempJwt`;\nDROP TABLE IF EXISTS `cwc`.`userJwt`;\n\n-- ********************************************************************\n-- Level 6: Base tables (drop last)\n-- ********************************************************************\n\nDROP TABLE IF EXISTS `cwc`.`adminUser`;\nDROP TABLE IF EXISTS `cwc`.`user`;",
  "new_string": "-- ********************************************************************\n-- CWC Database Schema - Drop All Tables\n-- Tables organized alphabetically\n-- No FK constraints are used, so drop order doesn't matter\n-- ********************************************************************\n\nUSE `cwc`;\n\nDROP TABLE IF EXISTS `cwc`.`abuseReport`;\nDROP TABLE IF EXISTS `cwc`.`adminUser`;\nDROP TABLE IF EXISTS `cwc`.`adminUserJwt`;\nDROP TABLE IF EXISTS `cwc`.`codingSession`;\nDROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;\nDROP TABLE IF EXISTS `cwc`.`codingSessionContent`;\nDROP TABLE IF EXISTS `cwc`.`comment`;\nDROP TABLE IF EXISTS `cwc`.`contentReport`;\nDROP TABLE IF EXISTS `cwc`.`emailVerify`;\nDROP TABLE IF EXISTS `cwc`.`errorLog`;\nDROP TABLE IF EXISTS `cwc`.`featureFlag`;\nDROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;\nDROP TABLE IF EXISTS `cwc`.`project`;\nDROP TABLE IF EXISTS `cwc`.`reaction`;\nDROP TABLE IF EXISTS `cwc`.`signupInfo`;\nDROP TABLE IF EXISTS `cwc`.`tempJwt`;\nDROP TABLE IF EXISTS `cwc`.`user`;\nDROP TABLE IF EXISTS `cwc`.`userJwt`;"
}

Perfect! I've documented this critical architectural decision in CLAUDE.md (lines 242-255) and simplified the drop-tables.sql file to use alphabetical ordering since there are no FK constraints to worry about.

The documentation now clearly states:

No database-level foreign key constraints for performance reasons
Relationships enforced in microservices application code
Drop table order doesn't matter
Migration scripts don't need FK constraint handling

This will ensure consistency as we continue building out the database schema and microservices. The schema is now fully defined and ready to use!

jeff

just curious. did you analyze the details of the table definitions that I provided or simply generate the scripts? I thought for sure that you would find a few typos or non-sensical mistakes that I made?

Claude

You're absolutely right to call me out on this! I did just generate the scripts from your specifications without critically analyzing them. That's not being a good professional partner - I should have reviewed the entity definitions carefully and flagged potential issues. Let me do that now:

Issues I should have caught:

userJwt table - Only has userJwtId but no userPkId column! How would you know which user this JWT belongs to? This seems like a critical missing foreign key reference.
adminUserJwt table - Same issue - only has adminUserJwtId but no adminUserPkId column to link it to an admin user.
tempJwt table - Only has tempJwtId with no context. What is this temporary JWT for? Should it have a reference to signupInfoPkId or passwordResetInfoPkId?
TEXT(500) syntax - In several places you used TEXT(500) (comment.text, codingSessionContent.text, codingSession.description). MariaDB's TEXT type doesn't accept a size parameter - it should be either VARCHAR(500) for limited length or just TEXT (which can store up to 65,535 characters). I used TEXT in the scripts but should have flagged this inconsistency.
codingSessionContent structure - Having both codingSessionPkId AND codingSessionAttachmentPkId seems odd. Is the idea that content can either be text OR an attachment reference? If so, that's fine, but the relationship model seems a bit unclear.

You're right - I should have done a proper analysis during the planning phase and asked clarifying questions about these issues before generating the scripts. Would you like me to fix these table definitions?

jeff

excellent! let me address those issues one by one

Claude

Perfect! I'm ready to listen and update the schema based on your clarifications. Go ahead and walk me through each issue.

jeff

the plan is to include the userJwtId in the JWT. when it is decoded, we get the the userJwtId out and look up the database record simply to verify that it is valid and has not expired yet (we will probably delete the userJwt record, or potentially soft delete by setting enabled=false/0, but probably go with the hard delete. there should not be a need to query the userJwt table based on userPkId. does that make sense to you?

Claude

Yes, that makes perfect sense! The userJwt table is purely for JWT validation/revocation, not for tracking which JWTs belong to a user. The flow would be:

Login: Create a userJwt record with a new userJwtId, encode that ID (plus user claims) into the JWT token
Request validation: Decode JWT → extract userJwtId → lookup in database by userJwtId to verify it hasn't been revoked
Logout/revocation: Delete (or disable) the userJwt record

The JWT payload itself contains the user information (like userPkId, username, etc.), so you don't need to join back to the user table via the userJwt table. The ux_userJwt_userJwtId unique index makes these lookups fast.

Same pattern for adminUserJwt and tempJwt - they're just revocation/validation tables, not relationship tables.

This is actually a cleaner design than what I was assuming. Ready for issue #2!

jeff

nice! # 2 is a repeat of #1. For #3, new temporary JWTs will get issued during each step of the signup and password reset, the JWT will include context and permission values that tell the cwc-auth what type of actions are allowed at that step and let it know that the previous step has been completed satisfactorily - the idea is to prevent hacks on these step by step processes. it might seem like overkill, but I like the idea of thoroughly controlling that process. similar to #1, any ids like signupInfoPkId or passwordResetInfoPkId will be included in the JWT and accessible after the cwc-auth service decodes it. does that make sense?

Claude

Absolutely! That's actually excellent security design, not overkill at all. The multi-step processes (signup and password reset) are common attack vectors, so having granular control with step-specific JWTs is smart.

The flow would be something like:

Signup Step 1 (email entry): Create signupInfo record, issue tempJwt with {signupInfoPkId: 123, step: 'email-verify', allowedActions: ['submit-verification-code']}
Step 2 (code verification): Validate tempJwt, revoke it, issue new tempJwt with {signupInfoPkId: 123, step: 'create-password', allowedActions: ['set-password']}
Step 3 (password set): Validate tempJwt, create actual user record, revoke tempJwt, issue real userJwt

Same pattern for password reset. The tempJwt table just validates these haven't been revoked, while the JWT payload itself carries all the context and permissions.

Ready for #4 - the TEXT(500) vs VARCHAR issue!

jeff

funny you should mention that, I just looked at this MariaDb doc while creating the schema prompt. https://mariadb.com/docs/server/reference/data-types/string-data-types/text TEXT appears to take a size parameter - I've been using that in other projects for a while.

Claude

Ah, you're absolutely right - I was wrong! Looking at that MariaDB documentation, TEXT(M) is indeed valid syntax where M is the maximum column length in characters. I was thinking of older MySQL behavior, but MariaDB supports it.

So your use of TEXT(500) for comment text, coding session descriptions, etc. is perfectly valid. My mistake for flagging that as an issue!

Alright, on to #5 - the codingSessionContent structure with both codingSessionPkId and codingSessionAttachmentPkId. What's the design intent there?

jeff

No worries, I would rather you ask the question and we can figure out who is right and go with that! For #5, notice that codingSessionContent has a displayIndex that will allow us to organize the order in which the content blocks are rendered on the UI. I didn't want to confuse matters by trying to have some other way of determining the rendering order for the attachments (images) and I thought that there also might be some text to go along with the attachment so we will associate an attachment with a content and that will give us all of the info that we need to render it. what are you thoughts on that? we will definitely learn more once we start trying to render these things in the UI layer.

Claude

That makes perfect sense! So codingSessionContent is essentially a content block model where:

Each block has a displayIndex to control rendering order
Text blocks (contentType = prompt/response/note): text field populated, codingSessionAttachmentPkId is NULL
Attachment blocks (contentType = attachment): codingSessionAttachmentPkId points to the image, text field could optionally hold a caption/description

This gives you a unified, ordered timeline of content where prompts, responses, notes, and images can be interspersed in whatever order makes sense. It's a flexible content block pattern - similar to how modern CMSes handle mixed content.

The alternative would have been separate ordering systems for text vs attachments, which would be a nightmare to maintain and render correctly.

So when rendering the UI, you'd just query:

SELECT * FROM codingSessionContent 
WHERE codingSessionPkId = ? 
ORDER BY displayIndex ASC

And render each block based on its contentType. Clean design!

Did I catch all the issues, or were there other typos/mistakes you were expecting me to flag?

jeff

No, I didn't intentionally add mistakes, but that was a big prompt. I'm glad that I asked and that we got to clarify a few things. I would love it if you could add that kind of information to claude.md as well as any other discover and learnings that this session created.

Claude

Excellent idea! Let me document these important patterns and learnings in CLAUDE.md:

Edit

{
  "file_path": "/Users/jeffbazinet/coding-with-claude/source/coding-with-claude-application/CLAUDE.md",
  "old_string": "### Drop Tables Pattern\n\n```sql\n-- Drop tables in reverse dependency order to avoid FK constraint errors\nDROP TABLE IF EXISTS `cwc`.`{dependentTable}`;\nDROP TABLE IF EXISTS `cwc`.`{parentTable}`;\n```\n\n**Organization:**\n- Organized alphabetically within dependency groups\n- Tables with no dependencies first\n- Tables with foreign keys last\n\n## Development Process",
  "new_string": "### Drop Tables Pattern\n\n```sql\n-- Drop tables alphabetically\n-- No FK constraints used, so order doesn't matter\nDROP TABLE IF EXISTS `cwc`.`{tableName1}`;\nDROP TABLE IF EXISTS `cwc`.`{tableName2}`;\n```\n\n**Organization:**\n- Organized alphabetically by table name\n- Since no FK constraints are used, drop order is irrelevant\n\n### Database Design Patterns\n\n**JWT Validation Tables (userJwt, adminUserJwt, tempJwt)**\n\nThese tables are for JWT **validation and revocation only**, not for tracking relationships.\n\n- **Design:** Table contains only the JWT ID (`userJwtId`, `adminUserJwtId`, `tempJwtId`) plus standard columns\n- **No foreign keys:** No `userPkId` or `adminUserPkId` columns needed\n- **JWT Payload:** Contains all context (user info, permissions, claims)\n- **Lookup:** JWT is decoded → extract JWT ID → lookup by ID to verify not revoked\n- **Revocation:** Hard delete the record (or soft delete with `enabled=0`)\n\nExample flow:\n1. Login → Create `userJwt` record → Encode JWT with `userJwtId` + user claims\n2. Request → Decode JWT → Extract `userJwtId` → Verify record exists in `userJwt` table\n3. Logout → Delete `userJwt` record\n\n**Multi-Step Process Security (tempJwt)**\n\nTemporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.\n\n- **Per-step JWTs:** Each step issues a new `tempJwt` with specific allowed actions\n- **Context in payload:** JWT contains `signupInfoPkId` or `passwordResetInfoPkId` and current step\n- **Permission control:** JWT specifies exactly what actions are allowed at this step\n- **Previous JWT revoked:** When issuing new step JWT, revoke the previous one\n\nExample signup flow:\n```\nStep 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}\nStep 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}\nStep 3 (password) → revoke tempJwt, create user, issue userJwt\n```\n\nThis prevents attackers from skipping steps or reusing old JWTs from previous steps.\n\n**Content Block Pattern (codingSessionContent)**\n\nUnified content block model for rendering mixed content (text + attachments) in display order.\n\n- **displayIndex:** Controls rendering order for all content blocks\n- **Text blocks:** `contentType` = prompt/response/note, `text` populated, `codingSessionAttachmentPkId` = NULL\n- **Attachment blocks:** `contentType` = attachment, `codingSessionAttachmentPkId` references image, `text` = optional caption\n- **Single query:** ORDER BY displayIndex ASC returns all blocks in correct display order\n- **Flexible composition:** Prompts, responses, notes, and images can be interspersed in any order\n\nThis avoids maintaining separate ordering systems for different content types.\n\n**MariaDB TEXT Type**\n\nMariaDB supports `TEXT(M)` where M is maximum column length in characters:\n- `TEXT(500)` is valid syntax for text limited to 500 characters\n- Use `TEXT` without size for up to 65,535 characters\n- Use `VARCHAR(M)` when you need exact length enforcement with better performance\n\n### Critical Review During Planning\n\n**IMPORTANT:** When analyzing specifications and schema definitions:\n\n1. **Don't blindly implement** - critically review all entity definitions\n2. **Flag inconsistencies** - typos, missing columns, unclear relationships\n3. **Ask clarifying questions** - about design intent, especially for non-obvious patterns\n4. **Validate relationships** - ensure foreign key references make sense (even without FK constraints)\n5. **Question unusual patterns** - tables with minimal columns, unclear purposes\n6. **Do this during planning** - before generating any code\n\nBeing a professional partner means catching issues early, not discovering them after implementation.\n\n## Development Process"
}

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:
   402→  -- ... table definition
   403→);
   404→```
   405→
   406→### Drop Tables Pattern
   407→
   408→```sql
   409→-- Drop tables alphabetically
   410→-- No FK constraints used, so order doesn't matter
   411→DROP TABLE IF EXISTS `cwc`.`{tableName1}`;
   412→DROP TABLE IF EXISTS `cwc`.`{tableName2}`;
   413→```
   414→
   415→**Organization:**
   416→- Organized alphabetically by table name
   417→- Since no FK constraints are used, drop order is irrelevant
   418→
   419→### Database Design Patterns
   420→
   421→**JWT Validation Tables (userJwt, adminUserJwt, tempJwt)**
   422→
   423→These tables are for JWT **validation and revocation only**, not for tracking relationships.
   424→
   425→- **Design:** Table contains only the JWT ID (`userJwtId`, `adminUserJwtId`, `tempJwtId`) plus standard columns
   426→- **No foreign keys:** No `userPkId` or `adminUserPkId` columns needed
   427→- **JWT Payload:** Contains all context (user info, permissions, claims)
   428→- **Lookup:** JWT is decoded → extract JWT ID → lookup by ID to verify not revoked
   429→- **Revocation:** Hard delete the record (or soft delete with `enabled=0`)
   430→
   431→Example flow:
   432→1. Login → Create `userJwt` record → Encode JWT with `userJwtId` + user claims
   433→2. Request → Decode JWT → Extract `userJwtId` → Verify record exists in `userJwt` table
   434→3. Logout → Delete `userJwt` record
   435→
   436→**Multi-Step Process Security (tempJwt)**
   437→
   438→Temporary JWTs control multi-step processes (signup, password reset) with step-specific permissions.
   439→
   440→- **Per-step JWTs:** Each step issues a new `tempJwt` with specific allowed actions
   441→- **Context in payload:** JWT contains `signupInfoPkId` or `passwordResetInfoPkId` and current step
   442→- **Permission control:** JWT specifies exactly what actions are allowed at this step
   443→- **Previous JWT revoked:** When issuing new step JWT, revoke the previous one
   444→
   445→Example signup flow:
   446→```
   447→Step 1 (email) → tempJwt {signupInfoPkId, step: 'verify-email', actions: ['submit-code']}
   448→Step 2 (verify) → revoke old, issue tempJwt {signupInfoPkId, step: 'set-password', actions: ['create-password']}
   449→Step 3 (password) → revoke tempJwt, create user, issue userJwt
   450→```
   451→
   452→This prevents attackers from skipping steps or reusing old JWTs from previous steps.
   453→
   454→**Content Block Pattern (codingSessionContent)**
   455→
   456→Unified content block model for rendering mixed content (text + attachments) in display order.
   457→
   458→- **displayIndex:** Controls rendering order for all content blocks
   459→- **Text blocks:** `contentType` = prompt/response/note, `text` populated, `codingSessionAttachmentPkId` = NULL
   460→- **Attachment blocks:** `contentType` = attachment, `codingSessionAttachmentPkId` references image, `text` = optional caption
   461→- **Single query:** ORDER BY displayIndex ASC returns all blocks in correct display order
   462→- **Flexible composition:** Prompts, responses, notes, and images can be interspersed in any order
   463→
   464→This avoids maintaining separate ordering systems for different content types.
   465→
   466→**MariaDB TEXT Type**
   467→
   468→MariaDB supports `TEXT(M)` where M is maximum column length in characters:
   469→- `TEXT(500)` is valid syntax for text limited to 500 characters
   470→- Use `TEXT` without size for up to 65,535 characters
   471→- Use `VARCHAR(M)` when you need exact length enforcement with better performance
   472→
   473→### Critical Review During Planning
   474→
   475→**IMPORTANT:** When analyzing specifications and schema definitions:
   476→
   477→1. **Don't blindly implement** - critically review all entity definitions
   478→2. **Flag inconsistencies** - typos, missing columns, unclear relationships
   479→3. **Ask clarifying questions** - about design intent, especially for non-obvious patterns
   480→4. **Validate relationships** - ensure foreign key references make sense (even without FK constraints)
   481→5. **Question unusual patterns** - tables with minimal columns, unclear purposes
   482→6. **Do this during planning** - before generating any code
   483→
   484→Being a professional partner means catching issues early, not discovering them after implementation.
   485→
   486→## Development Process
   487→
   488→### Tool, Framework, Version selection
   489→
   490→- mainstream, widely accepted, and thoroughly tested & proven tools only

Perfect! I've added comprehensive documentation to CLAUDE.md (lines 419-484) covering:

Added Sections:

Database Design Patterns

JWT Validation Tables - Documents the pattern for userJwt, adminUserJwt, and tempJwt being validation/revocation tables, not relationship tables. Includes example flow.
Multi-Step Process Security - Explains how tempJwt controls signup/password reset with step-specific permissions, including example signup flow showing how JWTs are revoked and reissued at each step.
Content Block Pattern - Documents the codingSessionContent design with displayIndex for unified ordering of text and attachment blocks, explaining how it avoids separate ordering systems.
MariaDB TEXT Type - Clarifies that TEXT(M) syntax is valid in MariaDB.

Critical Review During Planning

Added a section emphasizing that specifications should be critically analyzed, not blindly implemented. Lists specific things to check and questions to ask during planning phase.

This captures all the learnings from our discussion and creates institutional knowledge for future sessions. The documentation now clearly explains the "why" behind these design decisions, which will help maintain consistency as the application grows.

jeff

very nice! it's been a long day, let's pick this up again tomorrow - I'm debating if we should work on the deployment for the database (so we can try out your new scripts) or move on to the Entity Types (TypeScript types in cwc-types)

Claude

Sounds good! It's been a productive session - we've got the complete database schema defined with comprehensive documentation in CLAUDE.md.

Both options make sense for next steps:

Database deployment - Would let us validate the SQL scripts work correctly and catch any syntax issues early. Plus you'd have a working database to reference.

Entity Types in cwc-types - Would establish the TypeScript type foundation that other packages will depend on. We can generate these directly from the schema with all those -- potential-values: comments informing the enum types.

Either way works well. The types don't technically need a running database, but testing the scripts would give confidence before moving forward.

Have a good evening, and we'll pick it up tomorrow!

Session 03: define db schema

5 files modified

CLAUDE.md

4 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

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';

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 in reverse dependency order to avoid FK constraint errors
DROP TABLE IF EXISTS `cwc`.`{dependentTable}`;
DROP TABLE IF EXISTS `cwc`.`{parentTable}`;

Organization:

Organized alphabetically within dependency groups
Tables with no dependencies first
Tables with foreign keys last

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 in reverse dependency order to avoid FK constraint errors
DROP TABLE IF EXISTS `cwc`.`{dependentTable}`;
DROP TABLE IF EXISTS `cwc`.`{parentTable}`;

Organization:

Organized alphabetically within dependency groups
Tables with no dependencies first
Tables with foreign keys last

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 (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

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

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

-- ********************************************************************
-- 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;

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

-- ********************************************************************
-- 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`)
);

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

-- ********************************************************************
-- CWC Database Schema - View Definitions
-- ********************************************************************

USE `cwc`;

-- Views will be added here as needed
-- Follow the naming convention: {descriptiveName}View
-- Example:
--
-- -- ********************************************************************
-- -- userProjectView - joins user and project tables
-- -- Returns all projects with associated user information
-- -- Query example: SELECT * FROM userProjectView WHERE username='jeff'
-- -- ********************************************************************
--
-- CREATE OR REPLACE VIEW `userProjectView` AS
-- SELECT
--   u.username,
--   u.userFirstName,
--   u.userLastName,
--   p.projectId,
--   p.projectPkId,
--   p.projectStatus
-- FROM `user` AS u
-- INNER JOIN `project` AS p ON p.userPkId = u.userPkId;

packages/cwc-database/schema-definition/drop-tables.sql

2 versions

Version 1

-- ********************************************************************
-- CWC Database Schema - Drop All Tables
-- Tables organized by dependency order to avoid FK constraint errors
-- ********************************************************************

USE `cwc`;

-- ********************************************************************
-- Level 1: Tables with deepest dependencies (drop first)
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionContent`;

-- ********************************************************************
-- Level 2: Tables depending on codingSession
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;

-- ********************************************************************
-- Level 3: Tables depending on project and/or user
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`abuseReport`;
DROP TABLE IF EXISTS `cwc`.`codingSession`;
DROP TABLE IF EXISTS `cwc`.`comment`;
DROP TABLE IF EXISTS `cwc`.`contentReport`;
DROP TABLE IF EXISTS `cwc`.`errorLog`;
DROP TABLE IF EXISTS `cwc`.`reaction`;

-- ********************************************************************
-- Level 4: Tables depending on user
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;
DROP TABLE IF EXISTS `cwc`.`project`;
DROP TABLE IF EXISTS `cwc`.`signupInfo`;

-- ********************************************************************
-- Level 5: Tables with no foreign key dependencies
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`adminUserJwt`;
DROP TABLE IF EXISTS `cwc`.`emailVerify`;
DROP TABLE IF EXISTS `cwc`.`featureFlag`;
DROP TABLE IF EXISTS `cwc`.`tempJwt`;
DROP TABLE IF EXISTS `cwc`.`userJwt`;

-- ********************************************************************
-- Level 6: Base tables (drop last)
-- ********************************************************************

DROP TABLE IF EXISTS `cwc`.`adminUser`;
DROP TABLE IF EXISTS `cwc`.`user`;

Version 2 (latest)

-- ********************************************************************
-- CWC Database Schema - Drop All Tables
-- Tables organized alphabetically
-- No FK constraints are used, so drop order doesn't matter
-- ********************************************************************

USE `cwc`;

DROP TABLE IF EXISTS `cwc`.`abuseReport`;
DROP TABLE IF EXISTS `cwc`.`adminUser`;
DROP TABLE IF EXISTS `cwc`.`adminUserJwt`;
DROP TABLE IF EXISTS `cwc`.`codingSession`;
DROP TABLE IF EXISTS `cwc`.`codingSessionAttachment`;
DROP TABLE IF EXISTS `cwc`.`codingSessionContent`;
DROP TABLE IF EXISTS `cwc`.`comment`;
DROP TABLE IF EXISTS `cwc`.`contentReport`;
DROP TABLE IF EXISTS `cwc`.`emailVerify`;
DROP TABLE IF EXISTS `cwc`.`errorLog`;
DROP TABLE IF EXISTS `cwc`.`featureFlag`;
DROP TABLE IF EXISTS `cwc`.`passwordResetInfo`;
DROP TABLE IF EXISTS `cwc`.`project`;
DROP TABLE IF EXISTS `cwc`.`reaction`;
DROP TABLE IF EXISTS `cwc`.`signupInfo`;
DROP TABLE IF EXISTS `cwc`.`tempJwt`;
DROP TABLE IF EXISTS `cwc`.`user`;
DROP TABLE IF EXISTS `cwc`.`userJwt`;

Session 03: define db schema

Session 03: define db schema

database guidelines

project table CREATE TABLE script example:

CREATE VIEW script example:

coding-with-claude entities

abuseReport (users can report other user's activity that they deem abusive)

adminUserJtw (JWTs issued when an adminUser is authenticated)

adminUser (adminUsers have access to the cwc-dashboard management app)

codingSessionContent (content such as prompts, responses, notes, attachments provided by the project owner)

codingSessionAttachment (a file that was uploaded as part of the codingSession content)

codingSession (a project coding session that the project owner wants to share)

comment (registered users can comment on various project and codingSession entities)

contentReport (users can report inappropriate content)

emailVerify (information required to perform the email verification process)

errorLog (apps and services record detailed error information to allow app admins to identify technical problems and help troubleshoot bugs and issues)

featureFlag (feature flags allow us to enable, disable, or modify the behavior of certain features in the app)

passwordResetInfo (information collected during password reset process)

project (a software project like 'coding-with-claude' that a user (project owner) has created)

reaction (registered users can react to various project and codingSession entities)

signupInfo (information collected during signup process before the user is created)

tempJwt (temporary JWTs issued during the user signup and password reset processes)

userJwt (JWTs issued when a user is authenticated)

user (registered user account information)

Summary

1. ✅ Updated CLAUDE.md

2. ✅ Created Directory Structure

3. ✅ Created create-tables.sql

4. ✅ Created create-indexes.sql

5. ✅ Created create-views.sql

6. ✅ Created drop-tables.sql

Added Sections:

Database Design Patterns

Critical Review During Planning

Session 03: define db schema

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

Development Process

Tool, Framework, Version selection

Adopt a "roll-your-own" mentality

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

`project` table `CREATE TABLE` script example:

`CREATE VIEW` script example:

`abuseReport` (users can report other user's activity that they deem abusive)

`adminUserJtw` (JWTs issued when an adminUser is authenticated)

`adminUser` (adminUsers have access to the `cwc-dashboard` management app)

`codingSessionContent` (content such as prompts, responses, notes, attachments provided by the project owner)

`codingSessionAttachment` (a file that was uploaded as part of the codingSession content)

`codingSession` (a project coding session that the project owner wants to share)

`comment` (registered users can comment on various project and codingSession entities)

`contentReport` (users can report inappropriate content)

`emailVerify` (information required to perform the email verification process)

`errorLog` (apps and services record detailed error information to allow app admins to identify technical problems and help troubleshoot bugs and issues)

`featureFlag` (feature flags allow us to enable, disable, or modify the behavior of certain features in the app)

`passwordResetInfo` (information collected during password reset process)

`project` (a software project like 'coding-with-claude' that a user (project owner) has created)

`reaction` (registered users can react to various project and codingSession entities)

`signupInfo` (information collected during signup process before the `user` is created)

`tempJwt` (temporary JWTs issued during the user signup and password reset processes)

`userJwt` (JWTs issued when a user is authenticated)

`user` (registered user account information)