Extend the Schema and Build the Groups Page

Extend the schema

Open db/schema.sql and add two tables below the existing contacts
table:

sql

CREATE TABLE IF NOT EXISTS groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS contact_groups (
  contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  PRIMARY KEY (contact_id, group_id)
);

contact_groups is the join table: each row links one contact to one
group. Its primary key is the pair of columns, which means the same
contact can’t be linked to the same group twice. ON DELETE CASCADE on
both foreign keys means deleting a contact automatically removes its group
memberships too — you never end up with orphaned rows in contact_groups.

groups.name is UNIQUE, so you can’t accidentally create two groups
both named “Work”.

Re-run the schema — CREATE TABLE IF NOT EXISTS makes this safe even
though the contacts table already exists:

bash

npm run db:init

Add the type

Open lib/types.ts:

typescript

export type Group = {
  id: string;
  name: string;
  created_at: string;
  contact_count?: number;
};

contact_count is optional — it’s only populated by the query that lists
groups with how many contacts are in each.

Write the groups data functions

Create lib/groups.ts:

typescript

import { pool } from "./db";
import type { Group } from "./types";

export async function getGroups(): Promise<Group[]> {
  const result = await pool.query(
    `SELECT g.*, COUNT(cg.contact_id)::int AS contact_count
     FROM groups g
     LEFT JOIN contact_groups cg ON cg.group_id = g.id
     GROUP BY g.id
     ORDER BY lower(g.name) ASC`
  );
  return result.rows;
}

export async function getGroupById(id: string): Promise<Group | null> {
  const result = await pool.query(`SELECT * FROM groups WHERE id = $1`, [id]);
  return result.rows[0] || null;
}

export async function createGroup(name: string): Promise<Group> {
  const result = await pool.query(
    `INSERT INTO groups (name) VALUES ($1)
     ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
     RETURNING *`,
    [name.trim()]
  );
  return result.rows[0];
}

getGroups uses a LEFT JOIN (not a plain JOIN) so groups with zero
contacts still show up, with contact_count correctly at 0 —a plain
JOIN would silently drop them.

createGroup’s ON CONFLICT (name) DO UPDATE ... RETURNING * is a small
trick: if the group already exists, this “updates” it to the same name it
already has (a no-op) and still returns the existing row, instead of
throwing a duplicate-key error. It makes the function safe to call even if
a group with that name already exists.

Add the Server Action

Open lib/actions.ts and add:

typescript

import { createGroup } from "./groups";

export async function createGroupAction(formData: FormData) {
  const name = String(formData.get("name") || "").trim();
  if (!name) return;
  await createGroup(name);
  revalidatePath("/groups");
}

Notice there’s no redirect here — after creating a group, we want to
stay on the Groups page and just see the new group appear in the list,
not navigate away.

Build the Groups page

Replace the placeholder in app/groups/page.tsx:

tsx

import Link from "next/link";
import { getGroups } from "@/lib/groups";
import { createGroupAction } from "@/lib/actions";

export const dynamic = "force-dynamic";

export default async function GroupsPage() {
  const groups = await getGroups();

  return (
    <>
      <div className="page-heading">
        <h1>Groups</h1>
        <div className="meta">{groups.length} {groups.length === 1 ? "group" : "groups"}</div>
      </div>

      {groups.length === 0 ? (
        <div className="empty-state">
          <h2>No groups yet</h2>
          <p>Create a group to organize contacts by team, family, or however you like.</p>
        </div>
      ) : (
        <div className="groups-grid">
          {groups.map((g) => (
            <Link key={g.id} href={`/groups/${g.id}`} className="group-card">
              <div className="g-name">{g.name}</div>
              <div className="g-count">{g.contact_count} {g.contact_count === 1 ? "contact" : "contacts"}</div>
            </Link>
          ))}
        </div>
      )}

      <form action={createGroupAction} className="new-group-form">
        <input type="text" name="name" placeholder="New group name" required />
        <button type="submit" className="btn">Add group</button>
      </form>
    </>
  );
}

Test it

Visit /groups. Create a couple — “Work” and “Book Club” — using the form
at the bottom. Each should appear as a card showing “0 contacts”. Try
submitting “Work” again: thanks to ON CONFLICT, nothing breaks and no
duplicate card appears.

Checkpoint

You have a working Groups page: the schema supports many-to-many
relationships, and you can create new groups and see them listed with
accurate (currently zero) contact counts.