Add Devpost project import and judge project directory

[DVidal1205]

Goal

Add the first Forge project/judging slice: officer-managed Devpost project
imports and a permission-gated project directory for judges.

Product scope

Officer project management

• Add an officer-only /admin/projects surface.
• Let an officer select an existing hackathon and upload a Devpost project CSV.
• Treat each import as an authoritative full replacement of that hackathon's
  imported project inventory.
• Import submitted projects only; skip drafts and incomplete projects.
• Derive hackathon challenges from Devpost opt-in prize values and apply a
  General challenge to every project.
• Let officers edit approved imported project/team fields.
• Let officers soft-delete one project at a time after confirmation.
• Let officers restore individually soft-deleted projects.

Judge project directory

• Add /judge/projects for authenticated users with IS_JUDGE; preserve the
  existing IS_OFFICER bypass.
• Automatically show the currently active hackathon.
• Let officers preview the judge experience outside the active window using the
  hackathon with the nearest upcoming start date.
• Show title, Devpost link, prize categories, and challenges in the table.
• Support title search, sorting, established Forge pagination, and filters for
  challenge, prize/category, and participant count.
• Open a responsive project-detail modal with Markdown description, approved
  links and technologies, challenge/category data, team names, emails, schools,
  and submission time.

Import constraints

Devpost exports are variable-width and may repeat submission URLs while
expanding team-member data. The importer must produce one project per submission
URL without losing the associated team members. Event-specific questionnaire
answers are not imported for judge display.

An active hackathon satisfies start <= now <= end. Imported opt-in labels are
kept as supplied rather than merged by capitalization, whitespace, or
punctuation equivalence. A replacement import also replaces the selected
hackathon's imported challenge list, while every imported project receives the
General challenge.

The import result reports imported projects, excluded drafts, collapsed
duplicate URLs, and malformed or rejected records. Officers may edit title,
URLs, description, categories, challenge assignments, technologies,
universities, team roster/contact fields, and participant count after import.
Discord handles and all other event-specific questionnaire fields are ignored.

Accepted technical direction

• Replace the unused legacy project/challenge/submission/judge/judgment model;
  there is no production data requiring compatibility.
• Introduce durable project, project-member, challenge, and project/challenge
  join records scoped to a hackathon.
• Use a protected multipart upload route backed by an API-owned import service
  and an established CSV parser.
• Validate the complete file and replace the selected hackathon's inventory in
  one transaction; do not introduce a separate pool, queue, or staging system.
• Enforce judge/officer access on every project read and officer access on every
  mutation.
• Audit import, edit, delete, and restore without copying raw CSV or contact PII
  into audit metadata.
• Remove the unused legacy judging tables, including auth_judge_session.
• Enforce a 25 MiB multipart upload limit, store declared participant count
  independently from roster rows, and store/display normalized URLs only.

Out of scope

• Scoring, feedback, or judge responses
• Judge assignments, rooms, or judging sessions
• Ranking and winner selection
• Hacker-to-project matching
• Public project gallery
• Devpost API synchronization
• Batch project deletion outside replacement import
• Merge conflict resolution or preservation of manual edits across re-import

Planning artifacts

The feature contract is being developed under
.forge/features/project-judging/. Implementation must wait for approval of
the spec, SRD, and test cases.

Artifact status

The product spec and technical SRD are approved. Importer-focused test cases are
proposed for owner approval; implementation waits for approval of the complete
artifact bundle.

[coderabbitai]

Coding Plan

Summary

• Gate all implementation behind approval of the .forge/features/project-judging/ bundle.
• Add greenfield hackathon-scoped project, team-member, and challenge tables, plus a judgingEnabled flag, following modern schema conventions.
• Implement full-replacement CSV import as a locked transaction that deletes then bulk-inserts, and returns a per-row { imported, skipped, rejected } summary with one audit event.
• Reuse the existing canAccessX gating, permProcedure, audit, and detail-modal patterns; add a /judge route group that honors IS_JUDGE and the IS_OFFICER bypass.

Design Choices

