Week 01 ‐ Devlog

Kickoff

As part of the Schema Foundation milestone, this week was very important in getting the project off the ground. After theorizing, it's time to set up the database and start sorting through the essential basic steps:

1. Provision database
2. Run status check
3. Get the project URL and anon/publishable API key stored somewhere safe
4. Take screenshots of dashboard, version query result
5. Upload verification screenshots to repo, add to pull requests

After setting up the database and verifying it is running, creating four custom enum types was next: space_type, growth_stage, metric_type, task_status.

SELECT typname
FROM pg_type
WHERE typname IN ('space_type', 'growth_stage', 'metric_type', 'task_status')
ORDER BY typname;

To verify that they exist after being created:

SELECT typname
FROM pg_type
WHERE typname IN ('space_type', 'growth_stage', 'metric_type', 'task_status')
ORDER BY typname;

Expected output will be the four rows. The justifications for each decision will be below:

• space_type - three valid options for current growing enviornment. ENUM is used over TEXT + CHECK because it is a self-documenting piece in the data schema. This will also help produce cleaner error messages and make it easier to track/debug
• growth_stage - lifecycle stages ordered, seven different values that will cover the full plant arc from a seed to even dormant/overwintering
• metric_type - five different sensor categories. custom will be the escape hatch for anything that is not in the list, and it is paired with a metric_label within the data_logs table
• task_status - three terminal states, instead of just marking them incomplete there is a status that is more deliberately named "skipped" so that growers can know they didn't just leave it incomplete but something that may not want to do intentionally

We verified everything is working, and lastly we will work on Issue #3 where we will create tables for public profiles, spaces, and plants. We will also add data logs to hold onto useful information and keep track of units, metrics, values, etc. And for tasks, we can track and use information in tandem to change task statuses.

Here is the SQL required to build these tables:

CREATE TABLE public.profiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name  TEXT,
  location_zip  TEXT,
  units         TEXT NOT NULL DEFAULT 'imperial'
                  CHECK (units IN ('imperial', 'metric')),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE public.spaces (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  name        TEXT NOT NULL CHECK (char_length(name) BETWEEN 1 AND 100),
  space_type  space_type NOT NULL DEFAULT 'outdoor',
  description TEXT,
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE public.plants (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  space_id       UUID NOT NULL REFERENCES public.spaces(id) ON DELETE CASCADE,
  user_id        UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  common_name    TEXT NOT NULL CHECK (char_length(common_name) BETWEEN 1 AND 100),
  variety        TEXT,
  planted_date   DATE,
  current_stage  growth_stage NOT NULL DEFAULT 'seed',
  is_active      BOOLEAN NOT NULL DEFAULT true,
  notes          TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE public.data_logs (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plant_id     UUID NOT NULL REFERENCES public.plants(id) ON DELETE CASCADE,
  user_id      UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  metric_type  metric_type NOT NULL,
  metric_label TEXT,
  value        NUMERIC NOT NULL,
  unit         TEXT NOT NULL,
  notes        TEXT,
  logged_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE public.tasks (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  space_id     UUID NOT NULL REFERENCES public.spaces(id) ON DELETE CASCADE,
  user_id      UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  plant_id     UUID REFERENCES public.plants(id) ON DELETE SET NULL,
  title        TEXT NOT NULL CHECK (char_length(title) BETWEEN 1 AND 200),
  due_date     DATE,
  status       task_status NOT NULL DEFAULT 'pending',
  completed_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

After running it, you will be asked to enable RLS or to run without it. We will be setting custom RLS policies, so for now we will keep it off. Supabase auto-enable option would turn RLS on without policies in place, and that would silently block ALL reads/writes. It will also break the seed and testing stages we plan to move onto after this. So for now, RUN WITHOUT RLS.

Verification Query:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

These shows all 5 tables created: data_logs, plants, profiles, spaces, and tasks.

Issues Worked On:

• https://github.com/givecoffee/firstfrostmvp/issues/1
• https://github.com/givecoffee/firstfrostmvp/issues/2
• https://github.com/givecoffee/firstfrostmvp/issues/3

Branches:

• https://github.com/givecoffee/firstfrostmvp/tree/1-provision-status-check
• https://github.com/givecoffee/firstfrostmvp/tree/schema%2F2-enums
• https://github.com/givecoffee/firstfrostmvp/tree/schema%2F3-tables

Pull Requests:

• https://github.com/givecoffee/firstfrostmvp/pull/17
• https://github.com/givecoffee/firstfrostmvp/pull/18
• https://github.com/givecoffee/firstfrostmvp/pull/19

Notes:

• Provisioned the Supabase Postgres instance and confirmed connection from local tooling.
• Locked in version check workflow so later weeks don't drift on Postgres or psql versions.

Verification/Screenshots:

Server Launched:

Verified PostgreSQL version:

Create four enum types:

Check if they exist:

Create tables without RLS on (so we can set up our own policies):

Verify the tables: