Design Your Database
Set up contacts, companies, deals, and activities
Structuring Your CRM Data
A CRM is fundamentally a database with a nice interface on top. Get the data model right and the rest follows naturally. Get it wrong and you'll be fighting your own architecture for the life of the project.
The Core Tables
Give your AI tool this prompt to set up the database:
Create a Prisma schema for a CRM with these models:
Company - name, website, industry, size (small/medium/large/enterprise),
notes, createdAt, updatedAt
Contact - firstName, lastName, email, phone, title,
companyId (relation to Company), source (referral/inbound/outbound/other),
lastContactedAt, createdAt, updatedAt
Deal - title, value (decimal), stage (lead/qualified/proposal/negotiation/closed_won/closed_lost),
probability (int 0-100), companyId, contactId, expectedCloseDate,
notes, createdAt, updatedAt
Activity - type (call/email/meeting/note/task), subject, description,
contactId, dealId (optional), completedAt (optional), dueDate (optional),
createdAt
Use PostgreSQL. Add indexes on email, companyId, stage, and type.
Include a seed file with 3 companies, 8 contacts, 5 deals across
different stages, and 12 activities.Why This Structure Works
Companies have many Contacts. Contacts have many Deals. Both Contacts and Deals have Activities. This is the standard CRM relationship model and it lets you answer questions like "show me all deals for this company" or "what was the last activity on this contact?"
The stage field on Deals uses a string enum rather than a separate stages table. This is intentional — for a custom CRM, you want to be able to change stages by changing one line of code, not by running a migration. Keep it simple until you need it complex.
Custom Fields
This is where your CRM beats Salesforce. You can add any field you want to any model by editing the Prisma schema. Need to track "preferred contact method"? Add it to Contact. Need a "deal source" field? Add it to Deal. No configuration screen, no admin settings — just add the field and the AI will update the forms and displays to match.
After your schema is set up, run the migration and seed data to make sure everything works. You should be able to query contacts, deals, and activities before moving on.