Design Choice 1: Storage location for the per-hackathon judging enabled flag

Options Considered:

1. Add a judgingEnabled boolean column to the Hackathon table.
2. Add a child HackathonJudgingConfig table, mirroring HackathonPortalClient.

Chosen Option: 1

Rationale: A single boolean is the smallest change. Use a child table only if the SRD later adds more judging config fields.

Design Choice 2: Location of the judge surface in the route tree

Options Considered:

1. A top-level /judge route group with its own layout.tsx, following the /member/events precedent.
2. A nested /admin/judging route reusing the /admin OR-chain.

Chosen Option: 1

Rationale: The ticket path is /judge/projects. A judge-only user must not pass the officer-oriented /admin gate.

Design Choice 3: Method for the CSV to reach the server

Options Considered:

1. Base64 data URL sent as a tRPC mutation string input, decoded with decodeUploadDataUrl.
2. Presigned direct-to-MinIO upload, then server-side fetch and parse.

Chosen Option: 1

Rationale: This method matches resume/profile-picture precedent and needs no object storage for text. Revisit only if the SRD sets a large size bound.

Design Choice 4: Method to parse CSV rows

Options Considered:

1. Add papaparse (or equivalent) for robust quoting and newline handling.
2. Hand-roll a parser in packages/utils.

Chosen Option: 1

Rationale: Devpost exports contain quoted multi-line and comma-bearing cells. A tested parser reduces row-classification bugs.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Author and approve the feature contract

This phase produces the required planning artifacts. No code changes ship until the human approves the bundle.

Task 1: Scaffold the feature folder

Create the project-judging artifact bundle.

• Run pnpm forge:feature project-judging "Project Judging" to create .forge/features/project-judging/{spec,srd,test-cases,status}.md.

Task 2: Reverse-prompt and fill spec, SRD, and test cases

Resolve the open product questions before any implementation.

• Use the forge-spec-writer skill to fill spec.md. Resolve the open product questions from the ticket: submitted-status boundary, active-hackathon boundary, officer-editable fields, required skipped/rejected import summary, soft-delete restoration in this slice.
• Use the forge-srd-writer skill to fill srd.md. Cover the access policy (IS_OFFICER for /admin/projects; IS_OFFICER or IS_JUDGE for /judge/projects), the full-replacement import transaction, the data model, and the migration.
• Use the forge-test-case-writer skill to fill test-cases.md. Cover import row classification, full-replacement semantics, submission-URL dedupe with team-member aggregation, challenge derivation, judging toggle behavior, and access gating.
• Update status.md. Set the task-list approval checkbox only after the human signs off.

Task 3: Approval gate

Block implementation until approval.

• Do not start Phase 2 until the status.md task "Human approves artifact bundle before implementation/test generation" is checked.

🤖 Prompt for AI agents

Produce the planning artifact bundle only. Do not write implementation code in
this phase.

- Run `pnpm forge:feature project-judging "Project Judging"` to create
`.forge/features/project-judging/{spec,srd,test-cases,status}.md`.
- Fill `spec.md` with the `forge-spec-writer` skill. Resolve the open product
questions: submitted-status boundary, active-hackathon boundary,
officer-editable fields, the required skipped/rejected import summary, and
whether soft-delete restoration is in this slice.
- Fill `srd.md` with the `forge-srd-writer` skill. Cover the access policy
(`IS_OFFICER` for `/admin/projects`; `IS_OFFICER` or `IS_JUDGE` for
`/judge/projects`), the full-replacement import transaction, the data model, and
the migration.
- Fill `test-cases.md` with the `forge-test-case-writer` skill. Cover import row
classification, full-replacement semantics, submission-URL dedupe with
team-member aggregation, challenge derivation, judging toggle behavior, and
access gating.
- Update `status.md`. Check the task-list approval checkbox only after the human
signs off.
- Stop after this phase. Do not start Phase 2 until the `status.md` task "Human
approves artifact bundle before implementation/test generation" is checked.

Phase 2: Data model and shared contracts

This phase adds the schema, validators, audit keys, and the permission access surface. It has no UI.

