Before we build agents, we need the foundation they'll work with: the data model, the database, the API, and the basic schedule UI. This is the "non-AI" part of the app—the solid ground the agents will stand on.
The Plan
Defining the Data
Create a types folder in your project root with an
index.ts file:
// types/index.ts
export interface Employee {
id: number
name: string
is_on_call: boolean
}
export interface ScheduleEntry {
id: number
day_of_week: number
shift_type: string
employee_name: string | null
week_start_date: string
}
export interface ShiftSlot {
dayOfWeek: number
shiftType: string
employeeName: string | null
}
An Employee has an id, a name, and a flag for whether
they're on-call. A ScheduleEntry records who's
working which shift on which day of which week.
string | null means the employee name can be
empty—because sometimes a shift hasn't been assigned yet.
The Utility Belt
Create lib/utils.ts:
// lib/utils.ts
export const DAYS_OF_WEEK = [
'Sunday', 'Monday', 'Tuesday', 'Wednesday',
'Thursday', 'Friday', 'Saturday'
]
export const SHIFTS = [
{ type: 'opening', label: 'Opening', start: '10am', end: '4pm' },
{ type: 'mid', label: 'Mid', start: '12pm', end: '6pm' },
{ type: 'closing', label: 'Closing', start: '2pm', end: '8pm' },
]
export const SHIFTS_SATURDAY = [
{ type: 'opening', label: 'Opening', start: '10am', end: '4pm' },
{ type: 'mid', label: 'Mid', start: '12pm', end: '6pm' },
{ type: 'closing', label: 'Closing', start: '2pm', end: '9pm' },
]
export function getShiftsForDay(dayOfWeek: number) {
return dayOfWeek === 6 ? SHIFTS_SATURDAY : SHIFTS
}
// Dates are local (the store's time zone). toISOString() would convert to UTC
// and, on a San Francisco evening, return tomorrow's date.
function toYMD(d: Date): string {
const pad = (n: number) => String(n).padStart(2, '0')
return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())}`
}
export function getWeekStartDate(date?: Date): string {
const d = date ? new Date(date) : new Date()
d.setDate(d.getDate() - d.getDay()) // back to Sunday
return toYMD(d)
}
export function getDateFromDayOfWeek(
weekStart: string,
dayOfWeek: number
): Date {
// new Date('2026-11-15') would parse as UTC midnight: the day before, locally.
const [y, m, d] = weekStart.split('-').map(Number)
return new Date(y, m - 1, d + dayOfWeek)
}
export function formatDate(date: Date): string {
return date.toLocaleDateString('en-US', {
month: 'short',
day: 'numeric',
})
}
Nothing dramatic—just useful tools.
getWeekStartDate figures out what Sunday starts the
current week. getShiftsForDay returns the right shift
times, accounting for Saturday's later closing.
The Database Layer
This is the longest file in the project, but it's straightforward.
Create lib/db.ts. It has two halves: one for Neon
(production) and one for local SQLite (development). The app
automatically uses the right one based on whether
DATABASE_URL exists.
// lib/db.ts
import { neon } from '@neondatabase/serverless'
// ---- Neon (production) helpers ----
function getSQL() {
const url = process.env.DATABASE_URL
if (!url) throw new Error('DATABASE_URL is not set')
return neon(url)
}
async function initializeNeonSchema() {
const sql = getSQL()
await sql`
CREATE TABLE IF NOT EXISTS employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
is_on_call BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT NOW()
)
`
await sql`
CREATE TABLE IF NOT EXISTS schedule (
id SERIAL PRIMARY KEY,
day_of_week INTEGER NOT NULL,
shift_type TEXT NOT NULL,
employee_name TEXT,
week_start_date TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(week_start_date, day_of_week, shift_type)
)
`
// Seed employees if table is empty
const existing = await sql`SELECT count(*) as cnt FROM employees`
if (Number(existing[0].cnt) === 0) {
const employees = [
{ name: 'Alvin', isOnCall: false },
{ name: 'Melissa', isOnCall: false },
{ name: 'Marcus', isOnCall: false },
{ name: 'Dylan', isOnCall: false },
{ name: 'Joel', isOnCall: false },
{ name: 'Billy', isOnCall: false },
{ name: 'Johnny Ray', isOnCall: true },
{ name: 'Carly', isOnCall: true },
]
for (const emp of employees) {
await sql`
INSERT INTO employees (name, is_on_call)
VALUES (${emp.name}, ${emp.isOnCall})
ON CONFLICT (name) DO NOTHING
`
}
}
}
That UNIQUE constraint on the schedule table is
important—it means you can't accidentally assign two people
to the same shift. The database itself enforces the rules.
Below the Neon code in lib/db.ts, add the SQLite
fallback for local development:
// ---- sql.js (local dev) helpers ----
import initSqlJs, { Database as SqlJsDatabase } from 'sql.js'
import fs from 'fs'
import path from 'path'
let localDb: SqlJsDatabase | null = null
const getDbPath = () => {
const dbDir = path.join(process.cwd(), 'data')
const dbFile = path.join(dbDir, 'schedule.db.json')
return { dbDir, dbFile }
}
function saveLocalDb() {
if (!localDb) return
const { dbDir, dbFile } = getDbPath()
if (!fs.existsSync(dbDir)) {
fs.mkdirSync(dbDir, { recursive: true })
}
const data = localDb.export()
const buffer = Buffer.from(data)
fs.writeFileSync(dbFile, JSON.stringify(Array.from(buffer)))
}
function initializeLocalSchema() {
if (!localDb) return
localDb.run(`
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
is_on_call BOOLEAN DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`)
localDb.run(`
CREATE TABLE IF NOT EXISTS schedule (
id INTEGER PRIMARY KEY AUTOINCREMENT,
day_of_week INTEGER NOT NULL,
shift_type TEXT NOT NULL,
employee_name TEXT,
week_start_date TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(week_start_date, day_of_week, shift_type)
)
`)
// Seed the same eight employees as the Neon version
const [{ values }] = localDb.exec('SELECT count(*) FROM employees')
if (values[0][0] === 0) {
const employees: [string, number][] = [
['Alvin', 0], ['Melissa', 0], ['Marcus', 0], ['Dylan', 0],
['Joel', 0], ['Billy', 0], ['Johnny Ray', 1], ['Carly', 1],
]
for (const [name, onCall] of employees) {
localDb.run('INSERT INTO employees (name, is_on_call) VALUES (?, ?)', [name, onCall])
}
}
saveLocalDb()
}
// Open the saved file if there is one, otherwise start a fresh database.
async function getLocalDb(): Promise<SqlJsDatabase> {
if (localDb) return localDb
const SQL = await initSqlJs()
const { dbFile } = getDbPath()
localDb = fs.existsSync(dbFile)
? new SQL.Database(new Uint8Array(JSON.parse(fs.readFileSync(dbFile, 'utf8'))))
: new SQL.Database()
initializeLocalSchema()
return localDb
}
One more setting: sql.js runs SQLite as WebAssembly and loads its
.wasm file at runtime. Tell Next.js not to bundle it,
in next.config.ts:
// next.config.ts
import type { NextConfig } from 'next'
const nextConfig: NextConfig = {
// sql.js loads a .wasm file from its own folder at runtime, so keep it out
// of the server bundle.
serverExternalPackages: ['sql.js'],
}
export default nextConfig
These are the functions the rest of the app (and our agents) will use:
// ---- Public API ----
// Neon when DATABASE_URL is set (on Vercel, and locally after `vercel env pull`);
// the local sql.js file otherwise.
const useNeon = Boolean(process.env.DATABASE_URL)
// Create the tables once per server instance, not on every query.
let neonReady: Promise<void> | null = null
function ensureNeonSchema() {
neonReady ??= initializeNeonSchema()
return neonReady
}
export interface ScheduleRow {
day_of_week: number
shift_type: string
employee_name: string | null
}
export async function getAllEmployees() {
if (useNeon) {
await ensureNeonSchema()
const sql = getSQL()
const rows = await sql`SELECT id, name, is_on_call FROM employees ORDER BY name`
return rows.map((r: any) => ({
id: r.id, name: r.name, is_on_call: Boolean(r.is_on_call),
}))
} else {
const db = await getLocalDb()
const result = db.exec('SELECT id, name, is_on_call FROM employees ORDER BY name')
if (result.length === 0) return []
return result[0].values.map((row: any) => ({
// SQLite stores booleans as 0 and 1
id: row[0], name: row[1], is_on_call: Boolean(row[2]),
}))
}
}
export async function getWeekSchedule(weekStartDate: string): Promise<ScheduleRow[]> {
if (useNeon) {
await ensureNeonSchema()
const sql = getSQL()
const rows = await sql`
SELECT day_of_week, shift_type, employee_name FROM schedule
WHERE week_start_date = ${weekStartDate}
ORDER BY day_of_week, shift_type
`
return rows as ScheduleRow[]
} else {
const db = await getLocalDb()
const result = db.exec(
`SELECT day_of_week, shift_type, employee_name FROM schedule
WHERE week_start_date = ? ORDER BY day_of_week, shift_type`,
[weekStartDate]
)
if (result.length === 0) return []
return result[0].values.map((row: any) => ({
day_of_week: row[0], shift_type: row[1], employee_name: row[2],
}))
}
}
// Assign (or, with null, clear) one shift. The UNIQUE constraint turns a
// second assignment to the same slot into an update instead of a duplicate.
export async function updateSchedule(
weekStartDate: string,
dayOfWeek: number,
shiftType: string,
employeeName: string | null
): Promise<void> {
if (useNeon) {
await ensureNeonSchema()
const sql = getSQL()
await sql`
INSERT INTO schedule (week_start_date, day_of_week, shift_type, employee_name)
VALUES (${weekStartDate}, ${dayOfWeek}, ${shiftType}, ${employeeName})
ON CONFLICT (week_start_date, day_of_week, shift_type)
DO UPDATE SET employee_name = EXCLUDED.employee_name, updated_at = NOW()
`
} else {
const db = await getLocalDb()
db.run(
`INSERT INTO schedule (week_start_date, day_of_week, shift_type, employee_name)
VALUES (?, ?, ?, ?)
ON CONFLICT (week_start_date, day_of_week, shift_type)
DO UPDATE SET employee_name = excluded.employee_name, updated_at = CURRENT_TIMESTAMP`,
[weekStartDate, dayOfWeek, shiftType, employeeName]
)
saveLocalDb()
}
}
The API Routes
// app/api/employees/route.ts
import { getAllEmployees } from '@/lib/db'
import { NextResponse } from 'next/server'
export const dynamic = 'force-dynamic'
export async function GET() {
try {
const employees = await getAllEmployees()
return NextResponse.json(
employees.map((emp: any) => ({
...emp,
is_on_call: Boolean(emp.is_on_call),
}))
)
} catch (error) {
console.error('Error fetching employees:', error)
return NextResponse.json({ error: 'Failed to fetch employees' }, { status: 500 })
}
}
// app/api/schedule/route.ts
import { NextRequest, NextResponse } from 'next/server'
import { getWeekSchedule, updateSchedule } from '@/lib/db'
import { getWeekStartDate } from '@/lib/utils'
export const dynamic = 'force-dynamic'
export async function GET(request: NextRequest) {
try {
const searchParams = request.nextUrl.searchParams
const weekStart = searchParams.get('weekStart') || getWeekStartDate()
const schedule = await getWeekSchedule(weekStart)
return NextResponse.json({ schedule, weekStart })
} catch (error) {
return NextResponse.json({ error: 'Failed to fetch schedule' }, { status: 500 })
}
}
export async function PUT(request: NextRequest) {
try {
const body = await request.json()
const { weekStart, dayOfWeek, shiftType, employeeName } = body
await updateSchedule(weekStart, dayOfWeek, shiftType, employeeName || null)
return NextResponse.json({ success: true })
} catch (error) {
return NextResponse.json({ error: 'Failed to update schedule' }, { status: 500 })
}
}
The Schedule UI
Create components/ShiftCell.tsx — each cell in
the schedule grid is its own component. When you click it, a
dropdown shows all employees:
// components/ShiftCell.tsx
'use client'
import { Employee } from '@/types'
interface ShiftCellProps {
dayOfWeek: number
shiftType: string
currentEmployee: string | null
employees: Employee[]
onUpdate: (dayOfWeek: number, shiftType: string, employeeName: string | null) => void
isSaturday: boolean
}
export default function ShiftCell({
dayOfWeek, shiftType, currentEmployee, employees, onUpdate, isSaturday,
}: ShiftCellProps) {
const regularEmployees = employees.filter(e => !e.is_on_call)
const onCallEmployees = employees.filter(e => e.is_on_call)
return (
<select
value={currentEmployee || ''}
onChange={(e) => onUpdate(dayOfWeek, shiftType, e.target.value || null)}
className={`w-full p-2 rounded text-sm font-medium cursor-pointer ...`}
>
<option value="">Unassigned</option>
<optgroup label="Regular">
{regularEmployees.map(emp => (
<option key={emp.id} value={emp.name}>{emp.name}</option>
))}
</optgroup>
<optgroup label="On-Call">
{onCallEmployees.map(emp => (
<option key={emp.id} value={emp.name}>{emp.name}</option>
))}
</optgroup>
</select>
)
}
Create components/ScheduleGrid.tsx — the main
grid with week navigation and shift cells for all 21 slots (3
shifts × 7 days).
// components/ScheduleGrid.tsx
'use client'
import { Fragment, useEffect, useState } from 'react'
import ShiftCell from './ShiftCell'
import { Employee } from '@/types'
import {
DAYS_OF_WEEK, SHIFTS, getShiftsForDay, getWeekStartDate,
getDateFromDayOfWeek, formatDate,
} from '@/lib/utils'
// Who works each slot this week, keyed "day-shift", e.g. "4-closing"
type Assignments = Record<string, string | null>
const slot = (day: number, shiftType: string) => `${day}-${shiftType}`
export default function ScheduleGrid() {
// Set after mount: "this week" depends on the browser's clock and time
// zone, which the server doesn't share.
const [weekStart, setWeekStart] = useState<string | null>(null)
const [employees, setEmployees] = useState<Employee[]>([])
const [assignments, setAssignments] = useState<Assignments>({})
const [error, setError] = useState<string | null>(null)
useEffect(() => {
setWeekStart(getWeekStartDate())
fetch('/api/employees').then(res => res.json()).then(setEmployees)
}, [])
useEffect(() => {
if (!weekStart) return
fetch(`/api/schedule?weekStart=${weekStart}`)
.then(res => res.json())
.then(({ schedule }) => {
const next: Assignments = {}
for (const row of schedule) {
next[slot(row.day_of_week, row.shift_type)] = row.employee_name
}
setAssignments(next)
})
}, [weekStart])
if (!weekStart) return <p className="text-stone-500">Loading schedule…</p>
// Day 7 of this week is next Sunday; day -7 is last Sunday.
const moveWeek = (weeks: number) =>
setWeekStart(getWeekStartDate(getDateFromDayOfWeek(weekStart, weeks * 7)))
// Show the change at once, then save it; undo it if the save fails.
const handleUpdate = async (
dayOfWeek: number, shiftType: string, employeeName: string | null
) => {
const key = slot(dayOfWeek, shiftType)
const previous = assignments[key] ?? null
setAssignments(a => ({ ...a, [key]: employeeName }))
setError(null)
const res = await fetch('/api/schedule', {
method: 'PUT',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ weekStart, dayOfWeek, shiftType, employeeName }),
})
if (!res.ok) {
setAssignments(a => ({ ...a, [key]: previous }))
setError('That change didn’t save. Try again.')
}
}
return (
<div className="bg-white rounded-lg border border-stone-200 p-4">
<div className="flex items-center justify-between mb-4">
<button onClick={() => moveWeek(-1)}>← Previous week</button>
<h2 className="font-semibold text-stone-800">
Week of {formatDate(getDateFromDayOfWeek(weekStart, 0))}
</h2>
<button onClick={() => moveWeek(1)}>Next week →</button>
</div>
{error && <p className="text-red-600 text-sm mb-2">{error}</p>}
<div className="grid grid-cols-8 gap-2 text-sm">
<div />
{DAYS_OF_WEEK.map((day, d) => (
<div key={day} className="font-semibold text-stone-700">
{day.slice(0, 3)}{' '}
<span className="font-normal text-stone-400">
{formatDate(getDateFromDayOfWeek(weekStart, d))}
</span>
</div>
))}
{SHIFTS.map(shift => (
<Fragment key={shift.type}>
<div className="font-medium text-stone-600">{shift.label}</div>
{DAYS_OF_WEEK.map((_, d) => {
// Saturday's closing shift runs an hour later
const hours = getShiftsForDay(d).find(s => s.type === shift.type)!
return (
<div key={d}>
<ShiftCell
dayOfWeek={d}
shiftType={shift.type}
currentEmployee={assignments[slot(d, shift.type)] ?? null}
employees={employees}
onUpdate={handleUpdate}
isSaturday={d === 6}
/>
<div className="text-xs text-stone-400 mt-1">
{hours.start}–{hours.end}
</div>
</div>
)
})}
</Fragment>
))}
</div>
</div>
)
}
The grid keeps one map of who works each slot. Picking a name updates the screen first and saves second (an optimistic update), so the grid feels instant; if the save fails, the cell goes back to what it was and an error appears.
Replace app/page.tsx:
// app/page.tsx
import ScheduleGrid from '@/components/ScheduleGrid'
export default function Home() {
return (
<main className="min-h-screen bg-stone-50 p-8">
<div className="max-w-7xl mx-auto">
<header className="mb-8">
<h1 className="text-3xl font-bold text-stone-800">Fabulosa Books</h1>
<p className="text-stone-500 mt-1">Employee Schedule</p>
</header>
<ScheduleGrid />
</div>
</main>
)
}
npm run dev
Open http://localhost:3000. You should see the
schedule grid with week navigation and 21 "Unassigned" cells.
Click any cell and pick an employee to verify the assignment
works.
The Turning Point
The schedule app works. You can click each cell, pick an employee, and build a week's schedule one shift at a time. Push it to GitHub and Vercel deploys it automatically.
But right now, filling it is manual. Twenty-one cells, twenty-one clicks, twenty-one decisions. Alvin still has to do all the thinking: Who's available? Is this fair? Is the store covered?
Let's teach the app to think.