from flask_sqlalchemy import SQLAlchemy from datetime import datetime import json db = SQLAlchemy() # ✅ Import User model here, before Job and Application use it from backend.models.user import User class Job(db.Model): __tablename__ = 'jobs' id = db.Column(db.Integer, primary_key=True) role = db.Column(db.String(100), nullable=False) description = db.Column(db.Text, nullable=False) seniority = db.Column(db.String(50), nullable=False) skills = db.Column(db.Text, nullable=False) company = db.Column(db.String(100), nullable=False) date_posted = db.Column(db.DateTime, default=datetime.utcnow) # Number of interview questions for this job. Recruiters can set this # value when posting a job. Defaults to 3 to preserve existing # behaviour where the interview consists of three questions. The # interview API uses this field to determine when to stop asking # follow‑up questions. See backend/routes/interview_api.py for # details. num_questions = db.Column(db.Integer, nullable=False, default=3) recruiter_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True) recruiter = db.relationship('User', backref='posted_jobs') @property def skills_list(self): """Return a list of skills parsed from the JSON string stored in ``skills``. The ``skills`` column stores a JSON encoded list of skills (e.g. '["Python", "Flask"]'). In templates it is convenient to work with a Python list so that skills can be joined or iterated over. If parsing fails for any reason an empty list is returned. """ try: # Import json lazily to avoid circular imports at module import time. import json as _json return _json.loads(self.skills) if self.skills else [] except Exception: return [] def __repr__(self): return f"" class Application(db.Model): __tablename__ = 'applications' id = db.Column(db.Integer, primary_key=True) job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) name = db.Column(db.String(100), nullable=False) email = db.Column(db.String(100), nullable=False) resume_path = db.Column(db.String(255), nullable=True) cover_letter = db.Column(db.Text, nullable=True) extracted_features = db.Column(db.Text, nullable=True) status = db.Column(db.String(50), default='applied') date_applied = db.Column(db.DateTime, default=datetime.utcnow) interview_log = db.Column(db.Text) user = db.relationship('User', backref='applications') # Set up a relationship back to the Job so that templates can access # ``application.job`` directly. Without this relationship you'd need to # query the Job model manually in the route or template, which is less # convenient and can lead to additional database queries. job = db.relationship('Job', backref='applications', lazy='joined') def __repr__(self): return f"Application('{self.name}', '{self.email}', Job ID: {self.job_id})" def get_profile_data(self): try: return json.loads(self.extracted_features) if self.extracted_features else {} except: return {} def init_db(app): db.init_app(app) with app.app_context(): # Create any missing tables. SQLAlchemy does not automatically add # columns to existing tables, so we call create_all() first to ensure # new tables (like ``applications`` or ``jobs``) are present. db.create_all() # Dynamically add the ``num_questions`` column to the ``jobs`` table # if it is missing. When deploying an updated application against an # existing database, the new field will not appear until we run an # explicit ALTER TABLE. Inspect the current table schema and add # ``num_questions`` with a default of 3 if it doesn't exist. This # logic is idempotent: the ALTER TABLE statement runs only when # necessary. from sqlalchemy import inspect inspector = inspect(db.engine) try: columns = [col['name'] for col in inspector.get_columns('jobs')] if 'num_questions' not in columns: # SQLite supports adding new columns via ALTER TABLE. The # default value of 3 matches the default declared in the # Job model. If you are using a different database backend, # verify that this syntax is supported. db.session.execute('ALTER TABLE jobs ADD COLUMN num_questions INTEGER NOT NULL DEFAULT 3') db.session.commit() except Exception: # If inspection fails (e.g. the table does not exist yet), rely on # SQLAlchemy's create_all() to create a fresh schema with the # ``num_questions`` column. pass # Seed professional demo data (recruiter + applicant + a curated set of # jobs) so the platform always looks polished and the full demo flow # works after every restart. On Hugging Face the SQLite DB lives in # the ephemeral /tmp directory and is wiped on each restart, so this # idempotent seeding repopulates a clean, consistent dataset every time. seed_demo_data() # Known demo accounts used for the marketing demo. Passwords are intentionally # simple here because these are throwaway demo accounts on an ephemeral DB. DEMO_RECRUITER_EMAIL = 'hr@codingo.ai' DEMO_APPLICANT_EMAIL = 'candidate@codingo.ai' DEMO_PASSWORD = 'codingo123' def seed_demo_data(): """Idempotently seed a demo recruiter, a demo applicant and a curated set of professional job listings. Safe to call on every startup: each entity is only created when missing, and jobs are only inserted when the ``jobs`` table is empty. Any failure is rolled back and swallowed so seeding can never block app startup. """ try: # --- Demo recruiter (owns the seeded jobs so they appear in the HR # dashboard, which filters by recruiter_id) --- recruiter = User.query.filter_by(email=DEMO_RECRUITER_EMAIL).first() if recruiter is None: recruiter = User( username='Codingo HR', email=DEMO_RECRUITER_EMAIL, role='recruiter', ) recruiter.set_password(DEMO_PASSWORD) db.session.add(recruiter) db.session.commit() # --- Demo applicant (job-seeker role is 'unemployed') --- applicant = User.query.filter_by(email=DEMO_APPLICANT_EMAIL).first() if applicant is None: applicant = User( username='Demo Candidate', email=DEMO_APPLICANT_EMAIL, role='unemployed', ) applicant.set_password(DEMO_PASSWORD) db.session.add(applicant) db.session.commit() # --- Jobs: only seed when there are none, to avoid duplicates and to # never clobber jobs a recruiter may have posted at runtime --- if Job.query.count() == 0: demo_jobs = [ { 'role': 'Data Scientist', 'seniority': 'Mid-level', 'company': 'NorthBridge Analytics', 'skills': ['Python', 'SQL', 'Pandas', 'scikit-learn', 'Machine Learning', 'Statistics', 'Data Visualization'], 'description': ( "We are looking for a Data Scientist to turn raw data into " "actionable insight. You will design and evaluate machine " "learning models, run statistical analyses, and build clear " "visualizations that guide product and business decisions. " "You will collaborate closely with engineering and product " "teams to take models from prototype to production." ), }, { 'role': 'Data Engineer', 'seniority': 'Senior', 'company': 'Cloudbyte Systems', 'skills': ['Python', 'SQL', 'Apache Spark', 'Airflow', 'ETL', 'AWS', 'Data Warehousing'], 'description': ( "Join us as a Senior Data Engineer to design, build and " "maintain the data pipelines that power our analytics and " "machine learning platforms. You will own scalable ETL " "workflows, optimize our data warehouse, and ensure data " "quality and reliability across the organization." ), }, { 'role': 'Machine Learning Engineer', 'seniority': 'Mid-level', 'company': 'Vantix AI', 'skills': ['Python', 'TensorFlow', 'PyTorch', 'Machine Learning', 'MLOps', 'Model Deployment', 'Docker'], 'description': ( "As a Machine Learning Engineer you will take models from " "research into reliable, production-grade services. You will " "train and fine-tune models, build robust deployment and " "monitoring pipelines, and work with data scientists to " "ship features that delight our users." ), }, { 'role': 'NLP Engineer', 'seniority': 'Senior', 'company': 'Lingua Labs', 'skills': ['Python', 'NLP', 'Transformers', 'spaCy', 'PyTorch', 'LLMs', 'Hugging Face'], 'description': ( "We are hiring a Senior NLP Engineer to build state-of-the-art " "language understanding systems. You will develop models for " "text classification, information extraction and conversational " "AI, fine-tune large language models, and deploy them at scale " "to power our products." ), }, { 'role': 'Computer Vision Engineer', 'seniority': 'Mid-level', 'company': 'VisionWorks AI', 'skills': ['Python', 'OpenCV', 'Deep Learning', 'PyTorch', 'Image Processing', 'CNNs', 'Model Optimization'], 'description': ( "Join our team as a Computer Vision Engineer to develop " "image and video understanding systems. You will design and " "train deep learning models for detection, segmentation and " "recognition, optimize them for real-time performance, and " "integrate them into our production pipeline." ), }, ] for spec in demo_jobs: db.session.add(Job( role=spec['role'], description=spec['description'], seniority=spec['seniority'], skills=json.dumps(spec['skills']), company=spec['company'], recruiter_id=recruiter.id, num_questions=4, )) db.session.commit() # --- Pre-seed completed demo interviews for the demo candidate so the # HR dashboard is always populated even after a restart (the SQLite # DB is ephemeral on Hugging Face). Idempotent per (candidate, job): # a row is only created when one does not already exist. --- demo_interviews = [ { 'role': 'Data Scientist', 'skills': ['Python', 'SQL', 'Pandas', 'scikit-learn', 'Machine Learning', 'Data Visualization'], 'experience': [ 'Data Analyst at BrightData (2 years) — built dashboards and predictive models', 'Machine Learning Intern at NorthBridge Analytics — customer churn prediction', ], 'education': ['BSc in Computer Science, USAL'], 'qa_log': [ { 'question': "Hi, I'm LUNA, your AI recruiter. Can you tell me about your background and what drew you to data science?", 'answer': "I have around three years working with data. I started as a data analyst building dashboards, then moved into machine learning where I built churn and forecasting models in Python with scikit-learn. I love data science because it turns messy data into decisions that actually move the business.", 'evaluation': {'score': 'Excellent', 'feedback': 'Clear, specific background with concrete tools and real impact on the business.'}, }, { 'question': "How do you prevent a model from overfitting?", 'answer': "I use cross-validation to get an honest estimate of performance, keep the model as simple as the data allows, and apply regularization like L1 or L2. I also use more training data when possible, early stopping for neural nets, and I always compare training versus validation error to catch overfitting early.", 'evaluation': {'score': 'Excellent', 'feedback': 'Covers cross-validation, regularization, and monitoring train/validation gap accurately.'}, }, { 'question': "Walk me through a data science project you are proud of and the impact it had.", 'answer': "At NorthBridge I built a customer churn model. I engineered features from usage logs, trained a gradient boosting model, and reached about 0.86 ROC-AUC. The retention team used the risk scores to target outreach and we reduced monthly churn by roughly 12 percent.", 'evaluation': {'score': 'Good', 'feedback': 'Solid end-to-end project with a measurable result; could add more on validation and deployment.'}, }, { 'question': "What are your salary expectations? Are you looking for a full-time or part-time role, and do you prefer remote or on-site work?", 'answer': "I'm looking for a full-time role and I'm flexible on salary within the market range for a mid-level data scientist. I'm happy with hybrid or on-site, and comfortable fully remote as well.", 'evaluation': {'score': 'Excellent', 'feedback': 'Clear, reasonable and flexible answer on logistics.'}, }, ], }, { 'role': 'Data Engineer', 'skills': ['Python', 'SQL', 'Apache Spark', 'Airflow', 'ETL', 'AWS'], 'experience': [ 'Data Engineer at Cloudbyte Systems (3 years) — built batch and streaming pipelines', 'Backend Developer — automated ETL jobs and data quality checks', ], 'education': ['BSc in Software Engineering, USAL'], 'qa_log': [ { 'question': "Hi, I'm LUNA, your AI recruiter. Tell me about your experience and why data engineering?", 'answer': "I've spent three years as a data engineer building pipelines in Python and Spark, orchestrated with Airflow on AWS. I enjoy data engineering because reliable, well-modeled data is what makes everything else — analytics and machine learning — actually work.", 'evaluation': {'score': 'Excellent', 'feedback': 'Strong, specific experience with the exact stack the role needs.'}, }, { 'question': "How do you design a reliable ETL pipeline?", 'answer': "I make each step idempotent so re-runs are safe, add schema validation and data quality checks early, and design for incremental loads instead of full reloads. I orchestrate with Airflow, add retries and alerting on failures, and keep raw data so I can reprocess if logic changes.", 'evaluation': {'score': 'Excellent', 'feedback': 'Idempotency, validation, incremental loads and observability — a complete, senior answer.'}, }, { 'question': "Describe a time you optimized a slow data pipeline.", 'answer': "A nightly Spark job was taking over four hours. I found it was shuffling too much data, so I repartitioned on the join key, cached a reused dataframe, and switched some wide transformations to broadcast joins. It dropped to about forty minutes.", 'evaluation': {'score': 'Good', 'feedback': 'Concrete optimization with a real result; could mention profiling and cost trade-offs.'}, }, { 'question': "What are your salary expectations? Are you looking for a full-time or part-time role, and do you prefer remote or on-site work?", 'answer': "Full-time, and I'm open on compensation within the senior data engineer range. I prefer hybrid but I'm fully comfortable working remotely.", 'evaluation': {'score': 'Excellent', 'feedback': 'Direct and flexible on role type, pay and location.'}, }, ], }, ] for spec in demo_interviews: job = Job.query.filter_by(role=spec['role'], recruiter_id=recruiter.id).first() if job is None: continue existing = Application.query.filter_by(user_id=applicant.id, job_id=job.id).first() if existing is not None: continue db.session.add(Application( job_id=job.id, user_id=applicant.id, name=applicant.username, email=applicant.email, status='interviewed', extracted_features=json.dumps({ 'skills': spec['skills'], 'experience': spec['experience'], 'education': spec['education'], }), interview_log=json.dumps(spec['qa_log'], ensure_ascii=False), )) db.session.commit() except Exception as exc: # Never let seeding break startup. db.session.rollback() print(f"Demo data seeding skipped due to error: {exc}")