Task 1: Add project and team-member tables

Store imported projects and their team members per hackathon.

• Add tables to packages/db/src/schemas/knight-hacks.ts using createTable.
• Add a Projects table: hackathonId FK to Hackathon with onDelete: "cascade", submissionUrl, title, devpostUrl, description (Markdown text), links, technologies, submissionTime, archivedAt, archivedBy FK to User, and revision integer for optimistic concurrency.
• Add a unique constraint on (hackathonId, submissionUrl) so one project exists per submission URL per hackathon.
• Add a ProjectTeamMembers table linked to Projects with onDelete: "cascade": name, email, discordHandle, school.
• Add a Challenges table scoped by hackathonId and a project-to-challenge link table for many-to-many; represent prize/category values as needed to satisfy the table columns and filters in the ticket.
• Add a judgingEnabled boolean column to Hackathon, default false.
• Export Insert*/Select* types for each new table.
• Generate the Drizzle migration and snapshot pair under packages/db/drizzle/.

Task 2: Add validators

Define input and row schemas.

• Add packages/validators/src/projects.ts with Zod schemas: import input (hackathon scope plus CSV data URL string), project edit input, soft-delete input, judging toggle input, and the judge directory filter/sort/pagination input.
• Add a CSV upload policy for text/csv in packages/validators/src/upload-policy.ts. Treat CSV as "no signature" in SIGNATURES/matchesUploadSignature, like form attachments.
• Add export * from "./projects"; to packages/validators/src/index.ts.

Task 3: Register audit action keys and target type

Support audit trails for project mutations.

• Add a "project" AuditTargetType and action keys project.imported, project.edited, project.archived, project.judging_toggled (and project.restored if the spec includes restore) to AUDIT_ACTION_CATALOG in @forge/validators, with allowed metadataKeys/changeFields.
• Add "project" label/condition wiring in packages/api/src/utils/audit/queries.ts so project events appear in admin logs.

Task 4: Add the judging access surface

Wire IS_JUDGE into gating.

• Add packages/api/src/utils/project-judging/access.ts with require* wrappers over permissions.controlPerms.or(["IS_JUDGE"], ctx) for judge reads and requireProjectManage over officer-scoped checks for officer mutations. This preserves the IS_OFFICER bypass.
• Add canAccessJudging(permissions) to apps/blade/src/lib/admin-access.ts as permissions.IS_OFFICER === true || permissions.IS_JUDGE === true.

🤖 Prompt for AI agents

Implement the schema, validators, audit wiring, and access helpers needed by
later phases. Ship no UI in this phase.

- In `packages/db/src/schemas/knight-hacks.ts`, use `createTable` to add: a
`Projects` table with `hackathonId` FK to `Hackathon` (`onDelete: "cascade"`),
`submissionUrl`, `title`, `devpostUrl`, `description` (Markdown text), `links`,
`technologies`, `submissionTime`, `archivedAt`, `archivedBy` FK to `User`, and a
`revision` integer for optimistic concurrency; add a unique constraint on
`(hackathonId, submissionUrl)`; a `ProjectTeamMembers` table linked to
`Projects` (`onDelete: "cascade"`) with `name`, `email`, `discordHandle`,
`school`; a `Challenges` table scoped by `hackathonId` and a
project-to-challenge link table for many-to-many, representing prize/category
values as needed for the table columns and filters described in the ticket; and
a `judgingEnabled` boolean column on `Hackathon`, default `false`. Export
`Insert*`/`Select*` types for each new table. Generate the Drizzle migration and
snapshot pair under `packages/db/drizzle/`.
- In `packages/validators/src/projects.ts`, add Zod schemas for: import input
(hackathon scope plus CSV data URL string), project edit input, soft-delete
input, judging toggle input, and the judge directory filter/sort/pagination
input. Add a CSV upload policy for `text/csv` in
`packages/validators/src/upload-policy.ts`, treating CSV as "no signature" in
`SIGNATURES`/`matchesUploadSignature`, like form attachments. Add `export * from
"./projects";` to `packages/validators/src/index.ts`.
- Add a `"project"` `AuditTargetType` and the action keys `project.imported`,
`project.edited`, `project.archived`, `project.judging_toggled` (and
`project.restored` if the spec includes restore) to `AUDIT_ACTION_CATALOG` in
`@forge/validators`, with allowed `metadataKeys`/`changeFields`. Add `"project"`
label/condition wiring in `packages/api/src/utils/audit/queries.ts` so project
events appear in admin logs.
- Add `packages/api/src/utils/project-judging/access.ts` with `require*`
wrappers over `permissions.controlPerms.or(["IS_JUDGE"], ctx)` for judge reads
and a `requireProjectManage` wrapper over officer-scoped checks for officer
mutations. Preserve the `IS_OFFICER` bypass. Add `canAccessJudging(permissions)`
to `apps/blade/src/lib/admin-access.ts`, returning `permissions.IS_OFFICER ===
true || permissions.IS_JUDGE === true`.

