Schema & Setup — CA Compliance Tracker (Worked Example)

The two database tables, RLS strategy, and project CLAUDE.md template for the CA compliance tracker. Use this if you chose the CA tracker as your parallel project.

This page is for participants who chose the CA compliance tracker as their parallel project. If you chose a different project, use this as a reference for how to structure your own schema — the patterns (two tables, RLS, soft-delete) are the same for any project.

The most expensive mistake in any software project is discovering the wrong data model after you have already built the UI on top of it. Spend 30 minutes here understanding what you are building and why — it will save you hours of rebuilding later.

The Two Tables

Spreadsheet → DatabaseTry in Playground →|New tab

Your entire application lives in two database tables.

Table 1: `clients`

Stores the CA firms or individuals you manage.

Column	Type	Notes
`id`	`uuid`	Primary key, auto-generated
`created_at`	`timestamptz`	Auto-set on insert
`user_id`	`uuid`	The CA who owns this client — references `auth.users(id)`
`legal_name`	`text`	Full legal name of the client entity
`gstin`	`text`	15-character GSTIN (nullable — not all clients are GST-registered)
`entity_type`	`text`	"Private Limited" / "LLP" / "Partnership" / "Proprietorship" / "Individual"
`state_code`	`text`	2-character state code (e.g. "MH", "DL", "KA")
`contact_email`	`text`	Primary contact email
`contact_phone`	`text`	10-digit mobile (nullable)
`notes`	`text`	Any general notes about this client
`is_deleted`	`boolean`	Soft-delete — never hard-delete a client

Table 2: `compliance_tasks`

Stores one row per compliance obligation per client per period.

Column	Type	Notes
`id`	`uuid`	Primary key, auto-generated
`created_at`	`timestamptz`	Auto-set on insert
`client_id`	`uuid`	References `clients(id)`
`user_id`	`uuid`	Redundant with client.user_id, but simplifies RLS policies
`task_type`	`text`	"GSTR-1" / "GSTR-3B" / "ITR" / "Tax Audit" / "Statutory Audit" / "ROC Annual Return" / "Other"
`period`	`text`	Human-readable period: "Apr 2026", "FY 2025-26", "Q4 FY2025-26"
`due_date`	`date`	The statutory due date
`status`	`text`	"pending" / "in_progress" / "filed" / "overdue"
`filed_date`	`date`	Actual filing date (null until filed)
`filing_ref`	`text`	ARN / Acknowledgement number / Reference (nullable)
`notes`	`text`	Late fees, notices, special circumstances
`is_deleted`	`boolean`	Soft-delete

The relationship: One client has many compliance tasks. A compliance task belongs to one client. This is a standard one-to-many relationship.

The RLS Strategy

RLS ShieldTry in Playground →|New tab

You are the only user of your own app. The RLS policy is simple: you can only see and edit rows that belong to you.

For the clients table:

-- You can read your own clients (not deleted)
create policy "CA can read own clients"
  on clients for select
  using (auth.uid() = user_id and not is_deleted);
 
-- You can add clients for yourself
create policy "CA can create clients"
  on clients for insert
  with check (auth.uid() = user_id);
 
-- You can update your own clients
create policy "CA can update clients"
  on clients for update
  using (auth.uid() = user_id);

For the compliance_tasks table:

-- You can read your own tasks (not deleted)
create policy "CA can read own tasks"
  on compliance_tasks for select
  using (auth.uid() = user_id and not is_deleted);
 
-- You can add tasks for your clients
create policy "CA can create tasks"
  on compliance_tasks for insert
  with check (auth.uid() = user_id);
 
-- You can update your own tasks
create policy "CA can update tasks"
  on compliance_tasks for update
  using (auth.uid() = user_id);

No delete policies — you never hard-delete. You update is_deleted = true instead.

The Useful Queries

These are the three main things your app will ask the database for:

1. All my clients (for the client list page):

select * from clients
where user_id = auth.uid()
  and not is_deleted
order by legal_name asc;

2. All tasks for one client (for the client detail page):

select * from compliance_tasks
where client_id = '[client_id]'
  and user_id = auth.uid()
  and not is_deleted
order by due_date asc;

3. What's due this week (for the dashboard):

select ct.*, c.legal_name, c.gstin
from compliance_tasks ct
join clients c on ct.client_id = c.id
where ct.user_id = auth.uid()
  and ct.status != 'filed'
  and ct.due_date <= current_date + 7
  and not ct.is_deleted
order by ct.due_date asc;

You will not write these queries by hand — you will describe them to Claude and it will write the React Query hook. But understanding what the query does before asking Claude helps you verify the output is correct.

Your Project CLAUDE.md Template

When you scaffold your app in Week 3, create a CLAUDE.md at the root of your project with this content. Fill in the [brackets].

# CLAUDE.md — [Your App Name]
 
## What This App Is
A personal compliance management dashboard for my CA practice. I use it to track
my clients' GST, income tax, and audit filing deadlines — and to record when each
filing is completed.
 
