Repository navigation
feat(database): initialize Prisma schema and seed data - #1
Conversation
📝 WalkthroughWalkthroughAdds a PostgreSQL Prisma schema, initial migration, seed and reset workflows, migration lock, database scripts, local connection example, and completed database setup tracking. ChangesDatabase foundation
Estimated code review effort: 4 (Complex) | ~45 minutes Sequence Diagram(s)sequenceDiagram
participant Developer
participant PrismaCLI
participant SeedScript
participant PostgreSQL
Developer->>PrismaCLI: run db:migrate
PrismaCLI->>PostgreSQL: apply initial migration
Developer->>PrismaCLI: run db:seed or db:seed:reset
PrismaCLI->>SeedScript: execute seed workflow
SeedScript->>PostgreSQL: optionally clear and insert seed records
PostgreSQL-->>Developer: persisted database state
🚥 Pre-merge checks | ✅ 5✅ Passed checks (5 passed)
✨ Finishing Touches📝 Generate docstrings
🧪 Generate unit tests (beta)
Comment |
![chatgpt-codex-connector[bot]](https://avatars.githubusercontent.com/in/1144995?s=60&v=4){kind=small}
There was a problem hiding this comment.
💡 Codex Review
Here are some automated review suggestions for this pull request.
Reviewed commit: b2dff785b6
ℹ️ About Codex in GitHub
Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you
- Open a pull request for review
- Mark a draft as ready
- Comment "@codex review".
If Codex has suggestions, it will comment; otherwise it will react with 👍.
Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".
| CREATE TYPE "AcademicSessionStatus" AS ENUM ('planning', 'active', 'closed', 'archived'); | ||
|
|
||
| -- CreateEnum | ||
| CREATE TYPE "UserRole" AS ENUM ('student', 'supervisor', 'coordinator', 'admin'); |
There was a problem hiding this comment.
I checked this migration against database/schema.prisma and database/seed.ts: the Prisma schema defines hod and examiner, and the seed creates an examiner user, but the PostgreSQL enum created here only allows student, supervisor, coordinator, and admin. After db:migrate, db:seed will fail with an invalid enum value, and any future HOD/examiner user inserts will be rejected by the database until the migration is corrected.
Useful? React with 👍 / 👎.
![coderabbitai[bot]](https://avatars.githubusercontent.com/in/347564?s=60&v=4){kind=small}
There was a problem hiding this comment.
Actionable comments posted: 4
🧹 Nitpick comments (4)
database/schema.prisma (3)
174-186: 🗄️ Data Integrity & Integration | 🔵 Trivial | ⚡ Quick winConsider a compound unique constraint on
(institutionId, code).
Department.codehas no uniqueness guarantee. Two departments in the same institution could share the samecode(e.g. two "CSC" departments), which is likely unintended for a department code used for identification/reporting.♻️ Proposed fix
institution Institution `@relation`(fields: [institutionId], references: [id]) users User[] projects Project[] rubrics Rubric[] announcements Announcement[] + + @@unique([institutionId, code]) }🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@database/schema.prisma` around lines 174 - 186, Update the Department model to enforce a compound unique constraint on institutionId and code, ensuring department codes are unique within each institution while allowing the same code across different institutions.
205-240: 🔒 Security & Privacy | 🔵 TrivialVerify
mfaSecretis encrypted before persistence.Storing a TOTP/MFA secret as a plain
String?column means it's only as safe as whatever the application layer does before writing it. If nothing upstream encrypts this value, it's stored in cleartext at rest, which is a common secrets-handling anti-pattern for MFA secrets.🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@database/schema.prisma` around lines 205 - 240, Ensure the User.mfaSecret persistence path encrypts the MFA secret before it is written to storage, using the application’s established encryption mechanism and preserving nullable handling; update the corresponding read path to decrypt it before use. Verify all writes and reads for User.mfaSecret, rather than relying on the schema’s String? declaration alone.
304-325: 🚀 Performance & Scalability | 🔵 Trivial | ⚡ Quick winForeign key columns generally lack explicit indexes — verify this is intentional.
Many relation scalar fields across this schema (e.g.
Document.uploadedByIdhere, plusMessage.senderId/projectId,Announcement.*,AuditLog.userId,Report.*,Grade.rubricId/approvedById,User.departmentId,Project.departmentId/sessionId) have no@@index. With PostgreSQL and the defaultforeignKeysrelation mode, Prisma does not auto-create indexes on these columns — unlike some other ORMs/databases that do this implicitly.Interestingly,
database/migrations/0_init/migration.sqlline 386 already createsDepartment_institutionId_idxeven thoughDepartment(lines 174-186) has no matching@@indexin this schema — that's drift that a futureprisma migrate devcould try to reconcile by dropping it. Worth adding explicit@@indexto the frequently-joined/filtered FK columns for consistency and to avoid full table scans on joins/deletes as data grows.For PostgreSQL with the default relation mode, Prisma and Postgres both leave foreign key columns unindexed by default: It's something I didn't see in the docs, and had assumed it happened automatically. It turns out both Postgres and CockroachDB don't index it automatically, and neither does Prisma. For PostgreSQL users using the default foreignKeys mode, it's a good practice to manually add indexes to foreign key columns for performance reasons, even though Prisma won't warn you about their absence.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@database/schema.prisma` around lines 304 - 325, Review the Prisma schema’s relation scalar fields and add explicit @@index declarations for frequently joined or filtered foreign keys, including Document.projectId and uploadedById, plus the identified FK fields on Message, Announcement, AuditLog, Report, Grade, User, Project, and Department. Preserve existing composite indexes, avoid duplicating equivalent indexes, and align the schema with the existing Department_institutionId migration index to prevent migration drift.database/seed.ts (1)
74-116: 📐 Maintainability & Code Quality | 🔵 Trivial | ⚡ Quick winSeed passwords aren't actual hashes.
passwordHash: "hashed_password_student"(and the analogous values at lines 87, 99, 111) are plain placeholder strings, not real hash output. If any auth code path does a real hash comparison (e.g. bcrypt/argon2), logging in with these seeded accounts locally won't work as expected, and the misleading field name could cause confusion later.♻️ Proposed fix
+import bcrypt from "bcryptjs"; + +const passwordHash = await bcrypt.hash("Password123!", 10); + const [student, supervisor, coordinator, examiner] = await Promise.all([ prisma.user.create({ data: { ... - passwordHash: "hashed_password_student", + passwordHash,🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@database/seed.ts` around lines 74 - 116, Replace the placeholder passwordHash values in the seeded student, supervisor, coordinator, and examiner users with actual hashes generated using the same password-hashing algorithm and configuration used by authentication. Keep the seeded credentials consistent so local login works through the normal hash-comparison path.
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
Inline comments:
In `@database/migrations/0_init/migration.sql`:
- Around line 7-8: Update the UserRole enum definition in the initial migration
to include all values declared by the Prisma schema: student, supervisor,
coordinator, hod, admin, and examiner. If this migration may already be applied
to a shared or deployed database, preserve it and add a new migration using
ALTER TYPE to add hod and examiner instead.
In `@database/schema.prisma`:
- Around line 23-30: Synchronize the UserRole enum in the Prisma schema with the
existing database migration by ensuring the physical UserRole type also includes
hod and examiner; update the corresponding migration definition as specified in
the migration.sql review so writes using all six roles, including database seed
examiner users, succeed.
In `@database/seed.ts`:
- Around line 303-313: Update the notification data in the seed flow to build
actionUrl using the seeded project’s UUID (`project.id`) instead of the
hardcoded project ID, preserving the existing `/projects/{id}/documents` route
format.
In `@package.json`:
- Around line 6-16: Protect the prisma seed flow by adding a localhost/ephemeral
DATABASE_URL validation before the destructive wipe-and-recreate logic in
database/seed.ts, and make db:seed invoke only the guarded fixture seeding path.
Move the table-wiping/recreation behavior to a separate explicitly named package
script so it cannot run through the default seed command.
---
Nitpick comments:
In `@database/schema.prisma`:
- Around line 174-186: Update the Department model to enforce a compound unique
constraint on institutionId and code, ensuring department codes are unique
within each institution while allowing the same code across different
institutions.
- Around line 205-240: Ensure the User.mfaSecret persistence path encrypts the
MFA secret before it is written to storage, using the application’s established
encryption mechanism and preserving nullable handling; update the corresponding
read path to decrypt it before use. Verify all writes and reads for
User.mfaSecret, rather than relying on the schema’s String? declaration alone.
- Around line 304-325: Review the Prisma schema’s relation scalar fields and add
explicit @@index declarations for frequently joined or filtered foreign keys,
including Document.projectId and uploadedById, plus the identified FK fields on
Message, Announcement, AuditLog, Report, Grade, User, Project, and Department.
Preserve existing composite indexes, avoid duplicating equivalent indexes, and
align the schema with the existing Department_institutionId migration index to
prevent migration drift.
In `@database/seed.ts`:
- Around line 74-116: Replace the placeholder passwordHash values in the seeded
student, supervisor, coordinator, and examiner users with actual hashes
generated using the same password-hashing algorithm and configuration used by
authentication. Keep the seeded credentials consistent so local login works
through the normal hash-comparison path.
🪄 Autofix (Beta)
Fix all unresolved CodeRabbit comments on this PR:
- Push a commit to this branch (recommended)
- Create a new PR with the fixes
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro Plus
Run ID: 0f29fd5a-8a89-41a8-b7ca-e01bf3147396
⛔ Files ignored due to path filters (1)
package-lock.jsonis excluded by!**/package-lock.json
📒 Files selected for processing (7)
.env.examplecontext/progress-tracker.mddatabase/migrations/0_init/migration.sqldatabase/migrations/migration_lock.tomldatabase/schema.prismadatabase/seed.tspackage.json
| enum UserRole { | ||
| student | ||
| supervisor | ||
| coordinator | ||
| hod | ||
| admin | ||
| examiner | ||
| } |
There was a problem hiding this comment.
🗄️ Data Integrity & Integration | 🔴 Critical | ⚡ Quick win
UserRole enum drifted from the generated migration — will break at runtime.
This enum declares 6 values (student, supervisor, coordinator, hod, admin, examiner), but the corresponding CREATE TYPE "UserRole" in database/migrations/0_init/migration.sql (line 8) only defines 4 (student, supervisor, coordinator, admin). Once this migration is applied, the physical Postgres enum will be missing hod and examiner. Any write using those values (e.g. database/seed.ts line 114 creates a user with role: "examiner") will fail with an invalid-enum-value error at the database level. See the detailed fix proposed in the migration.sql review.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@database/schema.prisma` around lines 23 - 30, Synchronize the UserRole enum
in the Prisma schema with the existing database migration by ensuring the
physical UserRole type also includes hod and examiner; update the corresponding
migration definition as specified in the migration.sql review so writes using
all six roles, including database seed examiner users, succeed.
| "prisma": { | ||
| "seed": "tsx database/seed.ts" | ||
| }, | ||
| "scripts": { | ||
| "dev": "next dev", | ||
| "build": "next build", | ||
| "start": "next start", | ||
| "typecheck": "tsc --noEmit" | ||
| "typecheck": "tsc --noEmit", | ||
| "db:generate": "prisma generate --schema database/schema.prisma", | ||
| "db:migrate": "prisma migrate dev --schema database/schema.prisma", | ||
| "db:seed": "prisma db seed" |
There was a problem hiding this comment.
🗄️ Data Integrity & Integration | 🟠 Major | 🏗️ Heavy lift
🧩 Analysis chain
🏁 Script executed:
#!/bin/bash
set -euo pipefail
# Locate the relevant files and inspect their contents with line numbers.
git ls-files package.json database/seed.ts database/schema.prisma
printf '\n--- package.json ---\n'
cat -n package.json
printf '\n--- database/seed.ts ---\n'
cat -n database/seed.ts
printf '\n--- database/schema.prisma ---\n'
cat -n database/schema.prismaRepository: dev-Vortex51/nexesis-workspace
Length of output: 32288
🏁 Script executed:
#!/bin/bash
set -euo pipefail
# Show the Prisma-related package scripts and any seed-related logic in the seed file.
python3 - <<'PY'
from pathlib import Path
import json
pkg = json.loads(Path("package.json").read_text())
print("scripts:")
for k, v in pkg.get("scripts", {}).items():
if "db" in k or "seed" in k or k in ("build", "start", "dev"):
print(f" {k}: {v}")
seed = Path("database/seed.ts").read_text().splitlines()
for i, line in enumerate(seed, 1):
if any(tok in line for tok in ["deleteMany", "createMany", "upsert", "reset", "DATABASE_URL", "localhost", "127.0.0.1", "seed"]):
print(f"{i}: {line}")
PYRepository: dev-Vortex51/nexesis-workspace
Length of output: 1576
🏁 Script executed:
#!/bin/bash
set -euo pipefail
# Inspect Prisma seeding docs references in the repo, if any.
rg -n "prisma db seed|seed.*tsx|database/seed.ts|deleteMany|DATABASE_URL" .Repository: dev-Vortex51/nexesis-workspace
Length of output: 1943
🏁 Script executed:
#!/bin/bash
set -euo pipefail
# Read the relevant files only if present, and summarize the seed logic.
for f in package.json database/seed.ts database/schema.prisma; do
if [ -f "$f" ]; then
echo "===== $f ====="
cat -n "$f"
echo
fi
doneRepository: dev-Vortex51/nexesis-workspace
Length of output: 32247
Protect the seed entrypoint. db:seed points at a script that deletes every table before inserting fixtures; add a localhost/ephemeral DATABASE_URL guard and move the wipe-and-recreate path behind a separate explicit command.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@package.json` around lines 6 - 16, Protect the prisma seed flow by adding a
localhost/ephemeral DATABASE_URL validation before the destructive
wipe-and-recreate logic in database/seed.ts, and make db:seed invoke only the
guarded fixture seeding path. Move the table-wiping/recreation behavior to a
separate explicitly named package script so it cannot run through the default
seed command.
Source: MCP tools
There was a problem hiding this comment.
Actionable comments posted: 1
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
Inline comments:
In `@database/seed-reset.ts`:
- Around line 15-21: Update the allowedHosts check in the seed-reset validation
to recognize URL.hostname’s bracketed IPv6 representation by replacing the
unbracketed "::1" entry with "[::1]". Preserve the existing localhost and
127.0.0.1 entries and rejection behavior for other hosts.
🪄 Autofix (Beta)
Fix all unresolved CodeRabbit comments on this PR:
- Push a commit to this branch (recommended)
- Create a new PR with the fixes
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro Plus
Run ID: fe47bd52-272a-495e-81c6-bc87ebbdca86
📒 Files selected for processing (3)
database/seed-reset.tsdatabase/seed.tspackage.json
| const allowedHosts = new Set(["localhost", "127.0.0.1", "::1"]); | ||
|
|
||
| if (!allowedHosts.has(url.hostname)) { | ||
| throw new Error( | ||
| "db:seed:reset is only allowed against localhost or ephemeral DATABASE_URL values.", | ||
| ); | ||
| } |
There was a problem hiding this comment.
🎯 Functional Correctness | 🟡 Minor | ⚡ Quick win
🧩 Analysis chain
🌐 Web query:
Node.js WHATWG URL hostname IPv6 brackets
💡 Result:
In the Node.js WHATWG URL API (the modern URL class), IPv6 addresses are required to be enclosed in square brackets when appearing in a URL host [1][2]. According to the WHATWG URL Standard [3][1], a valid host string can be a domain, an IPv4 address, or an IPv6 address wrapped in brackets: U+005B ([), followed by a valid IPv6-address string, followed by U+005D (]). When the WHATWG host serializer processes an IPv6 address, it automatically adds these square brackets [1][4]. If you are migrating from the legacy Node.js url.parse module to the modern URL class, note that the behavior regarding these brackets differs: 1. Legacy url.parse: The hostname property typically excludes the brackets (e.g.,::1), and you often have to manually manage them when formatting or parsing strings [5]. 2. WHATWG URL API: The hostname property preserves the brackets (e.g., [::1]) [6]. If you need the raw IPv6 address without brackets, you must strip them manually, for example: myURL.hostname.replace(/^[|]$/, '') [6]. Additionally, in certain Node.js internal utilities, explicit logic has been added to strip enclosing square brackets from hostnames to ensure compatibility when converting WHATWG URL objects into other formats, such as connection options [7][8].
Citations:
- 1: https://url.spec.whatwg.org/review-drafts/2025-02/
- 2: https://github.com/whatwg/url/blob/main/url.bs
- 3: https://url.spec.whatwg.org/
- 4: https://url.spec.whatwg.org/commit-snapshots/eee49fdf4f99d59f717cbeb0bce29fda930196d4/
- 5: Parsing litteral IPv6 addresses in URLs (RFC2732 support) nodejs/node-v0.x-archive#1138
- 6: https://github.com/nodejs/userland-migrations/tree/main/recipes/node-url-to-whatwg-url
- 7: url: make urlToOptions() handle IPv6 literals nodejs/node#19720
- 8: nodejs/node@fa2d43b
🏁 Script executed:
#!/bin/bash
set -euo pipefail
# Inspect the relevant file and surrounding lines.
git ls-files database/seed-reset.ts
nl -ba database/seed-reset.ts | sed -n '1,120p'
# Probe WHATWG URL hostname behavior for IPv6 loopback in Node.
node - <<'JS'
const u = new URL('postgresql://[::1]:5432/db');
console.log({
href: u.href,
host: u.host,
hostname: u.hostname,
origin: u.origin,
});
JSRepository: dev-Vortex51/nexesis-workspace
Length of output: 231
🏁 Script executed:
#!/bin/bash
set -euo pipefail
python3 - <<'PY'
from pathlib import Path
p = Path("database/seed-reset.ts")
for i, line in enumerate(p.read_text().splitlines(), 1):
if 1 <= i <= 120:
print(f"{i:4d} {line}")
PY
node - <<'JS'
const urls = [
'postgresql://[::1]:5432/db',
'postgresql://127.0.0.1:5432/db',
'postgresql://localhost:5432/db',
];
for (const s of urls) {
const u = new URL(s);
console.log(s, JSON.stringify({
host: u.host,
hostname: u.hostname,
href: u.href,
}));
}
JSRepository: dev-Vortex51/nexesis-workspace
Length of output: 2749
Allow "[::1]" in the host allow-list
URL.hostname returns bracketed IPv6 hosts, so postgresql://[::1]:5432/db is rejected here because the set contains "::1". Replace it with "[::1]" or normalize the hostname before checking.
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@database/seed-reset.ts` around lines 15 - 21, Update the allowedHosts check
in the seed-reset validation to recognize URL.hostname’s bracketed IPv6
representation by replacing the unbracketed "::1" entry with "[::1]". Preserve
the existing localhost and 127.0.0.1 entries and rejection behavior for other
hosts.
Summary by CodeRabbit
DATABASE_URL.