Phase 3: Import and officer mutation backend

This phase adds CSV parsing and the officer-facing router procedures.

Task 1: Add the Devpost CSV parser

Convert CSV text into normalized rows.

• Add a CSV parser dependency (Option 1 in Assumptions).
• Add packages/api/src/utils/projects/devpost-import.ts. Parse the decoded CSV text into rows. Keep only submitted projects; skip drafts and incomplete projects. Group rows by submission URL to produce one project per URL while aggregating repeated team-member data. Derive challenges from opt-in prize values and add a General challenge to every project. Drop event-specific questionnaire answers.
• Return a structured result: { imported, skipped, rejected } with per-row reasons, matching the importTags return shape convention.

Task 2: Add the projects router

Expose import, edit, soft-delete, and judging toggle.

• Add packages/api/src/routers/projects.ts exporting projectsRouter on permProcedure.
• Add importProjects: officer-gated. Decode the CSV with decodeUploadDataUrl, parse it, then run a full-replacement transaction. Lock the hackathon row with for("update"), delete existing non-archived imported projects for that hackathon, bulk insert the new projects, challenges, links, and team members, and write one summarizing project.imported audit event with operationId.
• Add updateProject: officer-gated. Update approved editable fields with optimistic concurrency on revision, and write a project.edited audit event with changes.
• Add archiveProject: officer-gated. Soft-delete one project after confirmation, following the issues.ts archive pattern (archivedAt, archivedBy, revision bump, project.archived audit event). Add restoreProject only if the spec includes restoration.
• Add setJudgingEnabled: officer-gated. Update Hackathon.judgingEnabled and write a project.judging_toggled audit event.
• Add listProjects: judge-or-officer gated. Return paginated, filtered, sorted rows for the directory.
• Add getProject: judge-or-officer gated. Return full detail for the modal, including team members, challenges, prize categories, links, and technologies.
• Register projects: projectsRouter in both AppRouterShape and appRouterRecord in packages/api/src/root.ts.

Task 3: Add active-hackathon resolution for judging

Select the hackathon the judge directory shows.

• Add resolution logic in packages/api/src/utils/projects/ that returns the active hackathon by date. If none is active, return the hackathon whose start date is closest, following the date-derivation precedent in hacker.ts/hackathon-report.server.ts.
• Expose whether judging is enabled so the frontend can show the disabled banner.

🤖 Prompt for AI agents

Implement CSV parsing and the officer-facing router procedures built on the
Phase 2 schema and validators.

