120 lines
4.6 KiB
Python
120 lines
4.6 KiB
Python
import sqlite3
|
|
import datetime
|
|
import uuid
|
|
import hashlib
|
|
from pathlib import Path
|
|
|
|
def generate_job_hash(job_url: str) -> str:
|
|
return hashlib.sha256(job_url.encode('utf-8')).hexdigest()
|
|
|
|
SAMPLE_JOBS = [
|
|
{
|
|
"title": "Senior Software Engineer (Full Stack)",
|
|
"company": "Travelers Insurance",
|
|
"location": "Hartford, CT",
|
|
"is_remote": False,
|
|
"description": "Join our Hartford engineering team building next-generation digital cloud platform solutions using Next.js, React, Node.js, and PostgreSQL. Required skills: TypeScript, React, SQL, Cloud Architecture.",
|
|
"salary_min": 125000,
|
|
"salary_max": 165000,
|
|
"job_url": "https://careers.travelers.com/job/senior-software-engineer-hartford",
|
|
"source": "indeed"
|
|
},
|
|
{
|
|
"title": "Customer Support & Operations Specialist",
|
|
"company": "State of Connecticut - Department of Administrative Services",
|
|
"location": "Hartford & CT Statewide",
|
|
"is_remote": False,
|
|
"description": "Official State of CT posting. Manage public agency requests, support municipal administration systems, and streamline operations. Requirements: Customer Service, Administration, Communication, Problem Solving.",
|
|
"salary_min": 62000,
|
|
"salary_max": 84000,
|
|
"job_url": "https://www.jobapscloud.com/CT/specs/spec.asp?ClassNumber=2001",
|
|
"source": "jobaps_ct"
|
|
},
|
|
{
|
|
"title": "Remote Operations Associate",
|
|
"company": "Stripe",
|
|
"location": "Remote, USA",
|
|
"is_remote": True,
|
|
"description": "We are seeking a proactive Operations Associate to manage user onboardings, workflow automation, and cross-functional support across US remote teams. Skills: Operations, Communication, Project Management, Data Analysis.",
|
|
"salary_min": 85000,
|
|
"salary_max": 115000,
|
|
"job_url": "https://stripe.com/jobs/remote-operations-associate",
|
|
"source": "zip_recruiter"
|
|
},
|
|
{
|
|
"title": "IT Systems Support Technician",
|
|
"company": "Yale New Haven Health",
|
|
"location": "New Haven, CT",
|
|
"is_remote": False,
|
|
"description": "Provide tier-2 hardware, software, and network infrastructure support across Yale New Haven hospital facilities. Experience with Active Directory, Windows Server, and network troubleshooting required.",
|
|
"salary_min": 68000,
|
|
"salary_max": 88000,
|
|
"job_url": "https://www.ynhhs.org/careers/it-support-tech-new-haven",
|
|
"source": "indeed"
|
|
},
|
|
{
|
|
"title": "Full Stack React / Node Developer",
|
|
"company": "Vercel",
|
|
"location": "Remote, USA",
|
|
"is_remote": True,
|
|
"description": "Build high-performance web applications and serverless backend integrations with Next.js, React, Tailwind CSS, TypeScript, and Prisma ORM. 100% remote working environment.",
|
|
"salary_min": 140000,
|
|
"salary_max": 190000,
|
|
"job_url": "https://vercel.com/careers/full-stack-developer-remote",
|
|
"source": "zip_recruiter"
|
|
}
|
|
]
|
|
|
|
def seed_database():
|
|
db_path = Path(__file__).resolve().parent.parent / "web" / "prisma" / "dev.db"
|
|
conn = sqlite3.connect(str(db_path))
|
|
cursor = conn.cursor()
|
|
|
|
query = """
|
|
INSERT INTO Job (
|
|
id, jobUrlHash, title, company, location, isRemote,
|
|
description, salaryMin, salaryMax, jobUrl, source, datePosted,
|
|
createdAt, updatedAt
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT (jobUrlHash) DO UPDATE SET
|
|
title = excluded.title,
|
|
company = excluded.company,
|
|
location = excluded.location,
|
|
isRemote = excluded.isRemote,
|
|
description = excluded.description,
|
|
salaryMin = excluded.salaryMin,
|
|
salaryMax = excluded.salaryMax,
|
|
updatedAt = excluded.updatedAt;
|
|
"""
|
|
|
|
now_iso = datetime.datetime.now(datetime.timezone.utc).isoformat()
|
|
count = 0
|
|
|
|
for j in SAMPLE_JOBS:
|
|
job_hash = generate_job_hash(j["job_url"])
|
|
job_id = "job_" + str(uuid.uuid4()).replace("-", "")[:20]
|
|
|
|
cursor.execute(query, (
|
|
job_id,
|
|
job_hash,
|
|
j["title"],
|
|
j["company"],
|
|
j["location"],
|
|
1 if j["is_remote"] else 0,
|
|
j["description"],
|
|
j["salary_min"],
|
|
j["salary_max"],
|
|
j["job_url"],
|
|
j["source"],
|
|
now_iso,
|
|
now_iso,
|
|
now_iso
|
|
))
|
|
count += 1
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
print(f"Successfully seeded {count} job postings into {db_path.name}")
|
|
|
|
if __name__ == "__main__":
|
|
seed_database()
|