-- ============================================================ -- LocalJobs — Local job board network -- Schema for MySQL 8 / MariaDB 10.5+ -- -- One database powers: the admin, every generated local job site, -- and the employer + jobseeker accounts on those sites. -- -- Unlike a static directory builder, generated sites here read and -- write this database live (jobs, applications, accounts), so job -- approvals and applications take effect immediately with no rebuild. -- ============================================================ SET NAMES utf8mb4; -- ------------------------------------------------------------ -- Admin users -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, -- bcrypt full_name VARCHAR(120), email VARCHAR(190), created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Geography -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS countries ( id INT AUTO_INCREMENT PRIMARY KEY, code CHAR(2) UNIQUE NOT NULL, -- ISO-3166-1 alpha-2 name VARCHAR(80) NOT NULL, adzuna_code CHAR(2) -- Adzuna's country slug, usually = lower(code) ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS cities ( id INT AUTO_INCREMENT PRIMARY KEY, country_id INT NOT NULL, name VARCHAR(120) NOT NULL, INDEX(country_id), FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- A local recruitment catchment: the town/district a site serves. CREATE TABLE IF NOT EXISTS areas ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, -- "Redditch" slug VARCHAR(140), -- "redditch" postcode VARCHAR(20), -- "B97" — outward code, used for Adzuna + local SEO county VARCHAR(120), -- "Worcestershire" — used in address schema + copy country_id INT NOT NULL, city_id INT, latitude DECIMAL(10,7), -- optional; improves JobPosting geo schema longitude DECIMAL(10,7), radius_km SMALLINT DEFAULT 16, -- catchment radius used in feed queries + copy nearby_localities JSON, -- ["Studley","Astwood Bank",…] drives the coverage block + SEO travel_hubs JSON, -- ["Redditch railway station","A441"] used in localised copy created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(country_id), FOREIGN KEY (country_id) REFERENCES countries(id), FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Sectors — the job taxonomy. Used three ways: -- 1. filter facets on every site -- 2. optional site specialisation (a site can cover one sector only) -- 3. the keyword set used when pulling from the jobs feed -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS sectors ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, -- "Warehouse & Logistics" slug VARCHAR(140) UNIQUE NOT NULL, description TEXT, icon_key VARCHAR(40), -- names an inline SVG in the template icon set feed_keywords VARCHAR(500), -- "warehouse, picker packer, forklift" → Adzuna "what" terms match_terms JSON, -- ["warehouse","forklift","picker"] → auto-classify incoming jobs seo_keywords JSON, -- {primary,variants[],modifiers[]} with [area]/[postcode] placeholders sort_order INT DEFAULT 100, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(active, sort_order) ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Sites — one local job board. Normally one per area; optionally -- narrowed to a single sector ("Care jobs in Redditch"). -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS sites ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, -- "Redditch Jobs" slug VARCHAR(180) UNIQUE NOT NULL, -- folder name under SITES_ROOT domain VARCHAR(190), -- "redditchjobs.co.uk" (no scheme) area_id INT NOT NULL, sector_id INT, -- NULL = covers every sector tagline VARCHAR(255), -- "Jobs in Redditch, Studley and the B97 area" status ENUM('draft','built','live','paused') NOT NULL DEFAULT 'draft', -- Localised recruitment-company copy. Each is plain text/HTML written by -- admin or generated with Claude, then snapshotted into the site folder. about_html MEDIUMTEXT, mission_html MEDIUMTEXT, employers_html MEDIUMTEXT, -- pitch shown on the "post a job" page contact_name VARCHAR(120), contact_email VARCHAR(190), contact_phone VARCHAR(40), contact_address VARCHAR(255), contact_hours VARCHAR(190), -- "Mon–Fri, 9am–5:30pm" founded_line VARCHAR(255), -- "Recruiting across north Worcestershire since 2019" -- Presentation theme JSON, -- {accent_hex, accent_ink_hex, mark_style, …} meta_title VARCHAR(255), meta_desc VARCHAR(500), faq JSON, -- [{q,a}] rendered + FAQPage schema custom_head MEDIUMTEXT, -- raw HTML before (analytics, verification) custom_body MEDIUMTEXT, -- raw HTML before -- Jobs feed controls (Adzuna). Per-site switches; the global kill switch -- lives in settings.feeds_enabled / settings.feed_jobs_visible. feed_enabled TINYINT(1) NOT NULL DEFAULT 1, -- allow fetching new feed jobs for this site feed_visible TINYINT(1) NOT NULL DEFAULT 1, -- show already-fetched feed jobs on this site feed_keywords VARCHAR(500), -- override; blank = derive from sector(s) feed_max_days SMALLINT UNSIGNED DEFAULT 21, -- freshness window passed to the API feed_max_results SMALLINT UNSIGNED DEFAULT 150,-- per-run cap feed_auto_approve TINYINT(1) NOT NULL DEFAULT 0, -- 0 = feed jobs land in the moderation queue feed_last_run_at DATETIME, -- Employer controls employer_signup_open TINYINT(1) NOT NULL DEFAULT 1, employer_auto_approve TINYINT(1) NOT NULL DEFAULT 0, -- 0 = admin approves each new employer job_auto_approve TINYINT(1) NOT NULL DEFAULT 0, -- 0 = admin approves each employer job job_default_days SMALLINT UNSIGNED DEFAULT 30, -- employer job shelf life built_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(status), INDEX(domain), FOREIGN KEY (area_id) REFERENCES areas(id), FOREIGN KEY (sector_id) REFERENCES sectors(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Employers — local businesses with a login. Registration is -- moderated by default: status starts 'pending'. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS employers ( id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, -- the site they registered on ("home" site) email VARCHAR(190) NOT NULL, password VARCHAR(255) NOT NULL, company_name VARCHAR(190) NOT NULL, contact_name VARCHAR(190), phone VARCHAR(40), website_url VARCHAR(500), logo_path VARCHAR(500), -- relative path under UPLOADS_ROOT about MEDIUMTEXT, address_line VARCHAR(255), town VARCHAR(120), postcode VARCHAR(20), company_size VARCHAR(40), status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending', reject_reason VARCHAR(500), approved_at DATETIME, approved_by INT, last_login_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_employer_email (email), INDEX idx_site_status (site_id, status), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Jobseekers — candidate accounts. Open registration. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS jobseekers ( id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, -- site they registered on email VARCHAR(190) NOT NULL, password VARCHAR(255) NOT NULL, full_name VARCHAR(190) NOT NULL, phone VARCHAR(40), headline VARCHAR(190), -- "Forklift driver, 6 years" town VARCHAR(120), postcode VARCHAR(20), right_to_work TINYINT(1), -- self-declared; shown to employers has_transport TINYINT(1), cv_path VARCHAR(500), -- default CV, reused across applications cv_filename VARCHAR(255), cv_uploaded_at DATETIME, summary MEDIUMTEXT, status ENUM('active','suspended') NOT NULL DEFAULT 'active', last_login_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_seeker_email (email), INDEX idx_site (site_id), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Jobs — both employer-posted and feed-imported, in one table so -- the public site has a single query path and one visibility rule. -- -- Visibility rule enforced by lib/jobs_repo.php: -- status='approved' AND visible=1 -- AND (expires_at IS NULL OR expires_at > NOW()) -- AND (source='employer' -- OR (sites.feed_visible=1 AND settings.feed_jobs_visible=1)) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS jobs ( id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, employer_id INT, -- NULL for feed jobs sector_id INT, source ENUM('employer','adzuna') NOT NULL DEFAULT 'employer', external_id VARCHAR(120), -- feed provider's posting id (dedupe key) external_source VARCHAR(40), -- "adzuna" fuzzy_key VARCHAR(255), -- normalised title|company|date, cross-source dedupe title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, -- url segment; unique per site with id suffix summary VARCHAR(500), -- short teaser used on cards + meta description description MEDIUMTEXT, -- full text (HTML allowed, sanitised on save) responsibilities MEDIUMTEXT, requirements MEDIUMTEXT, benefits MEDIUMTEXT, -- Denormalised company display so feed jobs render identically to -- employer jobs without a join to a company that doesn't exist here. company_name VARCHAR(190), company_logo VARCHAR(500), employment_type ENUM('full_time','part_time','contract','temporary','apprenticeship','internship','volunteer') NOT NULL DEFAULT 'full_time', work_mode ENUM('on_site','hybrid','remote') NOT NULL DEFAULT 'on_site', hours_text VARCHAR(120), -- "37.5 hrs, Mon–Fri" / "Evenings & weekends" shift_pattern VARCHAR(120), salary_min DECIMAL(11,2), salary_max DECIMAL(11,2), salary_period ENUM('hour','day','week','month','year') DEFAULT 'year', salary_currency CHAR(3) DEFAULT 'GBP', salary_text VARCHAR(120), -- free text override, e.g. "Competitive + bonus" salary_hidden TINYINT(1) NOT NULL DEFAULT 0, salary_estimated TINYINT(1) NOT NULL DEFAULT 0, -- feed-predicted salary, flagged to users location_text VARCHAR(190), -- "Redditch, Worcestershire" postcode VARCHAR(20), latitude DECIMAL(10,7), longitude DECIMAL(10,7), apply_mode ENUM('internal','external','email') NOT NULL DEFAULT 'internal', apply_url VARCHAR(1000), -- external apply (all feed jobs) apply_email VARCHAR(190), apply_questions JSON, -- [{key,label,type,required,options[]}] extra screening questions status ENUM('draft','pending','approved','rejected','expired','archived') NOT NULL DEFAULT 'pending', visible TINYINT(1) NOT NULL DEFAULT 1, -- individual on/off switch, independent of status featured TINYINT(1) NOT NULL DEFAULT 0, reject_reason VARCHAR(500), posted_at DATETIME, -- displayed date; feed jobs use the provider's date expires_at DATETIME, approved_at DATETIME, approved_by INT, view_count INT NOT NULL DEFAULT 0, apply_click_count INT NOT NULL DEFAULT 0, -- external-apply clicks application_count INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_site_external (site_id, external_source, external_id), INDEX idx_live (site_id, status, visible, expires_at), INDEX idx_site_slug (site_id, slug), INDEX idx_employer (employer_id, status), INDEX idx_sector (sector_id), INDEX idx_source (source), INDEX idx_posted (posted_at), INDEX idx_fuzzy (site_id, fuzzy_key), FULLTEXT KEY ft_search (title, summary, description, company_name), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE, FOREIGN KEY (employer_id) REFERENCES employers(id) ON DELETE CASCADE, FOREIGN KEY (sector_id) REFERENCES sectors(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Applications — jobseeker → job. Employers see their own; -- admin sees all. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS applications ( id INT AUTO_INCREMENT PRIMARY KEY, job_id INT NOT NULL, jobseeker_id INT NOT NULL, site_id INT NOT NULL, -- denormalised for fast admin filtering employer_id INT, -- denormalised; NULL if job later unlinked cover_letter MEDIUMTEXT, cv_path VARCHAR(500), -- snapshot at apply time (profile CV may change later) cv_filename VARCHAR(255), answers JSON, -- responses to jobs.apply_questions phone VARCHAR(40), -- snapshot of contact details at apply time email VARCHAR(190), full_name VARCHAR(190), status ENUM('submitted','reviewed','shortlisted','interviewing','offered','rejected','hired','withdrawn') NOT NULL DEFAULT 'submitted', employer_note MEDIUMTEXT, -- private note, never shown to the candidate rating TINYINT, -- 1–5 employer rating viewed_at DATETIME, status_changed_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_one_per_job (job_id, jobseeker_id), INDEX idx_job (job_id, status), INDEX idx_seeker (jobseeker_id), INDEX idx_employer (employer_id, status), INDEX idx_site (site_id, created_at), FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY (jobseeker_id) REFERENCES jobseekers(id) ON DELETE CASCADE, FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Saved / shortlisted jobs for a jobseeker. CREATE TABLE IF NOT EXISTS saved_jobs ( jobseeker_id INT NOT NULL, job_id INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (jobseeker_id, job_id), INDEX idx_job (job_id), FOREIGN KEY (jobseeker_id) REFERENCES jobseekers(id) ON DELETE CASCADE, FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Email job alerts captured on each site. CREATE TABLE IF NOT EXISTS job_alerts ( id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, email VARCHAR(190) NOT NULL, keywords VARCHAR(190), sector_id INT, frequency ENUM('daily','weekly') NOT NULL DEFAULT 'weekly', token CHAR(32) NOT NULL, -- unsubscribe token confirmed TINYINT(1) NOT NULL DEFAULT 1, last_sent_at DATETIME, unsubscribed_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_site_email_kw (site_id, email, keywords), INDEX idx_token (token), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE, FOREIGN KEY (sector_id) REFERENCES sectors(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Moderation + operations trail -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS job_events ( id BIGINT AUTO_INCREMENT PRIMARY KEY, job_id INT NOT NULL, event_type VARCHAR(40) NOT NULL, -- created|submitted|approved|rejected|hidden|shown|expired|edited actor_type ENUM('admin','employer','system') NOT NULL DEFAULT 'system', actor_id INT, detail VARCHAR(500), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_job (job_id, created_at), FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- One row per feed fetch, for the Feeds console. CREATE TABLE IF NOT EXISTS feed_runs ( id INT AUTO_INCREMENT PRIMARY KEY, site_id INT, source VARCHAR(40) NOT NULL DEFAULT 'adzuna', trigger_type ENUM('manual','cron') NOT NULL DEFAULT 'manual', requested INT DEFAULT 0, -- rows returned by the provider inserted INT DEFAULT 0, updated INT DEFAULT 0, skipped INT DEFAULT 0, -- duplicates errors TEXT, duration_ms INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_site_time (site_id, created_at), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Lightweight per-day stats per site (no cookies, no personal data). CREATE TABLE IF NOT EXISTS site_stats_daily ( id BIGINT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, day DATE NOT NULL, job_views INT NOT NULL DEFAULT 0, searches INT NOT NULL DEFAULT 0, applications INT NOT NULL DEFAULT 0, apply_clicks INT NOT NULL DEFAULT 0, registrations INT NOT NULL DEFAULT 0, UNIQUE KEY uq_site_day (site_id, day), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- What people search for, including zero-result searches (unmet demand). CREATE TABLE IF NOT EXISTS search_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, query_text VARCHAR(160), location_text VARCHAR(120), sector_id INT, result_count INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_site_time (site_id, created_at), INDEX idx_query (query_text(40)), FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Shared legal / boilerplate copy reused by every site. CREATE TABLE IF NOT EXISTS site_texts ( `key` VARCHAR(64) PRIMARY KEY, body MEDIUMTEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS settings ( `key` VARCHAR(64) PRIMARY KEY, `value` TEXT ) ENGINE=InnoDB CHARSET=utf8mb4; -- Simple outbound mail queue so a slow SMTP host never blocks a request. CREATE TABLE IF NOT EXISTS mail_queue ( id BIGINT AUTO_INCREMENT PRIMARY KEY, to_email VARCHAR(190) NOT NULL, to_name VARCHAR(190), subject VARCHAR(255) NOT NULL, body_html MEDIUMTEXT, body_text MEDIUMTEXT, site_id INT, status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued', attempts TINYINT NOT NULL DEFAULT 0, last_error VARCHAR(500), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME, INDEX idx_status (status, created_at) ) ENGINE=InnoDB CHARSET=utf8mb4; -- ============================================================ -- Seed data -- ============================================================ INSERT IGNORE INTO countries (code, name, adzuna_code) VALUES ('GB','United Kingdom','gb'), ('IE','Ireland','ie'), ('US','United States','us'), ('CA','Canada','ca'), ('AU','Australia','au'); INSERT IGNORE INTO cities (country_id, name) SELECT id,'Birmingham' FROM countries WHERE code='GB' UNION SELECT id,'Manchester' FROM countries WHERE code='GB' UNION SELECT id,'Leeds' FROM countries WHERE code='GB' UNION SELECT id,'London' FROM countries WHERE code='GB' UNION SELECT id,'Bristol' FROM countries WHERE code='GB' UNION SELECT id,'Dublin' FROM countries WHERE code='IE'; -- A practical starting taxonomy for local/regional job boards. The -- match_terms drive auto-classification of incoming feed jobs. INSERT IGNORE INTO sectors (name, slug, description, icon_key, feed_keywords, match_terms, sort_order) VALUES ('Warehouse & Logistics','warehouse-logistics','Picking, packing, forklift, stock control and distribution roles.','box', 'warehouse, picker packer, forklift, distribution', '["warehouse","forklift","picker","packer","fulfilment","goods in","stock control","distribution","logistics"]',10), ('Driving & Delivery','driving-delivery','HGV, van, multi-drop delivery and courier work.','truck', 'driver, hgv, delivery driver, van driver', '["driver","hgv","lgv","van driver","courier","delivery","multi drop","class 1","class 2"]',20), ('Care & Support','care-support','Care assistants, support workers, home care and supported living.','heart', 'care assistant, support worker, home care', '["care assistant","carer","support worker","healthcare assistant","home care","domiciliary","supported living"]',30), ('Healthcare & Nursing','healthcare-nursing','Nurses, healthcare assistants, dental and clinical support.','cross', 'nurse, healthcare assistant, dental nurse', '["nurse","nursing","rgn","rmn","dental","clinical","paramedic","phlebotom"]',40), ('Hospitality & Catering','hospitality-catering','Chefs, bar, waiting, kitchen and hotel roles.','cup', 'chef, bar staff, waiting staff, kitchen porter', '["chef","kitchen","waiter","waitress","bar staff","barista","hospitality","catering","housekeep"]',50), ('Retail & Customer Service','retail-customer-service','Shop floor, supervisors, contact centre and customer support.','bag', 'retail assistant, customer service, sales assistant', '["retail","sales assistant","shop","store","customer service","customer advisor","call centre","contact centre"]',60), ('Construction & Trades','construction-trades','Site work, electricians, plumbers, joiners and labourers.','tools', 'construction, electrician, plumber, labourer', '["construction","electrician","plumber","joiner","carpenter","labourer","groundworker","site manager","cscs"]',70), ('Manufacturing & Engineering','manufacturing-engineering','Production, CNC, maintenance and quality roles.','gear', 'production operative, cnc, maintenance engineer', '["production","machine operator","cnc","maintenance engineer","manufacturing","assembly","quality inspector","fabricat"]',80), ('Office & Administration','office-administration','Admin, reception, data entry and business support.','folder', 'administrator, receptionist, office assistant', '["administrator","admin assistant","receptionist","data entry","office manager","business support","secretary"]',90), ('Accounting & Finance','accounting-finance','Bookkeeping, payroll, credit control and accountancy.','calculator', 'accounts assistant, bookkeeper, payroll', '["accounts","bookkeeper","payroll","credit control","finance assistant","accountant","purchase ledger"]',100), ('Education & Childcare','education-childcare','Teaching, teaching assistants, nursery and youth work.','book', 'teaching assistant, nursery practitioner, teacher', '["teacher","teaching assistant","nursery","childcare","early years","tutor","lecturer","learning support"]',110), ('Cleaning & Facilities','cleaning-facilities','Cleaning, caretaking, grounds and facilities management.','spray', 'cleaner, caretaker, facilities', '["cleaner","cleaning","caretaker","janitor","facilities","grounds","hygiene operative"]',120), ('Security','security','Door supervision, static guarding and CCTV roles.','shield', 'security officer, door supervisor', '["security officer","security guard","door supervisor","sia","cctv"]',130), ('IT & Digital','it-digital','Support, development, data and digital marketing.','monitor', 'it support, developer, digital marketing', '["it support","developer","software","helpdesk","network","data analyst","digital marketing","seo"]',140), ('Other','other','Roles that do not fit the categories above.','dots','', '[]', 999); INSERT IGNORE INTO site_texts (`key`, body) VALUES ('terms', '

Terms of use

By using this website you agree to these terms. We publish job vacancies from local employers and from licensed job feeds. We are not the employer for any role advertised unless the advert says so explicitly.

Accuracy of adverts

Employers are responsible for the accuracy of their own adverts. We review each advert before it appears, but we cannot guarantee that a role is still open, that the pay stated is accurate, or that an employer will respond to your application.

Your account

Keep your password private. You are responsible for activity on your account. You may close your account at any time from your account settings.

Fair use

Do not scrape, republish, or resell listings from this site. Do not post adverts that are discriminatory, misleading, illegal, or that charge a fee to the jobseeker.

Never pay for a job

A legitimate employer will never ask you to pay for work, for training you must buy up front, or for a DBS check before an offer. Report anything suspicious to us and we will remove the advert.

'), ('privacy', '

Privacy notice

This notice explains what we do with your personal data when you use this website.

What we collect

Jobseekers: your name, email, phone number, town or postcode, CV, and the content of any application you submit.
Employers: your name, business name, business contact details, and the adverts you post.

What we do with it

When you apply for a job, your application and CV are sent to that employer so they can consider you for the role. We do not sell your data and we do not share your CV with anyone other than the employer whose job you applied for.

How long we keep it

Applications are kept for 12 months from the date you apply, then deleted. Accounts inactive for 24 months are deleted. You can delete your account and CV yourself at any time from your account settings.

Your rights

You can ask us for a copy of your data, ask us to correct it, or ask us to delete it. Contact us using the details on our contact page and we will respond within one month.

Cookies

We use one essential cookie to keep you signed in. We do not use advertising cookies.

'), ('cookie_notice','This site uses one essential cookie to keep you signed in. No advertising or tracking cookies.'), ('safety_notice','Never pay to apply for a job, and never send bank details before a written offer. If an advert asks you to, close it and tell us.'); INSERT IGNORE INTO settings (`key`, `value`) VALUES ('feeds_enabled', '1'), -- master switch: allow feed fetching at all ('feed_jobs_visible', '1'), -- master switch: show feed jobs on every site ('feed_source', 'adzuna'), ('adzuna_app_id', ''), ('adzuna_app_key', ''), ('adzuna_country', 'gb'), ('feed_attribution', 'Some vacancies on this site are supplied by Adzuna.'), ('default_currency', 'GBP'), ('mail_from_email', 'no-reply@example.com'), ('mail_from_name', 'Local Jobs'), ('notify_admin_email', ''), ('cv_max_mb', '5'), ('brand_network_name', 'Local Jobs Network'); -- Default admin — username: admin / password: changeme INSERT IGNORE INTO users (username, password, full_name) VALUES ('admin', '$2y$10$9ko0nEvWH0MMDLdoTvgOvuuoUJnhSp2vlKkdF8vEEV2Mw196WvaAy', 'Administrator');