- Add a CSV parser dependency (papaparse or equivalent).
- Add `packages/api/src/utils/projects/devpost-import.ts`. Parse the decoded CSV
text into rows. Keep only submitted projects; skip drafts and incomplete
projects. Group rows by submission URL to produce one project per URL,
aggregating repeated team-member data. Derive challenges from opt-in prize
values and add a `General` challenge to every project. Drop event-specific
questionnaire answers. Return a structured result of the form `{ imported,
skipped, rejected }` with per-row reasons, matching the `importTags` return
shape convention.
- Add `packages/api/src/routers/projects.ts` exporting `projectsRouter` on
`permProcedure`, with these procedures:
- `importProjects` (officer-gated): decode the CSV with `decodeUploadDataUrl`,
parse it, then run a full-replacement transaction. Lock the hackathon row with
`for("update")`, delete existing non-archived imported projects for that
hackathon, bulk insert the new projects, challenges, links, and team members,
and write one summarizing `project.imported` audit event with `operationId`.
- `updateProject` (officer-gated): update approved editable fields with
optimistic concurrency on `revision`, and write a `project.edited` audit event
with `changes`.
- `archiveProject` (officer-gated): soft-delete one project after confirmation,
following the `issues.ts` archive pattern (`archivedAt`, `archivedBy`, revision
bump, `project.archived` audit event). Add `restoreProject` only if the spec
includes restoration.
- `setJudgingEnabled` (officer-gated): update `Hackathon.judgingEnabled` and
write a `project.judging_toggled` audit event.
- `listProjects` (judge-or-officer gated): return paginated, filtered, sorted
rows for the directory.
- `getProject` (judge-or-officer gated): return full detail for the modal,
including team members, challenges, prize categories, links, and technologies.
- Register `projects: projectsRouter` in both `AppRouterShape` and
`appRouterRecord` in `packages/api/src/root.ts`.
- Add active-hackathon resolution logic under `packages/api/src/utils/projects/`
that returns the active hackathon by date, or, if none is active, the hackathon
whose start date is closest, following the date-derivation precedent in
`hacker.ts`/`hackathon-report.server.ts`. Expose whether judging is enabled so
the frontend can show the disabled banner.

Phase 4: Officer /admin/projects UI

This phase adds the officer surface.

Task 1: Wire officer navigation and route gate

Expose /admin/projects to officers.

• Add a projects entry to AdminNavigationAccess, getAdminNavigationAccess, and adminNavigationItems in apps/blade/src/app/_components/shared/admin-navigation.ts, keyed to a canAccessProjects predicate (IS_OFFICER only).
• Add canAccessProjects to apps/blade/src/lib/admin-access.ts.
• Add apps/blade/src/app/admin/projects/page.tsx plus loading.tsx and error.tsx. Re-check officer access and redirect, following admin/logs/page.tsx.

Task 2: Build the officer dashboard and import dialog

Let officers import, edit, toggle judging, and soft-delete.

• Add components under apps/blade/src/app/_components/admin/projects/: project-admin-dashboard.tsx, params.ts, server-adapters.ts, types.ts.
• Add a hackathon selector and a judgingEnabled Switch with an explicit Save button, following portal-configuration-section.tsx.
• Add a CSV import dialog. Read the file with FileReader.readAsDataURL, check metadata with checkUploadMetadata, call importProjects, and render the returned { imported, skipped, rejected } summary, modeled on hackathon-tag-import-dialog.tsx.
• Add an edit form for approved editable fields.
• Add a soft-delete confirm dialog, modeled on form-delete-dialog.tsx.

🤖 Prompt for AI agents

Build the officer-facing `/admin/projects` surface on top of the Phase 3 router.

- Add a `projects` entry to `AdminNavigationAccess`, `getAdminNavigationAccess`,
and `adminNavigationItems` in
`apps/blade/src/app/_components/shared/admin-navigation.ts`, keyed to a
`canAccessProjects` predicate that requires `IS_OFFICER` only. Add
`canAccessProjects` to `apps/blade/src/lib/admin-access.ts`.
- Add `apps/blade/src/app/admin/projects/page.tsx` plus `loading.tsx` and
`error.tsx`. Re-check officer access and redirect, following the pattern in
`admin/logs/page.tsx`.
- Add components under `apps/blade/src/app/_components/admin/projects/`:
`project-admin-dashboard.tsx`, `params.ts`, `server-adapters.ts`, `types.ts`.
- Add a hackathon selector and a `judgingEnabled` `Switch` with an explicit Save
button, following `portal-configuration-section.tsx`.
- Add a CSV import dialog: read the file with `FileReader.readAsDataURL`, check
metadata with `checkUploadMetadata`, call `importProjects`, and render the
returned `{ imported, skipped, rejected }` summary, modeled on
`hackathon-tag-import-dialog.tsx`.
- Add an edit form for the approved editable fields.
- Add a soft-delete confirm dialog, modeled on `form-delete-dialog.tsx`.

