Skip to content

Week 10 ‐ Devlog

Jump to bottom
givecoffee edited this page Jun 15, 2026 · 5 revisions

What the Season taught me

The goal was to demonstrate every concept from the course - schema design, referential integrity, indexing, JSONB, JOIN queries, and API testing, applied to a real project rather than a series of disconnected exercises.

My Role

Sole developer. Designed and built the full backend from scratch over ten weeks.

Week 10 Issues:

Week 10 Branches:

Week 10 PRs:

Time Spent: 4 hours

Work Completed

  • Provisioned a PostgreSQL instance on Supabase and designed a five-table schema: profiles, spaces, plants, data_logs, tasks
  • Deployed four custom ENUM types and justified every type choice - UUID over SERIAL, NUMERIC for sensor values, TIMESTAMPTZ for all timestamps
  • Demonstrated schema evolution with ALTER TABLE and TRUNCATE vs DROP
  • Proved referential integrity with FK violation, CASCADE delete, and ON DELETE SET NULL tests
  • Deployed three triggers: auto-updating timestamps, auto-creating profiles on signup, and setting completed_at when a task is marked done
  • Locked down all five tables with row-level security - 15 policies, users can only read and write their own data
  • Seeded 100K data_logs rows with generate_series and reduced query time from 34ms to 8.64ms with a B-Tree index - 74.6% faster, 3.9x speedup
  • Added a JSONB metadata column to plants and demonstrated structurally different data in the same column - hydro nutrient specs vs outdoor soil amendments
  • Wrote filtering and JOIN queries: ILIKE plant search, IS NULL audit, INNER JOIN for active readings, FULL OUTER JOIN that surfaced a legitimate orphaned task
  • Built a 14-request Postman collection covering auth, full CRUD across all five tables, trigger verification, and append-only enforcement

Challenges and Solutions

The biggest challenge was Supabase's email rate limiting on the free tier - couldn't create test users through the normal auth flow. Worked around it by inserting directly into auth.users via SQL and setting passwords with crypt(). Ran into the missing aud column issue that blocked the second test user login entirely. Documented it and pivoted the RLS proof to SQL simulation rather than a second Postman user.

Schema conflicts from prior seeding also required careful ordering - ENUMs had to exist before tables, tables had to be seeded before FK tests, RLS had to come after seeding so it didn't silently block the inserts.

Key Learnings

The thing that stuck most: the schema design itself determines what anomalies can exist. ON DELETE CASCADE makes orphaned data_logs impossible. ON DELETE SET NULL on tasks.plant_id produces a legitimate orphan when a plant is deleted - the FULL OUTER JOIN in Week 7 surfaced that naturally. That's a different story than StreamFlix's manufactured NULL, and a better one.

RLS failures returning 200 with an empty array instead of 403 was unexpected. That's by design in PostgREST - worth knowing before you spend time debugging what looks like a silent success.

Links

  • GitHub: github.com/givecoffee/firstfrostmvp

Clone this wiki locally