AI Interview Module — Database Guide

AI Interview Module
Database Design & Implementation Guide

Portfolix LMS
Prepared for Parvathy
June 2026

Page 1
 AI Interview Module — Database Guide

Table of Contents
TOC \h \o "1-3"

Page 2
 AI Interview Module — Database Guide

1. Overview
This document lays out the database architecture for the Portfolix AI Interview Practice &
Evaluation System: how interview data is modeled, how recorded video and audio are stored
and eventually purged, and how a freemium / paid access layer can sit on top of it without
reworking the schema later. It is written for the current stack — [Link] / Express with
PostgreSQL, with a parallel Python (FastAPI + SQLAlchemy) implementation included for
any pieces built separately.

The guidance here is organized around three layers that work together but stay independent:
the core relational schema (templates, questions, attempts, answers, scores), the storage
and retention layer (where video/audio actually live and how a 30-day “recently deleted”
window is implemented), and the access-control layer (free vs. paid usage). Each is covered
in its own section below.

2. Storage Strategy: Database vs. Object Storage

The single most important architectural decision is this: video and audio files never live
inside PostgreSQL, MySQL, or any relational database — not even as BLOBs. Recorded
interview answers are large binary files, and a database carrying gigabytes of binary data
becomes slow to query, expensive to back up, and difficult to stream to a browser efficiently.

Instead, the actual files live in object storage — [Link] Stream is a natural fit given it is
already used elsewhere in the Portfolix stack, with S3 or Cloudflare R2 as alternatives. The
database only ever stores a reference: a URL or storage key pointing to where the file lives.
Every table below that mentions a video or audio field (video_url, audio_url) is storing a
string, not a file.

This separation is also what makes the 30-day retention system in Section 4 possible:
“deleting” a recording within the system is, for the first 30 days, purely a database operation
(flipping a flag), while the actual file removal from object storage is a separate, deliberate
step that only happens once after the window closes.

3. Core Database Schema

The schema below is expressed in Prisma syntax (matching the existing LMS backend),
mapping directly onto PostgreSQL tables. It expands the five tables originally sketched in the
project brief (interview_templates, interview_questions, interview_attempts,
interview_answers, admin_reviews) into a fuller structure that separates AI evaluation,
speech analysis, and posture analysis into their own tables — keeping each concern
independently queryable and the core answer record lean.

3.1 Interview Templates & Questions

Templates represent a specific interview (e.g. “Flutter Developer Interview — Intermediate”)
and hold their own questions, each with timing rules and an optional ideal-answer rubric for
AI scoring to compare against.

enum Role { STUDENT ADMIN SUPER_ADMIN }

enum Difficulty { BEGINNER INTERMEDIATE ADVANCED }
enum InterviewType { ONE_WAY CONVERSATIONAL }

Page 3
 AI Interview Module — Database Guide

enum TemplateStatus { DRAFT PUBLISHED ARCHIVED }

model InterviewTemplate {
id String @id @default(uuid())
title String
description String?
category String // "Flutter Developer", "UI/UX Designer", etc.
difficulty Difficulty
interviewType InterviewType
totalQuestions Int
minPassingScore Int?
status TemplateStatus @default(DRAFT)
createdBy String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt

questions InterviewQuestion[]
attempts InterviewAttempt[]
}

model InterviewQuestion {
id String @id @default(uuid())
templateId String
template InterviewTemplate @relation(fields: [templateId], references: [id])
questionText String
idealAnswer String? @[Link]
keywordsExpected String[] // native Postgres array, no join table needed
scoringRubric Json? // flexible per-question rubric overrides
preparationTime Int // seconds
answerTimeLimit Int // seconds
maxRetakes Int @default(1)
orderIndex Int

answers InterviewAnswer[]
}

3.2 Attempts & Answers

An attempt represents one full run through a template. Each answer within it moves through
a processing pipeline — upload, transcription, evaluation — so its status is tracked explicitly
rather than inferred, since each stage can succeed or fail independently.