Phase 5: Judge /judge/projects directory

This phase adds the judge-facing surface.

Task 1: Add the `/judge` route group and gate

Serve judges without exposing /admin.

• Add apps/blade/src/app/judge/layout.tsx following the /member/events layout precedent. Call auth(), fetch permissions, and redirect if canAccessJudging is false.
• Add apps/blade/src/app/judge/projects/page.tsx plus loading.tsx and error.tsx.

Task 2: Build the judge directory and detail modal

Show the project directory with search, sort, filters, and detail.

• Add components under apps/blade/src/app/_components/judge/projects/.
• When judging is enabled, show the active hackathon directory. When judging is disabled, show only the explanatory banner. Let officers preview using the closest-start-date hackathon.
• Add the directory table with title, Devpost link, prize categories, and challenges. Add title search, sort, filters for challenge, prize/category, and participant count, and URL-driven pagination following the admin/events/params.ts pattern.
• Add a responsive detail modal following hacker-detail-dialog.tsx. Render the Markdown description with MarkdownContent. Show approved links, technologies, challenge/category data, team names, emails, Discord handles, schools, and submission time. Do not show event-specific questionnaire answers.

🤖 Prompt for AI agents

Build the judge-facing `/judge/projects` surface on top of the Phase 3 router,
gated by `IS_JUDGE` or `IS_OFFICER`.

- Add `apps/blade/src/app/judge/layout.tsx` following the `/member/events`
layout precedent. Call `auth()`, fetch permissions, and redirect if
`canAccessJudging` is false.
- Add `apps/blade/src/app/judge/projects/page.tsx` plus `loading.tsx` and
`error.tsx`.
- Add components under `apps/blade/src/app/_components/judge/projects/`.
- When judging is enabled, show the active hackathon directory. When judging is
disabled, show only the explanatory banner. Let officers preview using the
closest-start-date hackathon.
- Add the directory table with title, Devpost link, prize categories, and
challenges. Add title search, sort, filters for challenge, prize/category, and
participant count, and URL-driven pagination, following the
`admin/events/params.ts` pattern.
- Add a responsive detail modal following `hacker-detail-dialog.tsx`. Render the
Markdown description with `MarkdownContent`. Show approved links, technologies,
challenge/category data, team names, emails, Discord handles, schools, and
submission time. Do not show event-specific questionnaire answers.

Research