Only I use this app. One user, one Supabase project, simple RLS.
 
**Supabase project ref:** `[paste from your Supabase dashboard URL]`
 
---
 
## Tech Stack
| Layer | Technology |
|-------|-----------|
| Framework | Next.js 15 (App Router) |
| Language | TypeScript (strict mode) |
| Styling | Tailwind CSS |
| Backend | Supabase (database + auth) |
| Server state | React Query |
| Forms | React Hook Form + Zod |
| Deploy | Vercel |
 
---
 
## Quick Commands
\`\`\`bash
npm run dev        # dev server at localhost:3000
npm run build      # production build
npx tsc --noEmit   # type check
\`\`\`
 
---
 
## Database Tables
| Table | Purpose |
|-------|---------|
| clients | My CA clients — name, GSTIN, entity type, state, contact |
| compliance_tasks | Filing tasks per client — type, period, due date, status, filed date |
 
---
 
## Key Business Rules
- Only I (the logged-in CA) can see or edit my own clients and tasks
- Clients and tasks are soft-deleted (is_deleted = true) — never hard-deleted
- Monetary amounts (late fees, penalties) stored as paise (integers)
- All dates in IST for display, UTC for storage
 
---
 
## Task Status Flow
pending → in_progress → filed
Any task can become "overdue" if due_date < today and status != 'filed'
Overdue is computed at query time — not stored
 
---
 
## Skill Routing
- "build, add, create" → new-feature skill
- "fix, bug, broken" → bug-fix skill
- "database, schema, migration, RLS" → supabase-migration skill
- "before demo, audit, check" → pre-launch-audit skill
 
---
 
## Decisions Made
- Overdue status is not stored — computed: due_date < today and status != 'filed'
- filing_ref stores ARN/acknowledgement numbers for filed returns
- user_id stored on compliance_tasks (denormalized) to simplify RLS without joins

Supabase Project Setup

Before Week 5 homework, you need a Supabase project for your app. It is separate from the School ERP project.

Supabase sign-in screen — log in at app.supabase.com to create your project

Go to app.supabase.com and create a new project — name it "[Your App Name] Dev"

Copy the project ref from the dashboard URL (the part after /project/)

Paste it into your project CLAUDE.md where it says [paste from your Supabase dashboard URL]

Copy the project URL and anon key from Settings → API and add them to your .env.local

The migrations are applied in Week 5 homework — you do not need to create tables manually

Create a separate Supabase project for your CA Practice Manager — do not reuse the School ERP project. Each app needs its own project so data stays isolated. The dev/prod split (two separate Supabase projects per app) is something you will learn in the School ERP and can apply to your Practice Manager after Demo Day.

The Two Tables

Spreadsheet → DatabaseTry in Playground →|New tab

Your entire application lives in two database tables.

Table 1: `clients`

Stores the CA firms or individuals you manage.

Column	Type	Notes
`id`	`uuid`	Primary key, auto-generated
`created_at`	`timestamptz`	Auto-set on insert
`user_id`	`uuid`	The CA who owns this client — references `auth.users(id)`
`legal_name`	`text`	Full legal name of the client entity
`gstin`	`text`	15-character GSTIN (nullable — not all clients are GST-registered)
`entity_type`	`text`	"Private Limited" / "LLP" / "Partnership" / "Proprietorship" / "Individual"
`state_code`	`text`	2-character state code (e.g. "MH", "DL", "KA")
`contact_email`	`text`	Primary contact email
`contact_phone`	`text`	10-digit mobile (nullable)
`notes`	`text`	Any general notes about this client
`is_deleted`	`boolean`	Soft-delete — never hard-delete a client

Table 2: `compliance_tasks`

Stores one row per compliance obligation per client per period.

Column	Type	Notes
`id`	`uuid`	Primary key, auto-generated
`created_at`	`timestamptz`	Auto-set on insert
`client_id`	`uuid`	References `clients(id)`
`user_id`	`uuid`	Redundant with client.user_id, but simplifies RLS policies
`task_type`	`text`	"GSTR-1" / "GSTR-3B" / "ITR" / "Tax Audit" / "Statutory Audit" / "ROC Annual Return" / "Other"
`period`	`text`	Human-readable period: "Apr 2026", "FY 2025-26", "Q4 FY2025-26"
`due_date`	`date`	The statutory due date
`status`	`text`	"pending" / "in_progress" / "filed" / "overdue"
`filed_date`	`date`	Actual filing date (null until filed)
`filing_ref`	`text`	ARN / Acknowledgement number / Reference (nullable)
`notes`	`text`	Late fees, notices, special circumstances
`is_deleted`	`boolean`	Soft-delete

The relationship: One client has many compliance tasks. A compliance task belongs to one client. This is a standard one-to-many relationship.

The RLS Strategy

RLS ShieldTry in Playground →|New tab

You are the only user of your own app. The RLS policy is simple: you can only see and edit rows that belong to you.

For the clients table:

-- You can read your own clients (not deleted)
create policy "CA can read own clients"
  on clients for select
  using (auth.uid() = user_id and not is_deleted);
 
-- You can add clients for yourself
create policy "CA can create clients"
  on clients for insert
  with check (auth.uid() = user_id);
 
-- You can update your own clients
create policy "CA can update clients"
  on clients for update
  using (auth.uid() = user_id);

For the compliance_tasks table:

-- You can read your own tasks (not deleted)
create policy "CA can read own tasks"
  on compliance_tasks for select
  using (auth.uid() = user_id and not is_deleted);
 
-- You can add tasks for your clients
create policy "CA can create tasks"
  on compliance_tasks for insert
  with check (auth.uid() = user_id);
 
-- You can update your own tasks
create policy "CA can update tasks"
  on compliance_tasks for update
  using (auth.uid() = user_id);

No delete policies — you never hard-delete. You update is_deleted = true instead.

The Useful Queries

These are the three main things your app will ask the database for:

1. All my clients (for the client list page):

select * from clients
where user_id = auth.uid()
  and not is_deleted
order by legal_name asc;

2. All tasks for one client (for the client detail page):

select * from compliance_tasks
where client_id = '[client_id]'
  and user_id = auth.uid()
  and not is_deleted
order by due_date asc;

3. What's due this week (for the dashboard):

select ct.*, c.legal_name, c.gstin
from compliance_tasks ct
join clients c on ct.client_id = c.id
where ct.user_id = auth.uid()
  and ct.status != 'filed'
  and ct.due_date <= current_date + 7
  and not ct.is_deleted
order by ct.due_date asc;

Your Project CLAUDE.md Template

When you scaffold your app in Week 3, create a CLAUDE.md at the root of your project with this content. Fill in the [brackets].

# CLAUDE.md — [Your App Name]
 
## What This App Is
A personal compliance management dashboard for my CA practice. I use it to track
my clients' GST, income tax, and audit filing deadlines — and to record when each
filing is completed.
 
Only I use this app. One user, one Supabase project, simple RLS.
 
**Supabase project ref:** `[paste from your Supabase dashboard URL]`
 
---
 
## Tech Stack
| Layer | Technology |
|-------|-----------|
| Framework | Next.js 15 (App Router) |
| Language | TypeScript (strict mode) |
| Styling | Tailwind CSS |
| Backend | Supabase (database + auth) |
| Server state | React Query |
| Forms | React Hook Form + Zod |
| Deploy | Vercel |
 
---
 
## Quick Commands
\`\`\`bash
npm run dev        # dev server at localhost:3000
npm run build      # production build
npx tsc --noEmit   # type check
\`\`\`
 
---
 
## Database Tables
| Table | Purpose |
|-------|---------|
| clients | My CA clients — name, GSTIN, entity type, state, contact |
| compliance_tasks | Filing tasks per client — type, period, due date, status, filed date |
 
---
 
## Key Business Rules
- Only I (the logged-in CA) can see or edit my own clients and tasks
- Clients and tasks are soft-deleted (is_deleted = true) — never hard-deleted
- Monetary amounts (late fees, penalties) stored as paise (integers)
- All dates in IST for display, UTC for storage
 
---
 
## Task Status Flow
pending → in_progress → filed
Any task can become "overdue" if due_date < today and status != 'filed'
Overdue is computed at query time — not stored
 
---
 
## Skill Routing
- "build, add, create" → new-feature skill
- "fix, bug, broken" → bug-fix skill
- "database, schema, migration, RLS" → supabase-migration skill
- "before demo, audit, check" → pre-launch-audit skill
 
---
 
## Decisions Made
- Overdue status is not stored — computed: due_date < today and status != 'filed'
- filing_ref stores ARN/acknowledgement numbers for filed returns
- user_id stored on compliance_tasks (denormalized) to simplify RLS without joins

Supabase Project Setup

Before Week 5 homework, you need a Supabase project for your app. It is separate from the School ERP project.

Supabase sign-in screen — log in at app.supabase.com to create your project

Go to app.supabase.com and create a new project — name it "[Your App Name] Dev"

Copy the project ref from the dashboard URL (the part after /project/)

Paste it into your project CLAUDE.md where it says [paste from your Supabase dashboard URL]

Copy the project URL and anon key from Settings → API and add them to your .env.local

The migrations are applied in Week 5 homework — you do not need to create tables manually

Schema & Setup — CA Compliance Tracker (Worked Example)

The Two Tables

Table 1: `clients`

Table 2: `compliance_tasks`

The RLS Strategy

The Useful Queries

Your Project CLAUDE.md Template

Supabase Project Setup

On this page

Schema & Setup — CA Compliance Tracker (Worked Example)

The Two Tables

Table 1: `clients`

Table 2: `compliance_tasks`

The RLS Strategy

The Useful Queries

Your Project CLAUDE.md Template

Supabase Project Setup

On this page