enum AttemptStatus { IN_PROGRESS COMPLETED ABANDONED }

enum ProcessingStatus { UPLOADED TRANSCRIBING TRANSCRIBED EVALUATING EVALUATED
FAILED }

model InterviewAttempt {
id String @id @default(uuid())
userId String
templateId String
template InterviewTemplate @relation(fields: [templateId], references: [id])
status AttemptStatus @default(IN_PROGRESS)
finalScore Float?
answerScore Float?

Page 4
 AI Interview Module — Database Guide

speechScore Float?
bodyLanguageScore Float?
startedAt DateTime @default(now())
completedAt DateTime?

answers InterviewAnswer[]
adminReview AdminReview?
conversationTurns ConversationTurn[]
}

model InterviewAnswer {
id String @id @default(uuid())
attemptId String
attempt InterviewAttempt @relation(fields: [attemptId], references: [id])
questionId String
question InterviewQuestion @relation(fields: [questionId], references:
[id])
retakeCount Int @default(0)
videoUrl String
audioUrl String?
durationSec Int?
transcript String? @[Link]
transcriptEditedBy String? // admin user id, if manually edited
processingStatus ProcessingStatus @default(UPLOADED)
processingError String?
createdAt DateTime @default(now())

evaluation AnswerEvaluation?
speechReport SpeechReport?
postureReport PostureReport?
}

3.3 AI Evaluation, Speech & Posture Reports

Each is a one-to-one extension of an answer. Splitting them out means a query like “all
answers with hire_readiness = STRONG” never has to touch speech or posture columns,
and the AI evaluation table can store the raw model response for debugging without
affecting the rest of the schema.

enum HireReadiness { NOT_READY NEEDS_IMPROVEMENT GOOD STRONG EXCELLENT }

model AnswerEvaluation {
id String @id @default(uuid())
answerId String @unique
answer InterviewAnswer @relation(fields: [answerId], references:
[id])
overallScore Int
relevanceScore Int
clarityScore Int
knowledgeScore Int
exampleScore Int
communicationScore Int
businessUnderstandingScore Int
impactScore Int

Page 5
 AI Interview Module — Database Guide

whatWentWell String[]
whatNeedsImprovement String[]
missingKeywords String[]
usedKeywords String[]
shortFeedback String @[Link]
detailedFeedback String @[Link]
refinedAnswer String @[Link]
hireReadiness HireReadiness
rawAiResponse Json? // raw model output, for debugging/audit
createdAt DateTime @default(now())
}

model SpeechReport {
id String @id @default(uuid())
answerId String @unique
answer InterviewAnswer @relation(fields: [answerId], references: [id])
wordsPerMinute Float
fillerWordCount Int
fillerWordBreakdown Json // { "um": 3, "like": 5, ... }
repeatedWords Json?
longPauseCount Int
pitchVariation Float?
speechRateScore Int // out of 5
fillerScore Int // out of 5
finalSpeechScore Int // out of 10
}

model PostureReport {
id String @id @default(uuid())
answerId String @unique
answer InterviewAnswer @relation(fields: [answerId], references:
[id])
faceCenteredScore Int // out of 5
eyeContactScore Int
shoulderAlignmentScore Int
headStraightScore Int
distanceScore Int
stabilityScore Int
finalPostureScore Int // out of 10
}

3.4 Admin Review

AI scoring supports mentors rather than replacing them, so every attempt can carry a
manual override.

enum ReviewStatus { PENDING REVIEWED }

model AdminReview {
id String @id @default(uuid())
attemptId String @unique
attempt InterviewAttempt @relation(fields: [attemptId], references: [id])
reviewedBy String
manualScore Int?

Page 6
 AI Interview Module — Database Guide

adminComment String? @[Link]

status ReviewStatus @default(PENDING)
reviewedAt DateTime?
}

