Relationships & Queries — Building with Lovable

Lesson 11 built one table. Real apps have several, and the interesting behaviour lives in the wires between them. This lesson builds a personal task manager: projects contain tasks, and tasks can carry many tags. Same bouncer as before — just wearing a longer coat.

Part 1 · Why relationships exist

The naïve version of this app puts everything in one table: each row is a task, and columns hold the project name, the colour, the tags as a comma-separated string. It works — for a weekend. Then you rename a project and 47 rows are out of sync, filter by tag and the query is a substring hack, or delete a task and orphan data lingers.

Avoid

One giant table

Project name repeated on every task. Tags glued together as "urgent,design,q4". Renaming or counting is a nightmare.

One table per idea

projects, tasks,tags, plus a small join table. Each fact lives in exactly one place.

The rule of thumb

Every distinct thing in your app gets its own table. Every connection between things is either a foreign key column or a join table.

Part 2 · One-to-many: projects → tasks

"One project has many tasks; each task belongs to exactly one project." That's a one-to-many. The mechanic is a foreign key column on the "many" side —project_id on tasks — pointing at the parent's id.

Figure 1.1

Three tables, one join table. Arrows point from the foreign key to the id it references.

Prompt the shape, not the SQL:

Add a projects table (name, color) and a tasks table (title, done, due_at) where each task belongs to one project. Deleting a project should delete its tasks. Both tables scope to the signed-in user with RLS.

Figure 1.2

Cascade delete. Removing a project takes its tasks with it — no orphan rows.

Part 3 · Many-to-many: tasks ↔ tags

A task can have many tags. A tag can be on many tasks. That's symmetric — neither side owns the other. The pattern is a third table, called a join table, that stores the pairings.

Figure 1.3

task_id	tag_id	Meaning
t_101	g_urgent	Task 101 is urgent
t_101	g_design	Task 101 is also design
t_102	g_urgent	Task 102 is urgent
t_103	g_q4	Task 103 is for Q4

task_tags is nothing but pairs. The primary key is the pair itself, so duplicates are impossible.

Why not a tags array column on tasks? It breaks filtering, counting, renaming, and RLS — a real table is worth the extra file.
The composite (task_id, tag_id) primary key means "the same tag can't be applied twice" is enforced by the database, not your code.
Cascade delete on both sides: remove a tag and every pairing disappears; remove a task and the same.

Part 4 · Querying across tables

Cloud lets you fetch related rows in one round trip. Ask for "projects, with their tasks, with their tags" and you get a nested JSON tree back — no client-side stitching.

Figure 1.4

Request

projects
  ├─ tasks
  │    ├─ tags
  │    └─ order by due_at
  └─ order by name

Response shape

[
  { name: "Website",
    tasks: [
      { title: "Hero copy",
        tags: [{ label: "urgent" }] },
      { title: "Pricing page",
        tags: [] },
    ]
  },
  ...
]

One request, three levels. The response mirrors the shape of the ask.

Load all my projects, each with its tasks ordered by due_at ascending, and each task with its tags. Paginate projects 10 at a time.

Filter through the join

To answer "show me every task tagged urgent", filter tasks whose id appears in atask_tags row where tag_id = 'g_urgent'. Prompt the agent for that filter — don't try to do it in JavaScript after the fetch.

Part 5 · RLS across joins

The bouncer from Lesson 11 works the same way, but now there are four doors. The rule of thumb: every table needs its own policy, and child tables check ownership through the parent.

Figure 1.5

task_tags has no user_id of its own. Its policy asks the task table: 'is this task owned by the caller?'

projects and tasks use auth.uid() = user_id — same as notes.
tags uses the same pattern (tags are per-user in this app).
task_tags uses an EXISTS subquery against tasks — the caller may only pair rows they own.

Add RLS to projects, tasks,tags, and task_tags so a user can only see and modify their own rows. For task_tags, verify ownership through the parent task.

Part 6 · UI patterns

With the schema and policies in place, the app assembles itself. Three regions, one screen:

1Sidebar
Projects list with counts and a colour dot; '+ New project' pinned at the bottom.
2Task list
Tasks under the selected project, grouped by done/undone, with tag chips inline.
3Filter
Tag dropdown at the top; picking one narrows tasks across all projects.
4Editor
Click a task to open a drawer for title, due date, and tag chips (add/remove).

Figure 1.6

tasks.lovable.app

Website

Tag: urgent ▾

Hero copy final pass

urgentdesign

Pricing page hierarchy

design

Set up analytics events

urgent

The full task manager: projects, tasks, tag chips, and a global tag filter.

Relationships cheat sheet

Which relationship do I need?

One-to-one — a profile row per user. FK with a UNIQUE constraint on the child.
One-to-many — projects → tasks. FK column on the "many" side.
Many-to-many — tasks ↔ tags. Third table with two FKs and a composite PK.

Quick prompts

"Deleting a project should delete its tasks" → ON DELETE CASCADE.
"A tag can't be applied twice to the same task" → composite PK on the join table.
"Load projects with their tasks and tag labels" → nested select in one query.
"task_tags belongs to whoever owns the task" → RLS via EXISTS.

Debug checklist

Nested query returns empty children → the child table's SELECT policy is missing.
Insert into join table fails silently → EXISTS policy denied it; the task isn't owned by the caller.
Orphan tasks after deleting a project → cascade wasn't set; add it in a follow-up migration.

Next up

Lesson 13 tackles the third pillar of Cloud: file storage. You'll build an image gallery with drag-and- drop uploads and per-user access — same bouncer, but the rows are now files.

PreviousDatabase Basics Next File Storage & Uploads