Calda Academy
Home Assignments

Assignment #1

Backend assignment on Thursday 14.5 - finish the work started on Days 2 and 3.

In the previous days you set up Supabase, modeled the database, implemented auth profile handling, started RLS, and introduced Edge Functions. Use the work-from-home day to finish the backend so the frontend team can bind to stable data and API behavior.

Goals

By the end of the day the following should be done:

Database schema and migrations

  • All tables and enums from your dbdiagram design are implemented in SQL.
  • Foreign keys, primary keys, unique constraints and cascading deletes are correct.
  • created_at / updated_at fields exist where they are useful.
  • Local migrations are generated and pushed to the remote Supabase project.

RLS policies

  • RLS is enabled on every public table.
  • Shared puzzle content is readable by authenticated users.
  • User-owned data, like profiles and attempts, is only readable or writable by the owner.
  • RLS is tested with two different users.

Views and PostgreSQL functions

  • user_weekly_attempts view works and uses with (security_invoker = true).
  • Function for fetching the full puzzle payload is implemented and tested.
  • Function for counting solvers before the current user is implemented and tested.
  • Views and functions do not expose data that RLS should protect.

Edge Functions

  • _shared/response-helpers.ts exists and is reused by Edge Functions.
  • Daily email notification function is implemented and can be called manually.
  • AI hint function is implemented and returns a hint for a puzzle group.
  • Required secrets are configured in Supabase.

Cron and data import

  • Required PostgreSQL extensions are enabled.
  • Daily email function is connected to a cron/HTTP schedule or prepared for scheduling.
  • Puzzle seed data is imported into the database.
  • Imported data is checked in the dashboard and through API queries.

Testing and documentation

  • Auth flow creates a users_data row automatically.
  • API requests are tested with authenticated user tokens, not only the service role.
  • Main queries needed by the frontend return the expected shape.
  • Any blocker is written down with the failing request, expected behavior, actual response and relevant logs.

Do not ship backend changes that only work with the service role key. The frontend will use the anon key plus the logged-in user's access token, so RLS must work in that setup.

Example puzzle response

select public.get_puzzle('088bfb8c-f880-4d79-985f-1fdbdb6e431f')
{
  "id": "088bfb8c-f880-4d79-985f-1fdbdb6e431f",
  "published_at": "2026-05-26",
  "mistakes": null, // number of mistakes the user that is calling the function already made
  "groups": [
    {
      "id": "edc41eed-0f6d-48af-9e88-b790cf41e2d1",
      "name": "___ top",
      "difficulty": "EXPERT",
      "resolved": false, // did the user that is calling the function already solve the group
      "words": [
        {
          "id": "43502adb-b528-4ef8-bb03-ba37870404cb",
          "word": "LAP"
        },
        {
          "id": "71c82b0f-824d-4de9-a7de-ca1035a934f1",
          "word": "TANK"
        },
        {
          "id": "1d45bf48-e9ef-4eaf-af76-456188a2f73c",
          "word": "DESK"
        },
        {
          "id": "0dbc366b-50f7-4787-9075-4ced3c277614",
          "word": "TABLE"
        }
      ]
    },
    {
      "id": "6a9192f0-4a54-4bb1-b8e5-df65ce151a10",
      "name": "Types of wine grape",
      "difficulty": "HARD",
      "resolved": false,
      "words": [
        {
          "id": "a0be568a-504c-4a13-9667-6b9135a82cca",
          "word": "MERLOT"
        },
        {
          "id": "f64cbaa5-1999-4f01-b524-397b717d3aa0",
          "word": "RIESLING"
        },
        {
          "id": "8c1db3ae-1005-4814-ab82-07bc3668634b",
          "word": "MALBEC"
        },
        {
          "id": "1f32eec4-dd63-477f-9aa0-cefef4f15a83",
          "word": "VIOGNIER"
        }
      ]
    },
    {
      "id": "6ffa448c-5dde-4b51-b7a3-27e42df09be1",
      "name": "___ club",
      "difficulty": "MEDIUM",
      "resolved": false,
      "words": [
        {
          "id": "e79d62c1-d1cd-4ecc-bfc9-3c1555a386b0",
          "word": "BOOK"
        },
        {
          "id": "6a5471c8-4a54-4df6-ad19-e231217ef9b6",
          "word": "NIGHT"
        },
        {
          "id": "9e33d14c-c031-4370-ad78-fb2d6aa9fdb6",
          "word": "GOLF"
        },
        {
          "id": "549af89a-fb9f-4e6a-9369-760e32d5c1b7",
          "word": "FAN"
        }
      ]
    },
    {
      "id": "e46c719e-7645-4212-94f8-7ca1c57d3e22",
      "name": "Shades of brown",
      "difficulty": "EASY",
      "resolved": false,
      "words": [
        {
          "id": "e60e116d-e5b6-42bb-9674-7f886f011106",
          "word": "UMBER"
        },
        {
          "id": "eec2235d-23ef-41bd-b046-b5817bd32871",
          "word": "SIENNA"
        },
        {
          "id": "a4bf7862-f262-4ad4-a703-29fac31eb3ef",
          "word": "OCHRE"
        },
        {
          "id": "7c5b0f95-c676-47f4-9ef5-4f1050daedaa",
          "word": "TAUPE"
        }
      ]
    }
  ]
}

If you get stuck

On this page

GoalsExample puzzle responseIf you get stuck