Skip to main content

​
Overview

Aura Farm uses PostgreSQL as its database, accessed through Prisma ORM (v6). The schema is defined in backend/prisma/schema.prisma and versioned via Prisma migrations.

​
Schema

The database has four models and one enum.

​
UserRole enum

enum UserRole {
  user
  admin
}
All users default to the user role. Admins have access to privileged API endpoints protected by the requireAdmin middleware.

​
User

Stores registered users. The email field is the join key between Supabase Auth and the PostgreSQL record. 
model User {
  id              Int                   @id @default(autoincrement())
  email           String                @unique
  name            String
  role            UserRole              @default(user)
  auraPoints      Int                   @default(0)
  streak          Int                   @default(0)
  lastCompletedAt DateTime?
  createdAt       DateTime              @default(now())
  completions     ChallengeCompletion[]
  flags           Flag[]

  @@index([auraPoints])
}
FieldTypeNotes
idIntAuto-incrementing primary key
emailStringUnique — mirrors the Supabase Auth identity
nameStringDisplay name
roleUserRoleuser or admin; defaults to user
auraPointsIntAccumulated score from completed challenges
streakIntConsecutive-day completion streak
lastCompletedAtDateTime?Nullable; used for streak tracking
createdAtDateTimeRecord creation timestamp
auraPoints is indexed to support efficient leaderboard queries.

​
Challenge

Represents a campus activity or location-based task that users can complete. 
model Challenge {
  id           Int                   @id @default(autoincrement())
  title        String
  description  String
  latitude     Float
  longitude    Float
  difficulty   String
  pointsReward Int
  createdAt    DateTime              @default(now())
  completions  ChallengeCompletion[]

  @@index([createdAt])
  @@index([difficulty])
}
FieldTypeNotes
idIntAuto-incrementing primary key
titleStringShort challenge name
descriptionStringFull description shown to the user
latitudeFloatGeographic coordinate of the challenge location
longitudeFloatGeographic coordinate of the challenge location
difficultyStringe.g., easy, medium, hard
pointsRewardIntAura points awarded on completion
createdAtDateTimeRecord creation timestamp
createdAt and difficulty are indexed to support filtered browsing.

​
ChallengeCompletion

Records a user’s proof of completing a challenge. Each user can only complete a given challenge once. 
model ChallengeCompletion {
  id          Int       @id @default(autoincrement())
  userId      Int
  challengeId Int
  latitude    Float
  longitude   Float
  imageUrl    String?
  imageUri    String
  caption     String?
  likes       Int       @default(0)
  completedAt DateTime  @default(now())
  user        User      @relation(fields: [userId], references: [id])
  challenge   Challenge @relation(fields: [challengeId], references: [id])
  flags       Flag[]

  @@unique([userId, challengeId])
  @@index([userId])
  @@index([challengeId])
  @@index([completedAt])
  @@index([userId, completedAt])
}
FieldTypeNotes
idIntAuto-incrementing primary key
userIdIntForeign key → User.id
challengeIdIntForeign key → Challenge.id
latitudeFloatUser’s recorded location at time of submission
longitudeFloatUser’s recorded location at time of submission
imageUrlString?Optional public URL of the submission photo
imageUriStringLocal or uploaded image reference
captionString?Optional text added by the user
likesIntNumber of likes from other users
completedAtDateTimeSubmission timestamp
Key constraint: @@unique([userId, challengeId]) prevents a user from submitting the same challenge more than once.
The composite index on [userId, completedAt] accelerates queries that load a user’s completion history sorted by date.

​
Flag

Allows users to report a completion as suspicious or invalid. A user can only flag a given completion once. 
model Flag {
  id           Int                 @id @default(autoincrement())
  completionId Int
  flaggedById  Int
  reason       String?
  createdAt    DateTime            @default(now())
  completion   ChallengeCompletion @relation(fields: [completionId], references: [id])
  flaggedBy    User                @relation(fields: [flaggedById], references: [id])

  @@unique([completionId, flaggedById])
  @@index([completionId])
  @@index([flaggedById])
}
FieldTypeNotes
idIntAuto-incrementing primary key
completionIdIntForeign key → ChallengeCompletion.id
flaggedByIdIntForeign key → User.id (the reporter)
reasonString?Optional explanation from the reporter
createdAtDateTimeFlag submission timestamp
Key constraint: @@unique([completionId, flaggedById]) prevents the same user from flagging the same completion multiple times.

​
Indexed fields summary

ModelIndexed field(s)Reason
UserauraPointsLeaderboard ranking
ChallengecreatedAt, difficultyFiltered and sorted browsing
ChallengeCompletionuserId, challengeId, completedAt, (userId, completedAt)User history, challenge stats, feed queries
FlagcompletionId, flaggedByIdFlag lookup by completion or reporter

​
Migrations

1

Apply pending migrations

Run this after pulling changes that include new migration files:
cd backend && npx prisma migrate deploy
2

Create a new migration (development only)

After editing schema.prisma, generate a migration file:
cd backend && npx prisma migrate dev --name describe-your-change
3

Seed the database

Populate the database with initial data:
cd backend && npm run seed
This runs prisma/seed.ts via ts-node.
Use migrate deploy in production environments. migrate dev is for development only — it may reset your database when there are conflicts.

Architecture

See how Prisma fits into the overall system design.

Environment setup

Configure your DATABASE_URL and run migrations for the first time.

Build docs developers (and LLMs) love

Get started for freeTalk to us