[Progress Logger] PR 1 — Database Schema, Migrations & RLS Policies

[Venkat-Kolasani]

⚠️ Start here. All other PRs in this feature depend on this.

---

Parent Issue: #87

Overview

Create the database foundation for the Progress Logger feature. Nothing else in this feature can be built until this PR is merged. If you are not comfortable writing SQL migrations and Supabase RLS policies, this is not the right issue for you — pick up PR 3 (Heatmap Component) instead which is purely frontend.

Exactly What to Do

Create a new migration file in supabase/migrations/ named [timestamp]_progress_logger.sql containing:

Table 1: progress_tracks

CREATE TABLE progress_tracks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  template_type TEXT NOT NULL CHECK (
    template_type IN ('leetcode', 'dev', 'system_design', 'mock', 'reading', 'custom')
  ),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
);

Table 2: progress_logs

CREATE TABLE progress_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  track_id UUID NOT NULL REFERENCES progress_tracks(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  log_date DATE NOT NULL DEFAULT CURRENT_DATE,
  did_log BOOLEAN NOT NULL DEFAULT false,
  what_did_you_do TEXT,
  what_did_you_learn TEXT,
  metadata JSONB DEFAULT '{}',
  mood TEXT CHECK (mood IN ('easy', 'moderate', 'hard')),
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(track_id, log_date)
);

Indexes to add:

CREATE INDEX idx_progress_logs_user_date ON progress_logs(user_id, log_date);
CREATE INDEX idx_progress_logs_track_id ON progress_logs(track_id);
CREATE INDEX idx_progress_tracks_user_id ON progress_tracks(user_id);

RLS Policies — enable for both tables:

ALTER TABLE progress_tracks ENABLE ROW LEVEL SECURITY;
ALTER TABLE progress_logs ENABLE ROW LEVEL SECURITY;

-- Users can only see and modify their own tracks and logs
CREATE POLICY "Users manage own tracks" ON progress_tracks
  FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "Users manage own logs" ON progress_logs
  FOR ALL USING (auth.uid() = user_id);

Acceptance Criteria

• Migration file exists in supabase/migrations/
• Both tables created with correct column types and constraints
• Indexes created on all foreign keys and frequently queried columns
• RLS enabled and policies applied on both tables
• Migration runs without errors on a fresh Supabase project
• docs/DOCUMENTATION.md updated with new table descriptions

Do Not

• Do not modify any existing tables
• Do not add any frontend or backend code in this PR
• Do not use the Supabase dashboard UI to create tables — SQL migration file only

[Yuvraj-Sarathe]

Hi @Venkat-Kolasani, I've read through the issue description for [Progress Logger] PR 1 and found it quite interesting. I'd like to be assigned to work on this task, and I'm ready to contribute towards its completion.