3.5 Phase 2: Conversational Interview

Worth stubbing into the schema now, even though the conversational interviewer is a later
phase, so the structure does not need a rework once it is built.

model ConversationTurn {
id String @id @default(uuid())
attemptId String
attempt InterviewAttempt @relation(fields: [attemptId], references:
[id])
turnIndex Int
aiQuestion String @[Link]
studentAnswerVideoUrl String?
transcript String? @[Link]
isFollowUp Boolean @default(false)
createdAt DateTime @default(now())
}

On the processing pipeline: an uploaded video lands in [Link] Stream, the upload
handler immediately writes an InterviewAnswer row with processingStatus UPLOADED, then
queues a job (BullMQ + Redis pairs naturally with the existing Node/Express stack). A
worker extracts and transcribes audio, updates the transcript and status to TRANSCRIBED,
then a second job runs the AI evaluation prompt against the transcript and writes the
AnswerEvaluation row. Speech and posture analysis can run as parallel jobs off the same
media rather than blocking the transcript-to-evaluation chain. Once every answer in an
attempt reaches EVALUATED, a final job aggregates the weighted final score (70% answer
evaluation, 15% speech, 15% body language per the project brief).

Page 7
 AI Interview Module — Database Guide

4. The 30-Day “Recently Deleted” System

The requirement is a Photos-app-style retention model: when a recording is deleted, it does
not disappear immediately. It moves to a “recently deleted” state for 30 days, during which it
can be restored instantly. If 30 days pass with no restore, a background job permanently
removes the actual video and audio files from object storage. Three pieces work together to
make this happen: a status flag and two timestamps on each answer row, application
queries that filter the flag, and a daily scheduled job that performs the real, irreversible
deletion.

4.1 Schema Additions

Three columns added to InterviewAnswer carry the whole mechanism: whether a row is in
the trash, when it was trashed, and the exact date it becomes unrecoverable.

model InterviewAnswer {
// ...existing fields...
isDeleted Boolean @default(false)
deletedAt DateTime?
purgeAt DateTime?

@@index([isDeleted, purgeAt])
}

The index matters: the daily cleanup job repeatedly queries WHERE isDeleted = true AND
purgeAt <= now(), and that needs to hit an index rather than scan the full table once there are
thousands of rows.

npx prisma migrate dev --name add_soft_delete_to_answers

4.2 [Link] / Express / Prisma Implementation

Delete and restore are simple flag flips. “My Recordings” and “Recently Deleted” are the
same table, filtered by the same flag in opposite directions.

const THIRTY_DAYS_MS = 30 * 24 * 60 * 60 * 1000;

// soft-delete a single answer

[Link]('/answers/:id/delete', authMiddleware, async (req, res) => {
const { id } = [Link];
const answer = await [Link]({
where: { id }, include: { attempt: true },
});
if (!answer) return [Link](404).json({ error: 'Not found' });
if ([Link] !== [Link] && [Link] !== 'ADMIN') {
return [Link](403).json({ error: 'Not allowed' });
}

const deletedAt = new Date();

const updated = await [Link]({

Page 8
 AI Interview Module — Database Guide

where: { id },
data: {
isDeleted: true,
deletedAt,
purgeAt: new Date([Link]() + THIRTY_DAYS_MS),
},
});
[Link](updated);
});

// restore within the 30-day window