Forge is a monorepo. tRPC routers live in packages/api/src/routers/* and register in packages/api/src/root.ts. Validators live in packages/validators/src/* and export through index.ts. DB tables live in packages/db/src/schemas/knight-hacks.ts with generated migrations in packages/db/drizzle/. The Blade app gates admin routes with canAccessX predicates in apps/blade/src/lib/admin-access.ts and server-side redirects in apps/blade/src/app/admin/layout.tsx. IS_JUDGE already exists in packages/consts/src/permissions.ts (idx 1) but no surface consumes it. No CSV parser dependency exists. No stored "active hackathon" exists; it is derived from dates. Audit events use createAdminAuditEvent with keys registered in AUDIT_ACTION_CATALOG. Soft-delete precedent is packages/api/src/routers/issues.ts.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Produce the planning artifact bundle only. Do not write implementation code in
this phase.

- Run `pnpm forge:feature project-judging "Project Judging"` to create
`.forge/features/project-judging/{spec,srd,test-cases,status}.md`.
- Fill `spec.md` with the `forge-spec-writer` skill. Resolve the open product
questions: submitted-status boundary, active-hackathon boundary,
officer-editable fields, the required skipped/rejected import summary, and
whether soft-delete restoration is in this slice.
- Fill `srd.md` with the `forge-srd-writer` skill. Cover the access policy
(`IS_OFFICER` for `/admin/projects`; `IS_OFFICER` or `IS_JUDGE` for
`/judge/projects`), the full-replacement import transaction, the data model, and
the migration.
- Fill `test-cases.md` with the `forge-test-case-writer` skill. Cover import row
classification, full-replacement semantics, submission-URL dedupe with
team-member aggregation, challenge derivation, judging toggle behavior, and
access gating.
- Update `status.md`. Check the task-list approval checkbox only after the human
signs off.
- Stop after this phase. Do not start Phase 2 until the `status.md` task "Human
approves artifact bundle before implementation/test generation" is checked.
===============================================================================

Task: 2

Implement the schema, validators, audit wiring, and access helpers needed by
later phases. Ship no UI in this phase.

- In `packages/db/src/schemas/knight-hacks.ts`, use `createTable` to add: a
`Projects` table with `hackathonId` FK to `Hackathon` (`onDelete: "cascade"`),
`submissionUrl`, `title`, `devpostUrl`, `description` (Markdown text), `links`,
`technologies`, `submissionTime`, `archivedAt`, `archivedBy` FK to `User`, and a
`revision` integer for optimistic concurrency; add a unique constraint on
`(hackathonId, submissionUrl)`; a `ProjectTeamMembers` table linked to
`Projects` (`onDelete: "cascade"`) with `name`, `email`, `discordHandle`,
`school`; a `Challenges` table scoped by `hackathonId` and a
project-to-challenge link table for many-to-many, representing prize/category
values as needed for the table columns and filters described in the ticket; and
a `judgingEnabled` boolean column on `Hackathon`, default `false`. Export
`Insert*`/`Select*` types for each new table. Generate the Drizzle migration and
snapshot pair under `packages/db/drizzle/`.
- In `packages/validators/src/projects.ts`, add Zod schemas for: import input
(hackathon scope plus CSV data URL string), project edit input, soft-delete
input, judging toggle input, and the judge directory filter/sort/pagination
input. Add a CSV upload policy for `text/csv` in
`packages/validators/src/upload-policy.ts`, treating CSV as "no signature" in
`SIGNATURES`/`matchesUploadSignature`, like form attachments. Add `export * from
"./projects";` to `packages/validators/src/index.ts`.
- Add a `"project"` `AuditTargetType` and the action keys `project.imported`,
`project.edited`, `project.archived`, `project.judging_toggled` (and
`project.restored` if the spec includes restore) to `AUDIT_ACTION_CATALOG` in
`@forge/validators`, with allowed `metadataKeys`/`changeFields`. Add `"project"`
label/condition wiring in `packages/api/src/utils/audit/queries.ts` so project
events appear in admin logs.
- Add `packages/api/src/utils/project-judging/access.ts` with `require*`
wrappers over `permissions.controlPerms.or(["IS_JUDGE"], ctx)` for judge reads
and a `requireProjectManage` wrapper over officer-scoped checks for officer
mutations. Preserve the `IS_OFFICER` bypass. Add `canAccessJudging(permissions)`
to `apps/blade/src/lib/admin-access.ts`, returning `permissions.IS_OFFICER ===
true || permissions.IS_JUDGE === true`.
===============================================================================

Task: 3

Implement CSV parsing and the officer-facing router procedures built on the
Phase 2 schema and validators.

- Add a CSV parser dependency (papaparse or equivalent).
- Add `packages/api/src/utils/projects/devpost-import.ts`. Parse the decoded CSV
text into rows. Keep only submitted projects; skip drafts and incomplete
projects. Group rows by submission URL to produce one project per URL,
aggregating repeated team-member data. Derive challenges from opt-in prize
values and add a `General` challenge to every project. Drop event-specific
questionnaire answers. Return a structured result of the form `{ imported,
skipped, rejected }` with per-row reasons, matching the `importTags` return
shape convention.
- Add `packages/api/src/routers/projects.ts` exporting `projectsRouter` on
`permProcedure`, with these procedures:
- `importProjects` (officer-gated): decode the CSV with `decodeUploadDataUrl`,
parse it, then run a full-replacement transaction. Lock the hackathon row with
`for("update")`, delete existing non-archived imported projects for that
hackathon, bulk insert the new projects, challenges, links, and team members,
and write one summarizing `project.imported` audit event with `operationId`.
- `updateProject` (officer-gated): update approved editable fields with
optimistic concurrency on `revision`, and write a `project.edited` audit event
with `changes`.
- `archiveProject` (officer-gated): soft-delete one project after confirmation,
following the `issues.ts` archive pattern (`archivedAt`, `archivedBy`, revision
bump, `project.archived` audit event). Add `restoreProject` only if the spec
includes restoration.
- `setJudgingEnabled` (officer-gated): update `Hackathon.judgingEnabled` and
write a `project.judging_toggled` audit event.
- `listProjects` (judge-or-officer gated): return paginated, filtered, sorted
rows for the directory.
- `getProject` (judge-or-officer gated): return full detail for the modal,
including team members, challenges, prize categories, links, and technologies.
- Register `projects: projectsRouter` in both `AppRouterShape` and
`appRouterRecord` in `packages/api/src/root.ts`.
- Add active-hackathon resolution logic under `packages/api/src/utils/projects/`
that returns the active hackathon by date, or, if none is active, the hackathon
whose start date is closest, following the date-derivation precedent in
`hacker.ts`/`hackathon-report.server.ts`. Expose whether judging is enabled so
the frontend can show the disabled banner.
===============================================================================

Task: 4

Build the officer-facing `/admin/projects` surface on top of the Phase 3 router.

- Add a `projects` entry to `AdminNavigationAccess`, `getAdminNavigationAccess`,
and `adminNavigationItems` in
`apps/blade/src/app/_components/shared/admin-navigation.ts`, keyed to a
`canAccessProjects` predicate that requires `IS_OFFICER` only. Add
`canAccessProjects` to `apps/blade/src/lib/admin-access.ts`.
- Add `apps/blade/src/app/admin/projects/page.tsx` plus `loading.tsx` and
`error.tsx`. Re-check officer access and redirect, following the pattern in
`admin/logs/page.tsx`.
- Add components under `apps/blade/src/app/_components/admin/projects/`:
`project-admin-dashboard.tsx`, `params.ts`, `server-adapters.ts`, `types.ts`.
- Add a hackathon selector and a `judgingEnabled` `Switch` with an explicit Save
button, following `portal-configuration-section.tsx`.
- Add a CSV import dialog: read the file with `FileReader.readAsDataURL`, check
metadata with `checkUploadMetadata`, call `importProjects`, and render the
returned `{ imported, skipped, rejected }` summary, modeled on
`hackathon-tag-import-dialog.tsx`.
- Add an edit form for the approved editable fields.
- Add a soft-delete confirm dialog, modeled on `form-delete-dialog.tsx`.
===============================================================================

Task: 5

Build the judge-facing `/judge/projects` surface on top of the Phase 3 router,
gated by `IS_JUDGE` or `IS_OFFICER`.

- Add `apps/blade/src/app/judge/layout.tsx` following the `/member/events`
layout precedent. Call `auth()`, fetch permissions, and redirect if
`canAccessJudging` is false.
- Add `apps/blade/src/app/judge/projects/page.tsx` plus `loading.tsx` and
`error.tsx`.
- Add components under `apps/blade/src/app/_components/judge/projects/`.
- When judging is enabled, show the active hackathon directory. When judging is
disabled, show only the explanatory banner. Let officers preview using the
closest-start-date hackathon.
- Add the directory table with title, Devpost link, prize categories, and
challenges. Add title search, sort, filters for challenge, prize/category, and
participant count, and URL-driven pagination, following the
`admin/events/params.ts` pattern.
- Add a responsive detail modal following `hacker-detail-dialog.tsx`. Render the
Markdown description with `MarkdownContent`. Show approved links, technologies,
challenge/category data, team names, emails, Discord handles, schools, and
submission time. Do not show event-specific questionnaire answers.

💡 Iterate on the plan with: `@coderabbitai` <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---