WDD 430 | Assignment

W03 Assignment: Sacrament Meeting Planner

Overview

Build on your Sacrament Meeting Planner application by replacing the in-memory data module with a live PostgreSQL database, organizing your routes using route groups, and adding URL-based search and pagination to the meetings list. By the end of this assignment, your app will persist real meeting data across server restarts and deployments, support filtered queries driven by the URL, and scale cleanly.

Requirements

This week's assignment adds three capabilities to the Sacrament Meeting Planner you built in Week 02:

Meeting data is stored in a live PostgreSQL database (Neon on Vercel) and persists across server restarts and deployments.
Public (member-facing) and admin (leader-facing) routes are separated using Next.js route groups, each with their own layout and scoped loading states.
The meetings list supports URL-based text search (filtering by speaker, presiding, conducting, or meeting type) and pagination (five meetings per page), with all state tracked in the URL.

Instructions

Leveraging AI

You may use AI to ask conceptual questions, diagnose errors, and generate small snippets. You are responsible for understanding, testing, and explaining all code in your submission.

Review the course AI Policy.

Continue on your existing branch

Open your sacrament-meetings project in VS Code and confirm you are on the peer-code-review branch:
```
git branch
```
Pull any remote changes so your local copy is up to date:
```
git pull origin peer-code-review
```
All Week 03 commits go on this same branch. Your existing open Pull Request will update automatically when you push.

You do not need a new branch or a new Pull Request for Week 03. Pushing to peer-code-review appends to the existing diff, which is what your peer reviewer will evaluate.

Review and address your peer's feedback

Before writing any new code, open your own pull request and read through what your teammate wrote during the Week 3 code review. Addressing feedback first keeps the commit history clean and prevents you from building on top of known problems.

Navigate to your sacrament-meetings repository on GitHub and open the pull request you created in Week 02.
Click the Files changed tab and read every inline comment your reviewer left. Then check the Conversation tab for any summary comments.
For each comment, decide on a response:
- Valid issue — make the fix in VS Code, commit it to peer-code-review, and push. The Pull Request updates automatically.
- Disagree or unsure — reply on the thread with a polite explanation of your reasoning. You do not have to accept every suggestion, but you must respond.
- Question — post a reply asking for clarification. If the reviewer is unavailable, note it in your Canvas reflection.
After fixing a comment, click Resolve conversation on that thread so the reviewer can see it has been addressed.

If you made any fixes, write a clear commit message that references what was changed:

git add .
git commit -m "Address peer review feedback: fix [brief description]"
git push origin peer-code-review

Why do this before adding new features? On professional teams, reviewers expect authors to address feedback before the Pull Request evolves further. Resolving comments first keeps the diff focused, avoids re-reviewing old issues mixed with new code, and signals that you take the review process seriously.

If your reviewer has not yet submitted their review, proceed with the steps below and check back before you submit. You can always add a follow-up commit to address late feedback — the Pull Request will update automatically.

Provision a PostgreSQL database on Vercel

Open your sacrament-meetings project on vercel.com.
In the left sidebar, click Storage, then click Create Database.
Select Neon (Serverless Postgres) and click Continue.
Choose the recommended database region closest to your location.
Leave Auth unselected and confirm the free hobby-tier plan is selected.
Give the database a name (for example, sacrament-meetings-db) and click Create.
When provisioning finishes, click Connect Project. Vercel automatically adds the database environment variables to your project.

Pull environment variables for local development

Install the Vercel CLI if you have not already:
```
npm install -g vercel
```
Link your local project and pull the credentials to a .env.local file:
```
vercel link
vercel env pull .env.local
```
Confirm that .env.local contains a DATABASE_URL variable before continuing.

Never commit .env.local to Git. Confirm it is listed in your .gitignore before your next commit — exposing database credentials is a serious security risk.

Install the Neon serverless client

Install the client package:
```
npm install @neondatabase/serverless
```

Create the meetings table and seed data

In your Vercel project dashboard, navigate to Storage → your database → Query.

