Week 03 ‐ Devlog
FK tests are not just for error catching, but we can prove that the schema is alive. Can we uproot everything, delete when needed, and try to break it? Referential integrity is about the schema enforcing its own logic. CASCADE, SET NULL behaviors not error handling but decisions that are ingrained into the design about what should survive and what shouldn't.
First, illegal insert right out of the gate to see if we can draw a FK violation aka "flag on the play" (sorry, World Cup has me excited):
INSERT INTO public.data_logs (plant_id, user_id, metric_type, value, unit)
VALUES (
'ffffffff-ffff-ffff-ffff-ffffffffffff',
'b8b9ec50-6b0a-4c59-aaaa-8effecf78903',
'ph',
7.0,
'pH'
);
Errors returned will be:
Failed to run sql query: ERROR: 23503: insert or update on table "data_logs" violates foreign key constraint "data_logs_plant_id_fkey"
DETAIL: Key (plant_id)=(ffffffff-ffff-ffff-ffff-ffffffffffff) is not present in table "plants".
We are creating different growing spaces since the app will be for both indoor, outdoor, soil, hydroponic and everything-in-between growers and enthusiasts. It would be useful to check how many plants are in the Windowsill space before deleting:
SELECT id, common_name FROM public.plants
WHERE space_id = 'aaaaaaaa-0000-0000-0000-000000000003';
After confirming the id: bbbbbbbb-0000-0000-0000-000000000004 and common_name: mint, we will delete the space entirely.
DELETE FROM public.spaces
WHERE id = 'aaaaaaaa-0000-0000-0000-000000000003';
And to check if the plant is REALLY gone:
SELECT id, common_name FROM public.plants
WHERE space_id = 'aaaaaaaa-0000-0000-0000-000000000003';
Success, no rows returned. SET NULL - this will allow us to delete a plant, and confirm the task survives after.
Checking the task before:
SELECT id, title, plant_id FROM public.tasks
WHERE plant_id = 'bbbbbbbb-0000-0000-0000-000000000003';
Deleting the plant:
DELETE FROM public.plants
WHERE id = 'bbbbbbbb-0000-0000-0000-000000000003';
And to check if the task exists after:
SELECT id, title, plant_id FROM public.tasks
WHERE title = 'Check pH levels';
The expected output is the task will still exist, despite the plant_id showing NULL. The task survives, ON DELETE SET NULL was successful and demonstrated. The task has its own ID, and its title is still intact, even when the plant reference is gone.
Now, to set in place three triggers that have three different jobs:
update_updated_at will fire before any UPDATE on profiles, spaces, plants, and tasks. Every time a row is changed in one of those tables, postgresql will automatically set updated_at to the current timestamp before it writes the change. The database will automatically handle that info rather than manually getting from the client.
handle_new_users will fire after a new row is inserted into auth.users - which should be any time a user signs up. It created a matching row in public.profiles using the same UUID and the tables stay in sync with Supabase Auth. ON CONFLICT DO NOTHING is a safety net for two users we inserted to test with. They have profile rows, so the trigger will skip them instead of throwing "duplicate key" errors.
set_task_completed_at will fire before any UPDATE on tasks. it checks the status column specifically and will update the completed_at time based on the status changing to 'complete'. If the status is changed away from complete, or someone reopens a task, it will clear the completed_at value back to NULL. the client doesn't set the completed_at directly, it will be dictated by the database and makes the timestamp more trustworthy.
This logic is set in the database, where the data lives, and these rules will "fire" or be executed no matter what client hits the API, whether it is Postman, mobile apps, etc.
- https://github.com/givecoffee/firstfrostmvp/issues/6
- https://github.com/givecoffee/firstfrostmvp/issues/7
- https://github.com/givecoffee/firstfrostmvp/tree/6-referential-integrity-tests
- https://github.com/givecoffee/firstfrostmvp/tree/schema%2F7-triggers
- https://github.com/givecoffee/firstfrostmvp/pull/23
- https://github.com/givecoffee/firstfrostmvp/pull/24
- All six verified in information_schema.triggers, four updated_at triggers, one on_auth_user_created, and one set_task_completed_at