[Link]('/answers/:id/restore', authMiddleware, async (req, res) => {
const answer = await [Link]({ where: { id: [Link] }
});
if (!answer || ![Link]) return [Link](400).json({ error: 'Nothing to
restore' });

const updated = await [Link]({

where: { id: [Link] },
data: { isDeleted: false, deletedAt: null, purgeAt: null },
});
[Link](updated);
});

// "My Recordings" — active only

[Link]('/my-recordings', authMiddleware, async (req, res) => {
const answers = await [Link]({
where: { attempt: { userId: [Link] }, isDeleted: false },
orderBy: { createdAt: 'desc' },
});
[Link](answers);
});

// "Recently Deleted" — trashed only

[Link]('/recently-deleted', authMiddleware, async (req, res) => {
const answers = await [Link]({
where: { attempt: { userId: [Link] }, isDeleted: true },
orderBy: { deletedAt: 'desc' },
});
[Link](answers);
});

[Link] deletion helper, used only by the purge job below — never on the delete/restore
routes themselves:

async function deleteFile(fileUrl) {

if (!fileUrl) return;
const path = [Link]([Link].BUNNY_CDN_BASE_URL, '');
await [Link](
`[Link]
{ headers: { AccessKey: [Link].BUNNY_API_KEY } }
);
}

Page 9
 AI Interview Module — Database Guide

The daily purge job — the only place a real file is ever deleted:

async function purgeExpiredAnswers() {

const expired = await [Link]({
where: { isDeleted: true, purgeAt: { lte: new Date() } },
});

for (const answer of expired) {

try {
await [Link]([Link]);
if ([Link]) await [Link]([Link]);

await [Link]({
where: { id: [Link] },
data: { videoUrl: null, audioUrl: null }, // row stays; scores/transcript
survive
});
} catch (err) {
[Link](`Failed to purge ${[Link]}:`, [Link]);
}
}
}

// run once daily at 3am

[Link]('0 3 * * *', purgeExpiredAnswers);

If BullMQ is already running for the AI pipeline, a repeatable BullMQ job is safer than node-cron
once more than one server instance is running, since cron schedules duplicate per-instance
while BullMQ repeatables do not.

4.3 Python / FastAPI / SQLAlchemy Implementation

The same pattern, for any part of the module built in Python.

class InterviewAnswer(Base):
__tablename__ = "interview_answers"
id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4()))
attempt_id = Column(String, ForeignKey("interview_attempts.id"), nullable=False)
video_url = Column(String, nullable=True)
audio_url = Column(String, nullable=True)
transcript = Column(String, nullable=True)
# ...existing fields...

is_deleted = Column(Boolean, default=False, nullable=False)

deleted_at = Column(DateTime, nullable=True)
purge_at = Column(DateTime, nullable=True)

__table_args__ = (Index("ix_answer_deleted_purge", "is_deleted", "purge_at"),)

alembic revision --autogenerate -m "add soft delete to interview_answers"

alembic upgrade head

THIRTY_DAYS = timedelta(days=30)

Page 10
 AI Interview Module — Database Guide

@[Link]("/answers/{answer_id}/delete", response_model=InterviewAnswerOut)
def soft_delete_answer(answer_id: str, db: Session = Depends(get_db),
user=Depends(get_current_user)):
answer = [Link](InterviewAnswer).join(InterviewAttempt).filter(
[Link] == answer_id
).first()
if not answer:
raise HTTPException(404, "Not found")
if [Link].user_id != [Link] and [Link] != "ADMIN":
raise HTTPException(403, "Not allowed")

now = [Link]()
answer.is_deleted = True
answer.deleted_at = now
answer.purge_at = now + THIRTY_DAYS
[Link]()
return answer

@[Link]("/answers/{answer_id}/restore", response_model=InterviewAnswerOut)
def restore_answer(answer_id: str, db: Session = Depends(get_db),
user=Depends(get_current_user)):
answer = [Link](InterviewAnswer).filter([Link] == answer_id).first()
if not answer or not answer.is_deleted:
raise HTTPException(400, "Nothing to restore")
answer.is_deleted = False
answer.deleted_at = None
answer.purge_at = None
[Link]()
return answer

[Link] helper and the scheduled purge, via APScheduler:

async def delete_file(file_url: str | None):

if not file_url:
return
path = file_url.replace(BUNNY_CDN_BASE_URL, "")
url = f"[Link]
async with [Link]() as client:
await [Link](url, headers={"AccessKey": BUNNY_API_KEY})

async def purge_expired_answers():

db: Session = SessionLocal()
try:
expired = [Link](InterviewAnswer).filter(
InterviewAnswer.is_deleted == True,
InterviewAnswer.purge_at <= [Link](),
).all()
for answer in expired:
await delete_file(answer.video_url)
await delete_file(answer.audio_url)
answer.video_url = None
answer.audio_url = None
[Link]()
finally:

Page 11
 AI Interview Module — Database Guide

[Link]()

# [Link]
scheduler = AsyncIOScheduler()

@app.on_event("startup")
async def start_scheduler():
scheduler.add_job(purge_expired_answers, "cron", hour=3, minute=0)
[Link]()

4.4 Raw PostgreSQL Implementation

For reference, or for manual administration via psql, the same mechanism expressed directly
in SQL.

ALTER TABLE interview_answers

ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN purge_at TIMESTAMP;

Soft-delete on request:

UPDATE interview_answers
SET is_deleted = TRUE, deleted_at = NOW(), purge_at = NOW() + INTERVAL '30 days'
WHERE id = $1;

Restore within the window:

UPDATE interview_answers
SET is_deleted = FALSE, deleted_at = NULL, purge_at = NULL
WHERE id = $1;

Finding what is due for permanent removal — run by whichever script performs the actual
storage deletion:

SELECT id, video_url, audio_url

FROM interview_answers
WHERE is_deleted = TRUE AND purge_at <= NOW();

Clearing the references once the real files are gone:

UPDATE interview_answers
SET video_url = NULL, audio_url = NULL
WHERE id = $1;

Postgres can self-schedule the column-clearing half of this with the pg_cron extension, but it
cannot make the outbound HTTP call to [Link]'s delete API — Postgres does not talk to
external services on its own. So pg_cron alone leaves orphaned files sitting in storage; an
external script (Node or Python, as above) is what actually has to call Bunny's API and is the
only way to fully complete the purge.

4.5 Choosing How to Run the Daily Purge

Option Best fit

Page 12
 AI Interview Module — Database Guide

node-cron / Python Single server instance; simplest setup, runs inside the
APScheduler existing app process.
BullMQ repeatable job / Multiple server instances; avoids the job firing once per
Celery beat instance.
pg_cron Only suitable if file deletion from object storage is
handled by a separate process — pg_cron can clear
database columns but cannot call external APIs.

Page 13
 AI Interview Module — Database Guide

5. Premium / Paid Access System

The pricing model (pay-per-attempt, subscription, or credit packs) and payment gateway
(Razorpay vs. Stripe) are not finalized yet, so the schema below is built to support any of
them without requiring a rework later. The core idea: track entitlements as their own concept,
separate from payment records. A user's first free attempt is simply a starting credit balance
— not a special case hardcoded into the interview-start logic.

5.1 Entitlement & Payment Schema

model UserEntitlement {
id String @id @default(uuid())
userId String @unique
freeAttemptsUsed Int @default(0)
freeAttemptsLimit Int @default(1) // change if free tier becomes per-
category
paidCredits Int @default(0) // pay-per-attempt or credit packs
isSubscribed Boolean @default(false)
subscriptionExpiresAt DateTime?
updatedAt DateTime @updatedAt
}

model Payment {
id String @id @default(uuid())
userId String
provider String? // "razorpay" | "stripe" — fill in once decided
providerPaymentId String?
amount Int // in paise/cents
currency String @default("INR")
planType String // "single_attempt" | "credit_pack_5" |
"subscription_monthly"
creditsGranted Int?
status String @default("PENDING") // PENDING | SUCCESS | FAILED
createdAt DateTime @default(now())
}

5.2 Access Check & Deduction Logic

One function, called at the top of the interview-start route, decides whether a student can
begin:

async function canStartInterview(userId) {

const entitlement = await [Link]({ where: { userId } });
if (!entitlement) return { allowed: true, reason: 'free_attempt' }; // first-ever
user

if ([Link] && [Link] > new Date()) {

return { allowed: true, reason: 'subscription' };
}
if ([Link] < [Link]) {
return { allowed: true, reason: 'free_attempt' };

Page 14
 AI Interview Module — Database Guide

}
if ([Link] > 0) {
return { allowed: true, reason: 'paid_credit' };
}
return { allowed: false, reason: 'payment_required' };
}

Deduction happens only once an attempt is actually created — never on the paywall check
itself, so a student does not lose a credit just for loading the page:

async function consumeEntitlement(userId, reason) {

if (reason === 'subscription') return; // unlimited, nothing to deduct

if (reason === 'free_attempt') {

await [Link]({
where: { userId },
create: { userId, freeAttemptsUsed: 1 },
update: { freeAttemptsUsed: { increment: 1 } },
});
} else if (reason === 'paid_credit') {
await [Link]({
where: { userId },
data: { paidCredits: { decrement: 1 } },
});
}
}

The same check, expressed directly in SQL for reference:

SELECT
CASE
WHEN is_subscribed AND subscription_expires_at > NOW() THEN 'subscription'
WHEN free_attempts_used < free_attempts_limit THEN 'free_attempt'
WHEN paid_credits > 0 THEN 'paid_credit'
ELSE 'payment_required'
END AS access_reason
FROM user_entitlements
WHERE user_id = $1;

Recording a successful payment and granting credits, wrapped in a transaction so a

payment is never recorded without the credit actually landing:

BEGIN;

INSERT INTO payments (user_id, provider, provider_payment_id, amount, plan_type,

credits_granted, status)
VALUES ($1, 'razorpay', $2, 19900, 'single_attempt', 1, 'SUCCESS');

UPDATE user_entitlements
SET paid_credits = paid_credits + 1, updated_at = NOW()
WHERE user_id = $1;

COMMIT;

Page 15
 AI Interview Module — Database Guide

5.3 Why This Stays Flexible

Whichever pricing model gets decided on, only the payment webhook handler changes —
the access-check logic above never does. Pay-per-attempt simply adds one credit per
successful payment. Credit packs add N credits based on the plan purchased. A
subscription sets isSubscribed and subscriptionExpiresAt, and the check short-circuits past
the credit logic entirely. The same applies to the gateway: switching between Razorpay and
Stripe only changes how a successful payment event is received and parsed in the webhook
— everything downstream of that stays identical.

Page 16
 AI Interview Module — Database Guide

6. Open Decisions & Next Steps

A few choices are still pending and worth settling before the paywall and free-tier messaging
are built on the frontend:

• Payment gateway — Razorpay (typical default for INR pricing) vs. Stripe (adds
international card support).
• Pricing model — pay-per-attempt, a monthly/yearly subscription, or pre-purchased
credit packs.
• Scope of the free tier — one free attempt ever, or one free attempt per interview
category. This changes whether UserEntitlement is keyed by user alone or by (user,
category), and changes how the paywall message reads to the student.
• Whether discarded retakes should be logged for audit (a small RetakeLog table
keyed by answerId, videoUrl, and attempt number) or simply overwritten, since
retakeCount on InterviewAnswer already covers the basic case.
• Posture analysis approach — server-side frame extraction and pose estimation (e.g.
MediaPipe) versus a lighter in-browser pass that logs metrics as JSON during
recording. The latter is cheaper and avoids shipping every frame through the
pipeline, but only suits self-improvement feedback rather than scored evaluation.

Once the gateway and pricing model are settled, the only remaining build is the webhook
handler that turns a successful payment event into a Payment row and the corresponding
UserEntitlement update — the schema and access-check logic in Section 5 do not need to
change regardless of which option is chosen.

Page 17