Run the following SQL to create the meetings table. The schema uses JSONB for the nested objects (hymns, speakers, ward business) and TEXT[] for announcements:

CREATE TABLE IF NOT EXISTS meetings (
  id             SERIAL        PRIMARY KEY,
  date           DATE          NOT NULL UNIQUE,
  meeting_type   VARCHAR(20)   NOT NULL
                               CHECK (meeting_type IN
                                 ('testimony','regular','stake','general','special')),
  presiding      VARCHAR(255)  NOT NULL,
  conducting     VARCHAR(255)  NOT NULL,
  announcements  TEXT[]        DEFAULT '{}',
  opening_hymn   JSONB         NOT NULL,
  opening_prayer VARCHAR(255)  NOT NULL,
  ward_business  JSONB         DEFAULT '[]',
  stake_business BOOLEAN       DEFAULT false,
  sacrament_hymn JSONB         NOT NULL,
  speakers       JSONB         DEFAULT '[]',
  closing_hymn   JSONB         NOT NULL,
  closing_prayer VARCHAR(255)  NOT NULL
);

JSONB stores structured data (objects and arrays) as binary JSON. It supports efficient querying and is a practical choice for nested fields like hymns and speaker lists that aren't queried relationally.

Seed the table with at least 8 meeting records so you have enough data to test pagination. Vary the meeting_type values and use different speaker names so search filtering is verifiable:

Example: January 2026 Seed – INSERT (expand to 8+)

INSERT INTO meetings (
  date, meeting_type, presiding, conducting, announcements,
  opening_hymn, opening_prayer, ward_business, stake_business,
  sacrament_hymn, speakers, closing_hymn, closing_prayer
) VALUES
(
'2026-01-04','testimony','Bishop Thompson','Brother Nakamura',
ARRAY[]::TEXT[],
'{"number":134,"title":"I Believe in Christ"}','Sister Park',
'[]',false,
'{"number":175,"title":"God, Our Father, Hear Us Pray"}',
'[]',
'{"number":219,"title":"Because I Have Been Given Much"}','Brother Alvarez'
),
(
  '2026-01-11','regular','Bishop Thompson','Brother Nakamura',
  ARRAY['Ward temple night: Jan 30'],
  '{"number":2,"title":"The Spirit of God"}','Sister Ramirez',
  '[{"description":"Sustaining of new Sunday School president"}]',true,
  '{"number":169,"title":"In Remembrance of Thy Suffering"}',
  '[{"name":"Sister Chen","topic":"The Sacrament","type":"speaker"},
    {"name":"Brother Osei","topic":"Covenant Keeping","type":"speaker"}]',
  '{"number":31,"title":"O God, Our Help in Ages Past"}','Brother Lewis'
),(
  '2026-01-18','regular','Bishop Thompson','Sister Torres',
  ARRAY['Ministering interviews this week'],
  '{"number":85,"title":"How Firm a Foundation"}','Brother Kim',
  '[{"description":"Release - Sister Martinez - Primary Teacher"},{"description":"Sustain - Sister Agbavor - Primary Teacher"}, {"description":"Sustain - Sister Mukiwa - RS 2nd Counselor"}]',false,
  '{"number":173,"title":"While of These Emblems We Partake"}',
  '[{"name":"Sister Nakamura","topic":"Personal Revelation","type":"speaker"},
    {"name":"Youth Choir","topic":"","type":"musical-number"},
    {"name":"Brother Santos","topic":"Temple Covenants","type":"speaker"}]',
  '{"number":226,"title":"Improve the Shining Moments"}','Sister Jensen'
),(
  '2026-01-25','stake','President Gimenez','',
  ARRAY[]::TEXT[],
  '','',
  '[]',true,
  '',
  '[]',
  '',''
);

Replace the in-memory module with database queries

Open lib/meetings-db.ts and replace its entire contents with live SQL queries. The read functions (getMeetings and getMeetingById) need to be updated now. The mutation functions (addMeeting, updateMeeting, deleteMeeting) will be wired to the database in Week 04 when forms are built — leave their signatures in place as stubs.

