Week 02 ‐ Devlog
For demonstration purposes, I will be creating scratch tables that allow me to showcase results without it affecting the main FirstFrost tables. This week, we will be evolving the basic schema and doing some DDL cleanup without interfering with the main tables. I will include a demo of altering data with the scratch tables.
Creating scratch table:
CREATE TABLE scratch_demo (
id SERIAL PRIMARY KEY,
name TEXT
);
INSERT INTO scratch_demo (name)
VALUES ('tomato'), ('basil'), ('pepper');
SELECT * FROM scratch_demo;
Again, we will be running without RLS on so that we can configure that in later stages. After the table has been created, there are 3 rows returned - (ID:1, tomato), (ID:2, basil), and (ID:3, pepper). We can alter the table to add on an additional column for the planted_date:
ALTER TABLE scratch_demo
ADD COLUMN planted_date DATE;
SELECT * FROM scratch_demo;
After confirming the column has been added to the scratch table (and hopefully not the others), we can "flush" the data or TRUNCATE so that the data is gone but the structure is still intact. Could use all kinds of metaphors like filing cabinets, or even toilets, but you get the picture.
TRUNCATE TABLE scratch_demo;
SELECT * FROM scratch_demo;
We do get a warning that it can be a destructive query, since we are in deleting data in some form. However, the benefit of truncating is that we can just reuse the schema's structure but add new data. However, if we wanted to drop the table entirely, especially for demonstrative purposes, we would want to run this query:
DROP TABLE scratch_demo;
SELECT * FROM scratch_demo;
This gives an error because the second line is selecting from scratch_demo, which was JUST dropped in the line prior. DROP removed the table entirely, and all the data with it. 0 Rows returned.
Now, in order to insert baseline test rows to do some testing, we will have to create auth users in Supabase to test with (since I thought it would be more interactive than disabling the FK). We can get the UUIDs from those users created as well. Instead of inviting and using regular accounts to test, I created users directly via SQL which bypasses the normal email flow.
INSERT INTO auth.users (
id,
email,
encrypted_password,
email_confirmed_at,
created_at,
updated_at,
raw_app_meta_data,
raw_user_meta_data,
is_super_admin,
role
)
VALUES
(
gen_random_uuid(),
'rae@firstfrost.app',
crypt('password123', gen_salt('bf')),
now(),
now(),
now(),
'{"provider":"email","providers":["email"]}',
'{}',
false,
'authenticated'
),
(
gen_random_uuid(),
'test@firstfrost.app',
crypt('password123', gen_salt('bf')),
now(),
now(),
now(),
'{"provider":"email","providers":["email"]}',
'{}',
false,
'authenticated'
);
(Keep in mind, Supabase doesn't make auth.users visible from the jump. You can query it directly in the editor, though)
Afterwards, ya gotta collect the UUIDs that were generated:
SELECT id, email FROM auth.users
WHERE email IN ('rae@firstfrost.app', 'test@firstfrost.app');
In return, two rows with id and emails for both the test emails so we can run the full seed but must copy the UUIDs EXACT, any typo will result in an FK violation.
-- profiles
INSERT INTO public.profiles (id, display_name, location_zip, units)
VALUES
('b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Rae', '98057', 'imperial'),
('8d084294-3dfa-45ca-97e1-1df286c2f22f', 'Test User', '98101', 'metric');
-- spaces
INSERT INTO public.spaces (id, user_id, name, space_type, description)
VALUES
('aaaaaaaa-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Back Garden', 'outdoor', 'Raised beds along the south fence'),
('aaaaaaaa-0000-0000-0000-000000000002', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Grow Tent', 'indoor', '4x4 tent in the garage'),
('aaaaaaaa-0000-0000-0000-000000000003', '8d084294-3dfa-45ca-97e1-1df286c2f22f', 'Windowsill', 'indoor', 'South-facing kitchen window');
-- plants
INSERT INTO public.plants (id, space_id, user_id, common_name, variety, planted_date, current_stage)
VALUES
('bbbbbbbb-0000-0000-0000-000000000001', 'aaaaaaaa-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Tomato', 'San Marzano', '2026-04-01', 'flowering'),
('bbbbbbbb-0000-0000-0000-000000000002', 'aaaaaaaa-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Basil', 'Genovese', '2026-04-15', 'vegetative'),
('bbbbbbbb-0000-0000-0000-000000000003', 'aaaaaaaa-0000-0000-0000-000000000002', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'Northern Lights', 'Auto', '2026-05-01', 'vegetative'),
('bbbbbbbb-0000-0000-0000-000000000004', 'aaaaaaaa-0000-0000-0000-000000000003', '8d084294-3dfa-45ca-97e1-1df286c2f22f', 'Mint', 'Spearmint', '2026-03-10', 'harvest');
-- data_logs
INSERT INTO public.data_logs (plant_id, user_id, metric_type, metric_label, value, unit, notes)
VALUES
('bbbbbbbb-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'ph', null, 6.8, 'pH', 'Morning reading'),
('bbbbbbbb-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'temperature', null, 72.0, 'F', 'Soil temp'),
('bbbbbbbb-0000-0000-0000-000000000003', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'ec', null, 1.4, 'mS/cm', 'Nutrient solution'),
('bbbbbbbb-0000-0000-0000-000000000003', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'ph', null, 5.9, 'pH', 'After top-up');
-- tasks (one references a plant that will be deleted in Issue #6)
INSERT INTO public.tasks (space_id, user_id, plant_id, title, due_date, status)
VALUES
('aaaaaaaa-0000-0000-0000-000000000001', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'bbbbbbbb-0000-0000-0000-000000000001', 'Feed tomatoes', '2026-06-20', 'pending'),
('aaaaaaaa-0000-0000-0000-000000000002', 'b8b9ec50-6b0a-4c59-aaaa-8effecf78903', 'bbbbbbbb-0000-0000-0000-000000000003', 'Check pH levels', '2026-06-18', 'pending'),
('aaaaaaaa-0000-0000-0000-000000000003', '8d084294-3dfa-45ca-97e1-1df286c2f22f', 'bbbbbbbb-0000-0000-0000-000000000004', 'Harvest mint', '2026-06-15', 'pending');
I noticed after running these, that although it shows no rows returned because of the visibility of the table, it still hasn't had all the custom RLS policies set up. However, we can verify row counts using this:
SELECT 'profiles' AS table_name, COUNT(*) FROM public.profiles
UNION ALL
SELECT 'spaces', COUNT(*) FROM public.spaces
UNION ALL
SELECT 'plants', COUNT(*) FROM public.plants
UNION ALL
SELECT 'data_logs',COUNT(*) FROM public.data_logs
UNION ALL
SELECT 'tasks', COUNT(*) FROM public.tasks;
Output returned (had to remember that Supabase makes this lowercased at all times):
profiles - 2 spaces - 3 plants - 4 data_logs - 4 tasks - 3
All five tables seeded and verified. Milestone 1 on the GitHub Issues tracking tool is officially completed with five major issues closed (#1-5) in total.
- https://github.com/givecoffee/firstfrostmvp/issues/4
- https://github.com/givecoffee/firstfrostmvp/issues/5
- https://github.com/givecoffee/firstfrostmvp/tree/schema%2F4-evolution-demo
- https://github.com/givecoffee/firstfrostmvp/tree/5-seed-test-data
- https://github.com/givecoffee/firstfrostmvp/pull/20
- https://github.com/givecoffee/firstfrostmvp/pull/21
- https://github.com/givecoffee/firstfrostmvp/pull/22
- TRUNCATE cleared the data, DROP removed the table entirely.
- The error code 42P01 is PostgreSQL saying there is a missing relation.
