Lesson 5.1 · 45 minDesigning a simple CRM

What you will have

The student has docs/crm-spec.md, a written specification of the CRM: pipeline stages, screens, data and what is left out. The three new tables lead_notes, lead_tasks and lead_status_history exist in the database, closed to everyone except the signed-in owner.

Video

The video for this lesson is not recorded yet.

Prompts used in this lesson

Part 5 — Having Claude write the spec

I'm about to build a simple CRM inside this project, in the /admin area that
already has a login. Before any code, I want a written specification so that
later lessons build against one agreed plan.

Context: read docs/brief.md for how this business handles enquiries,
docs/architecture.md, and the existing files in supabase/migrations/ so you
know the leads table and how Row Level Security was set up.

Write docs/crm-spec.md with these sections:
1. Purpose — two sentences: no lead forgotten, every lead's state known.
2. Pipeline stages — exactly these five values of leads.status: new,
   contacted, qualified, won, lost. For each, one sentence saying what must
   be true for a lead to be in it, written for this business.
3. Data model — the new tables lead_notes, lead_tasks and
   lead_status_history, each with its columns, types and a one-line reason.
   lead_notes: lead, text, author, created time. lead_tasks: lead, title,
   due date (a date without a time), completed time, created time.
   lead_status_history: lead, from status, to status, changed time, optional
   reason. Also one new column on leads: created_via with the allowed values
   form, manual, import, defaulting to form.
4. Screens — /admin (dashboard), /admin/leads, /admin/leads/<id>,
   /admin/pipeline, /admin/today: for each, its purpose in one sentence and
   what the owner can do there. Plus the navigation between them.
5. Settings — one business time zone used for every date in the CRM. Ask me
   which time zone to use.
6. Out of scope — multiple users and roles, sending email from the CRM,
   custom fields, deal amounts, automation rules.

Constraints: do not write application code or migrations yet. Keep the
document under two pages. If anything in the brief is unclear for the stage
definitions, ask me before writing.

Done means: the file exists, and you have re-read it and confirmed that every
screen and every table above appears in it.

Part 6 — The migration

Now create the database changes described in docs/crm-spec.md.

Purpose: the CRM screens built in the next lessons need these tables to exist
and to be private.

Write one new migration file in supabase/migrations/ that:
- creates lead_notes, lead_tasks and lead_status_history exactly as specified;
- links each to leads with a foreign key, so that deleting a lead also
  deletes its notes, tasks and history;
- restricts to_status and from_status to the five pipeline stages;
- adds the created_via column to leads with the default form;
- enables Row Level Security on all three new tables with the same access
  pattern the leads table got in lesson 4.7: only the signed-in owner can
  read or write; anonymous visitors get nothing.

Constraints: do not change or delete any existing data. Do not edit earlier
migration files. Do not print any keys.

Show me the migration and explain each statement in one plain sentence before
applying anything. After I approve, apply it the same way the earlier
migrations were applied, then verify: list the three tables with their
columns, confirm Row Level Security is on for each, and repeat the anonymous
read test from lesson 4.7 against each new table and against leads. Report
what each check returned.

Do along

1. Pause after Part 2. Open docs/brief.md and write, in your own words, what "qualified" means for your business. One sentence, a fact that is true or false.
2. Pause at the prompt in Part 5. Run the specification prompt. Replace nothing in the stage names; answer Claude's questions about your time zone and your stage definitions.
3. Read docs/crm-spec.md. Correct anything that does not match how you actually sell. Add to "Out of scope" anything you were tempted to ask for.
4. Pause at the prompt in Part 6. Run the migration prompt. Read the explanation before approving.
5. Open the Supabase table view and confirm the result with your own eyes.
6. Do the "Check your work" steps, then run the commit and tag commands that end the lesson.

Check your work

1. Open docs/crm-spec.md. Count the screens: five addresses under /admin plus the login. Count the stages: five. Find the "Out of scope" section. Expected: all present.
2. Open the Supabase dashboard table view. Expected: lead_notes, lead_tasks and lead_status_history are listed and empty.
3. Open the leads table. Expected: a created_via column with form in every row, and your old test leads still there.
4. Look at supabase/migrations/. Expected: one new file, and the older files unchanged (git status shows only new files).
5. Ask Claude: "Repeat the anonymous read test on lead_notes, lead_tasks and lead_status_history and show me the raw result." Expected: no rows for each.

Common problems

• Claude starts building pages. → The prompt's "no code yet" was dropped or edited. → Say: "Stop. Undo any application code. This step is only the document."
• The migration fails because a table already exists. → It was applied twice, or a table was created by hand in the dashboard. → Tell Claude the exact error and ask it to inspect the current database state before proposing a fix; do not delete tables yourself.
• The spec invents extra stages. → The brief describes a longer sales process. → Say: "Keep exactly the five stages; move the extra detail into the stage definitions."
• The anonymous test returns rows. → Row Level Security is off or a policy is too open. → Fix before any other lesson: "The anonymous test returned data from <table>. Find out why, fix the policy, run the test again."

Homework

About 25 minutes, on your own, after the lesson. No later lesson depends on it.

1. Test your stage definitions. Take five enquiries your business really received (typical ones, if you are just starting) and decide which stage each would be in today, using the definitions in docs/crm-spec.md. Where you hesitate, reword the definition; do not rename or add a stage. Deliverable: the reworded definitions in the spec. Done when: each of the five fits exactly one stage without a second thought.
2. Qualifying questions. Write the three or four questions you ask on a first call whose answers tell you whether the lead is qualified. Deliverable: a section "Qualifying questions" in docs/crm-habits.md (your own notes; create it if missing). Done when: the answers give a yes or no against your definition of qualified.
3. Where your leads live today. List every place enquiries sit right now — inbox, phone, messenger, spreadsheet, paper — with a rough count of open ones. No names. Deliverable: a section "Where my leads are now" in the same file. Done when: you can say how many open enquiries you have in total.

If a task changed files in the project, commit and push without a tag.

Save your work

git add -A
git commit -m "Lesson 5.1: CRM specification and tables for notes, tasks, status history"
git tag lesson-5.1
git push
git push --tags