You will need to import the neon client and the SacramentMeeting type.

Example Updated: lib/meetings-db.ts

import { neon } from '@neondatabase/serverless';
import type { SacramentMeeting } from './types';

const sql = neon(process.env.DATABASE_URL!);

const ITEMS_PER_PAGE = 5;

export async function getMeetings(
  query: string = '',
  currentPage: number = 1
): Promise<SacramentMeeting[]> {
  const searchTerm = `%${query}%`;
  const offset = (currentPage - 1) * ITEMS_PER_PAGE;

  const rows = await sql`
    SELECT
      id,
      to_char(date, 'YYYY-MM-DD') AS "date",
      meeting_type                AS "meetingType",
      presiding, conducting, announcements,
      opening_hymn                AS "openingHymn",
      opening_prayer              AS "openingPrayer",
      ward_business               AS "wardBusiness",
      stake_business              AS "stakeBusiness",
      sacrament_hymn              AS "sacramentHymn",
      speakers,
      closing_hymn                AS "closingHymn",
      closing_prayer              AS "closingPrayer"
    FROM meetings
    WHERE
      presiding     ILIKE ${searchTerm}
      OR conducting ILIKE ${searchTerm}
      OR meeting_type ILIKE ${searchTerm}
      OR speakers::text ILIKE ${searchTerm}
    ORDER BY date DESC
    LIMIT ${ITEMS_PER_PAGE} OFFSET ${offset}
  `;
  return rows as unknown as SacramentMeeting[];
}

export async function getMeetingsTotalPages(
  query: string = ''
): Promise<number> {
  const searchTerm = `%${query}%`;
  const rows = await sql`
    SELECT COUNT(*) FROM meetings
    WHERE
      presiding     ILIKE ${searchTerm}
      OR conducting ILIKE ${searchTerm}
      OR meeting_type ILIKE ${searchTerm}
      OR speakers::text ILIKE ${searchTerm}
  `;
  return Math.ceil(Number(rows[0].count) / ITEMS_PER_PAGE);
}

export async function getMeetingById(
  id: number
): Promise<SacramentMeeting | null> {
  const rows = await sql`
    SELECT
      id,
      to_char(date, 'YYYY-MM-DD') AS "date",
      meeting_type                AS "meetingType",
      presiding, conducting, announcements,
      opening_hymn                AS "openingHymn",
      opening_prayer              AS "openingPrayer",
      ward_business               AS "wardBusiness",
      stake_business              AS "stakeBusiness",
      sacrament_hymn              AS "sacramentHymn",
      speakers,
      closing_hymn                AS "closingHymn",
      closing_prayer              AS "closingPrayer"
    FROM meetings WHERE id = ${id}
  `;
  return (rows[0] as unknown as SacramentMeeting) ?? null;
}

// Mutation stubs — will be wired to the database in Week 04
export async function addMeeting(
  data: Omit<SacramentMeeting, 'id'>
): Promise<SacramentMeeting> {
  throw new Error('addMeeting: database implementation coming in Week 04');
}

export async function updateMeeting(
  id: number,
  updates: Partial<SacramentMeeting>
): Promise<SacramentMeeting | null> {
  throw new Error('updateMeeting: database implementation coming in Week 04');
}

export async function deleteMeeting(id: number): Promise<boolean> {
  throw new Error('deleteMeeting: database implementation coming in Week 04');
}

The neon() function from @neondatabase/serverless returns a tagged template literal that automatically parameterizes every interpolated value — making SQL injection impossible. Never build SQL strings using string concatenation with user input.

Ensure all exported function signatures stay compatible with what your API route handlers already call — the goal is to replace the implementation without touching the routes.

Update API route handlers to await database calls

