Database Schema

Entity Relationship Diagram

erDiagram

    scrape_cache {
        int         id              PK  "auto_increment"
        varchar255  url             UK  "raw AMS URL"
        char64      url_hash            "STORED GENERATED from url"
        json        data_payload        "full scraped item JSON"
        datetime    scraped_at
        int         fk_job_id       FK  "NULL until synced to jobs"
    }

    jobs {
        int         id              PK  "auto_increment"
        varchar255  url             UK
        char64      url_hash            "STORED GENERATED from url"
        varchar45   position
        varchar45   order_number        "NULL until Stage 3"
        varchar45   portal              "e.g. ams"
        varchar45   employment_relationship
        varchar45   contract_type
        varchar45   detailed_location
        varchar45   education
        varchar45   original_salary
        varchar45   start_timeline
        varchar45   occupational_group
        date        publication_date
        datetime    created_at
        datetime    updated_at
        int         company_id      FK  "NULL until synced"
        int         location_id     FK  "NULL until synced"
    }

    companies {
        int         id              PK  "auto_increment"
        varchar255  company_crawler_name UK  "raw name from scraper"
        varchar45   company_url
        varchar45   company_info2
    }

    locations {
        int         id              PK  "auto_increment"
        varchar45   zipcode             "match key from payload"
        varchar45   location
        varchar45   city
        varchar45   municipality
    }

    scrape_cache    ||--o|   jobs        : "fk_job_id → jobs.id"
    jobs            }o--||   companies   : "company_id → companies.id"
    jobs            }o--||   locations   : "location_id → locations.id"

Hold "Ctrl" to enable pan & zoom