Skip to main content

​
Overview

The Interview Simulator uses SQLAlchemy ORM with SQLite for development (PostgreSQL recommended for production). The schema consists of four main tables that track users, interview sessions, conversation messages, and feedback results.

​
Entity Relationship Diagram

┌──────────┐         ┌───────────┐         ┌──────────┐
│  User    │◄───┐    │  Session  │◄────┬───│ Message  │
│          │    │    │           │     │   │          │
│ id (PK)  │    └────│ user_id   │     │   │ id (PK)  │
│ email    │         │ id (PK)   │     └───│session_id│
└──────────┘         │ ...       │         │ role     │
                     └─────┬─────┘         │ content  │
                           │               └──────────┘
                           │
                           │    ┌──────────┐
                           └────│ Feedback │
                                │          │
                                │ id (PK)  │
                                │session_id│
                                │ score    │
                                └──────────┘

​
Tables

​
User

Stores optional user information for future authentication features. 
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email VARCHAR(120) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.now)

    sessions = db.relationship(
        "Session", backref="user", lazy=True, cascade="all, delete-orphan"
    )
FieldTypeDescription
idINTEGERPrimary key, auto-incremented
emailVARCHAR(120)User email address (unique, nullable)
created_atDATETIMEAccount creation timestamp
Relationships:
  • One-to-many with Session (a user can have multiple interview sessions)
  • Cascade delete: Deleting a user removes all their sessions
Current usage: Currently nullable and not enforced. Used for future authentication implementation.

​
Session

Represents a single interview session with associated job details and documents. 
CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    job_title VARCHAR(200) NOT NULL,
    company_name VARCHAR(200) NOT NULL,
    cv_text TEXT,
    job_description_text TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

class Session(db.Model):
    __tablename__ = "sessions"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    job_title = db.Column(db.String(200), nullable=False)
    company_name = db.Column(db.String(200), nullable=False)
    cv_text = db.Column(db.Text, nullable=True)
    job_description_text = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.now)

    messages = db.relationship(
        "Message", backref="session", lazy=True, cascade="all, delete-orphan"
    )
    feedback = db.relationship(
        "Feedback",
        backref="session",
        lazy=True,
        uselist=False,
        cascade="all, delete-orphan",
    )
FieldTypeDescription
idINTEGERPrimary key, auto-incremented
user_idINTEGERForeign key to users.id (nullable)
job_titleVARCHAR(200)Target job position (e.g., “Senior Backend Engineer”)
company_nameVARCHAR(200)Target company (e.g., “Google”)
cv_textTEXTExtracted text from uploaded CV/resume
job_description_textTEXTFull job posting text
created_atDATETIMESession creation timestamp
Relationships:
  • Many-to-one with User (optional association)
  • One-to-many with Message (conversation history)
  • One-to-one with Feedback (interview results)
  • Cascade delete: Deleting a session removes all messages and feedback
Validation rules:
  • job_title must be non-empty and ≤ 200 characters
  • company_name must be non-empty
  • Session is “ready for interview” when both cv_text and job_description_text are populated

​
Message

Stores conversation messages between the user and AI interviewer. 
CREATE TABLE messages (
    id INTEGER PRIMARY KEY,
    session_id INTEGER NOT NULL REFERENCES sessions(id),
    role VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

class Message(db.Model):
    __tablename__ = "messages"

    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.Integer, db.ForeignKey("sessions.id"), nullable=False)
    role = db.Column(db.String(20), nullable=False)  # 'system', 'assistant', 'user'
    content = db.Column(db.Text, nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.now)
FieldTypeDescription
idINTEGERPrimary key, auto-incremented
session_idINTEGERForeign key to sessions.id
roleVARCHAR(20)Message sender: 'assistant' (AI), 'user' (candidate), or 'system'
contentTEXTFull message text
timestampDATETIMEMessage creation time
Relationships:
  • Many-to-one with Session
Role types:
  • assistant: Questions from the AI interviewer
  • user: Answers from the candidate
  • system: Internal system messages (rarely used)
Business rules:
  • Interview completes after 8 assistant messages (MAX_QUESTIONS = 8)
  • Messages are ordered by timestamp for conversation flow
  • Message count determines interview progress

​
Feedback

Stores AI-generated feedback and scores for completed interviews. 
CREATE TABLE feedback (
    id INTEGER PRIMARY KEY,
    session_id INTEGER NOT NULL REFERENCES sessions(id),
    interview_score INTEGER,
    strengths TEXT,
    weaknesses TEXT,
    cv_improvements TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

class Feedback(db.Model):
    __tablename__ = "feedback"

    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.Integer, db.ForeignKey("sessions.id"), nullable=False)
    interview_score = db.Column(db.Integer, nullable=True)  # 1-10
    strengths = db.Column(db.Text, nullable=True)
    weaknesses = db.Column(db.Text, nullable=True)
    cv_improvements = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.now)
FieldTypeDescription
idINTEGERPrimary key, auto-incremented
session_idINTEGERForeign key to sessions.id (unique - one feedback per session)
interview_scoreINTEGEROverall performance score (1-10 scale)
strengthsTEXTWhat the candidate did well (markdown formatted)
weaknessesTEXTAreas for improvement (markdown formatted)
cv_improvementsTEXTSpecific CV optimization suggestions for the role
created_atDATETIMEFeedback generation timestamp
Relationships:
  • One-to-one with Session (enforced by uselist=False)
Score interpretation:
  • 1-3: Needs significant improvement
  • 4-6: Moderate performance, room for growth
  • 7-8: Strong performance
  • 9-10: Exceptional performance
Generation process:
  1. Interview must be complete (8 questions answered)
  2. AI analyzes all conversation messages
  3. Compares responses against CV and job description
  4. Generates structured feedback
  5. Stored as single feedback record per session

​
Indexes and Performance

Recommended indexes for production: 
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_messages_session_id ON messages(session_id);
CREATE INDEX idx_messages_role ON messages(role);
CREATE INDEX idx_feedback_session_id ON feedback(session_id);
CREATE INDEX idx_sessions_created_at ON sessions(created_at DESC);
Query optimization strategies:
  • Use db.joinedload() for eager loading relationships (see repositories)
  • Filter by role when counting messages: count_messages(session_id, role='assistant')
  • Order sessions by created_at DESC for recent sessions query

​
Data Lifecycle

  1. Session Creation: User submits job title and company name → Session record created
  2. Document Upload: CV and job description uploaded → cv_text and job_description_text populated
  3. Interview Start: First AI question generated → First Message with role='assistant' created
  4. Conversation: User answers → Alternating Message records with role='user' and role='assistant'
  5. Completion: 8 questions answered → Feedback record generated
  6. Viewing: User views results → Session loaded with messages and feedback

​
Database Migrations

The application uses SQLAlchemy’s db.create_all() for development. For production: 
# In app/__init__.py:35
with app.app_context():
    db.create_all()
For production migrations, consider:
  • Alembic: SQLAlchemy’s migration tool
  • Flask-Migrate: Flask wrapper around Alembic
flask db init
flask db migrate -m "Initial migration"
flask db upgrade

​
Backup and Retention

Development:
  • SQLite file stored at instance/app.db
  • Simple file backup with cp instance/app.db backups/app.db.$(date +%Y%m%d)
Production recommendations:
  • Use PostgreSQL with automated backups
  • Implement soft deletes for sessions (add deleted_at column)
  • Archive old sessions after 90 days
  • Anonymize CV text for privacy compliance

Build docs developers (and LLMs) love

Get started for freeTalk to us