The data functions in lib/meetings-db.ts are now async. Every route handler that calls them must await the result. Update both route files:
- app/api/meetings/route.ts — await getMeetings() in the GET handler.
- app/api/meetings/[id]/route.ts — await getMeetingById(id) in the GET handler.
Also update any Server Component pages that call these functions directly (For example, app/meetings/page.tsx and app/meetings/[id]/page.tsx) to await the new async calls.
Run npm run build and fix any TypeScript errors before continuing.

Organize routes with route groups

Route groups use parenthesized folder names — (public), (admin) — that organize files without appearing in URLs. Restructure your app/ folder to separate member-facing pages from leader-facing pages:

app/
  (public)/
    meetings/
      layout.tsx      (member-facing meetings layout + navigation)
      loading.tsx     (skeleton shown while meetings list fetches)
      page.tsx        (meetings list — search + pagination)
      current/
        page.tsx      (redirect to this Sunday's meeting)
      [id]/
        page.tsx      (meeting detail + print view)
  (admin)/
    layout.tsx        (admin layout — authentication scaffolded in Week 05)
    meetings/
      new/
        page.tsx      (create meeting form — implemented in Week 04)
      [id]/
        edit/
          page.tsx    (edit meeting form — implemented in Week 04)
  api/
    meetings/
      route.ts
      [id]/
        route.ts
  layout.tsx          (root layout — unchanged)
  page.tsx            (home page — unchanged)

Moving loading.tsx inside (public)/meetings/ scopes the loading skeleton to only the meetings list routes. Without route groups, a loading.tsx at the app/ level would apply to every route in the application.

Add a placeholder page at app/(admin)/meetings/new/page.tsx with a heading "Create Meeting — Coming in Week 04" so the route exists and is navigable.
Confirm that the existing URLs (/meetings, /meetings/[id]) are unaffected by the reorganization — route group folders are invisible to the router.

Add URL-based search to the meetings list

Install the debounce library:
```
npm install use-debounce
```

Create components/MeetingSearch.tsx as a Client Component. It reads and writes the query URL parameter using useSearchParams, usePathname, and useRouter, debouncing input to avoid a database round-trip on every keystroke:

Example: components/MeetingSearch.tsx

'use client';

import { useSearchParams, usePathname, useRouter } from 'next/navigation';
import { useDebouncedCallback } from 'use-debounce';

export function MeetingSearch() {
  const searchParams = useSearchParams();
  const pathname = usePathname();
  const { replace } = useRouter();

  const handleSearch = useDebouncedCallback((term: string) => {
    const params = new URLSearchParams(searchParams);
    params.set('page', '1'); // always reset to page 1 on a new search
    if (term) {
      params.set('query', term);
    } else {
      params.delete('query');
    }
    replace(`${pathname}?${params.toString()}`);
  }, 300);

  return (
    <input
      type="search"
      placeholder="Search by speaker, leader, or meeting type..."
      defaultValue={searchParams.get('query')?.toString()}
      onChange={(e) => handleSearch(e.target.value)}
      aria-label="Search meetings"
    />
  );
}

Why defaultValue instead of value? The URL is the source of truth, not component state. defaultValue initializes the input from the URL on first render without creating a controlled input. This means the search state survives a page refresh or a shared link naturally.

Update app/(public)/meetings/page.tsx to read searchParams from its props and pass them to getMeetings():

Example: app/(public)/meetings/page.tsx

import { getMeetings, getMeetingsTotalPages } from '@/lib/meetings-db';
import { MeetingSearch } from '@/components/MeetingSearch';
import { MeetingCard } from '@/components/MeetingCard';
import { Pagination } from '@/components/Pagination';

export default async function MeetingsPage(props: {
  searchParams?: Promise<{ query?: string; page?: string }>;
}) {
  const searchParams = await props.searchParams;
  const query = searchParams?.query ?? '';
  const currentPage = Number(searchParams?.page) || 1;

  const [meetings, totalPages] = await Promise.all([
    getMeetings(query, currentPage),
    getMeetingsTotalPages(query),
  ]);

  return (
    <div>
      <MeetingSearch />
      {meetings.map((m) => (
        <MeetingCard key={m.id} meeting={m} />
      ))}
      <Pagination totalPages={totalPages} />
    </div>
  );
}

Add pagination to the meetings list

Create components/Pagination.tsx as a Client Component. It reads the current page from the URL and renders previous/next links that preserve any active search query:

Example: components/Pagination.tsx

'use client';

import Link from 'next/link';
import { usePathname, useSearchParams } from 'next/navigation';

export function Pagination({ totalPages }: { totalPages: number }) {
  const pathname = usePathname();
  const searchParams = useSearchParams();
  const currentPage = Number(searchParams.get('page')) || 1;

  function createPageURL(page: number) {
    const params = new URLSearchParams(searchParams);
    params.set('page', String(page));
    return `${pathname}?${params.toString()}`;
  }

  return (
    <nav aria-label="Pagination">
      {currentPage > 1 && (
        <Link href={createPageURL(currentPage - 1)}>Previous</Link>
      )}
      <span>Page {currentPage} of {totalPages}</span>
      {currentPage < totalPages && (
        <Link href={createPageURL(currentPage + 1)}>Next</Link>
      )}
    </nav>
  );
}

Style the pagination controls using Tailwind so previous/next links are clearly interactive and the current page number is readable.
Verify these observable behaviors before moving on:
- Typing a search term updates the URL and filters the results without a full page reload.
- Starting a new search always resets to page 1.
- Pasting the URL into a new tab restores the same filtered, paginated state.
- The browser back button returns to the previous search.

Quality and accessibility checks

Run local checks before submission:
```
npm run lint
npm run build
```
Run accessibility checks on the main user-facing routes:
- Verify keyboard navigation works for all links and the search input.
- Confirm the search input has an accessible aria-label and the pagination nav has an aria-label.
- Confirm images use meaningful alt text and headings follow a logical order.
Run Lighthouse in Chrome DevTools on the /meetings page (mobile and desktop). Address color contrast, missing labels, or incorrect semantic structure as time permits.
Use AI to review your code before submission. Ask it to focus on bugs, accessibility, incorrect routing or API behavior, type-safety problems, and missing assignment requirements rather than style preferences.

AI Example prompt

"Review for bugs, accessibility problems, incorrect Next.js App Router usage, API route issues, type-safety problems, and places where I may have missed the assignment requirements. Prioritize findings by severity. For each finding, include: (1) what is wrong, (2) why it matters, (3) the file or route involved, and (4) a specific fix. Do not praise the code or suggest optional refactors unless they are directly related to correctness, accessibility, or assignment compliance."
Manually verify these routes in the browser:
- /meetings — shows paginated list from the database
- /meetings?query=smith — filters results by that name
- /meetings?page=2 — shows the second page
- /meetings?query=regular&page=1 — combined search + pagination
- /meetings/1 (or any valid id)
- /meetings/current — redirects to this Sunday
- /api/meetings
- /api/meetings?date=2026-03-01
- /api/meetings/1
- /api/meetings/abc — should return 400

Push your work and update the team membership document

Commit and push your changes to the same peer-code-review branch:

git add .
git commit -m "W3: database, route groups, search, pagination"
git push origin peer-code-review

GitHub will automatically update the open Pull Request with the new commits — no new Pull Request is needed.

Do not merge the PR. Leave it open. Your peer reviewer will use this Pull Request for the code review.
Update your line in team-membership.docx in Microsoft Teams with the new Vercel preview URL for the peer-code-review branch. Vercel creates a new preview deployment each time you push, so the URL may have changed.

Submission Instructions

Submit the following in the text entry area in Canvas:

Project Links

GitHub repository URL for your sacrament-meetings repository.
Pull Request URL — the link to your open Pull Request on GitHub (peer-code-review → main).
Vercel preview URL for the peer-code-review branch. Find it in your Vercel project dashboard under Deployments, tagged peer-code-review. Do not submit the production URL.

Reflection

A brief summary of any challenges you encountered (database connection, SQL schema design, type mismatches, etc.) and how you used AI during the development process.