Modeling Projects in the Database

Projects on this site are rows in a projects table. Posts and docs attach to a project through the project_id column on the content table, and a single dynamic route renders a page for each project with its associated content listed underneath.

Schema

CREATE TABLE IF NOT EXISTS projects (
  id           TEXT PRIMARY KEY,
  slug         TEXT UNIQUE NOT NULL,
  title        TEXT NOT NULL,
  tagline      TEXT,
  description  TEXT,
  status       TEXT NOT NULL DEFAULT 'draft',
  icon         TEXT,
  app_href     TEXT,
  repo_url     TEXT,
  demo_url     TEXT,
  video_url    TEXT,
  thumbnail    TEXT,
  logo         TEXT,
  images       TEXT,
  tags         TEXT,
  sort_order   INTEGER NOT NULL DEFAULT 0,
  created_at   TEXT NOT NULL,
  updated_at   TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_content_project_id ON content(project_id);

The migration lives at src/modules/db/migrations/004-projects-table.sql. Run it with:

npx tsx --env-file=.env.local src/modules/db/pipeline/run-migration.ts src/modules/db/migrations/004-projects-table.sql

images and tags are JSON strings, parsed by parseRow in src/modules/projects/queries.ts. SQLite has no array type, so this matches how content already stores authors, tags, and images.

id is a readable string

id values are pool, food-math, jaygriff-com — not UUIDs. The column is what gets written into content.project_id, so a readable value means tagging content requires no lookup.

slug is a separate column. Changing a project's URL therefore does not touch id, and existing associations survive. The seed upserts on id for the same reason: keying on slug would detach every associated row on rename.

Icons are keys, not components

projects.icon holds a string. A whitelist in src/modules/projects/icons.ts maps it to a Lucide component:

const PROJECT_ICONS = {
  blocks: BlocksIcon,
  cpu: CpuIcon,
  droplet: DropletIcon,
  globe: GlobeIcon,
  shield: ShieldIcon,
  utensils: UtensilsCrossedIcon,
} satisfies Record<string, LucideIcon>;

export function getProjectIcon(key: string | null): LucideIcon {
  return PROJECT_ICONS[key as ProjectIconKey] ?? BoxIcon;
}

A React component cannot be stored in a database column, and a value read from the database is never resolved directly into a component. An unrecognised key falls back to BoxIcon. Adding an icon option requires editing this file — the database selects from the set, it cannot extend it.

Files

• src/modules/db/migrations/004-projects-table.sql — table and index.
• src/modules/db/pipeline/seed-projects.ts — the project definitions and the upsert.
• src/modules/projects/queries.ts — ProjectRow, getAllProjects(), getProjectBySlug(slug). Both filter to status = 'published'.
• src/modules/projects/icons.ts — the icon whitelist.
• src/modules/content/queries.ts — gained getContentByProject(projectId).
• src/app/projects/[slug]/page.tsx — the project detail page.
• src/app/projects/page.tsx and src/app/page.tsx — both now read the table. Each previously held its own hardcoded array.

Adding or editing a project

Edit the PROJECTS array in src/modules/db/pipeline/seed-projects.ts, then run:

npx tsx --env-file=.env.local src/modules/db/pipeline/seed-projects.ts

The statement is INSERT ... ON CONFLICT(id) DO UPDATE, so it is safe to run repeatedly. New entries insert, existing entries update in place, and nothing is duplicated.

The seed file is the source of truth. Any edit made to the projects table by other means is overwritten the next time the seed runs. If an admin UI is added later, one of the two has to stop owning the data.

Attaching content to a project

UPDATE content SET project_id = 'jaygriff-com' WHERE slug = 'some-slug';

The detail page calls getContentByProject(project.id), splits the results by type, and renders Docs and Posts sections. The query filters to published content, so a draft stays hidden even once tagged.

Constraints

• project_id has no foreign key. A typo produces a row that silently belongs to no project rather than an error.
• Project pages are generated by generateStaticParams at build time. A project added to the database after a deploy does not appear until the next build.
• Project cards on the home page and the listing link to /projects/[slug], not to the live app. The detail page links on to app_href.
• The home page filters to four specific slugs through a PROJECT_SLUGS constant in src/app/page.tsx. A new project appears on /projects automatically but not on the home page.

Not wired up

• thumbnail, logo, images, demo_url, and video_url exist as columns and are rendered by the detail page when present, but the seed does not set them.
• No content row has a project_id yet. Every project page currently renders metadata with no Docs or Posts sections.
• Setting project_id is a manual UPDATE. The sync script does not infer it, and nothing in the pipeline sets it.
• src/app/resume/page.tsx still holds its own separate project array with a different shape.