I’m utilizing Supabase for my database and have crafted SQL queries to update specific table columns. I’ve established pg_cron jobs to automatically run these queries periodically. The application I’m working on is a job application platform that tracks fields such as total_jobs (the total job openings available based on subscription), jobs_applied (the number of applications submitted by a user), and available_jobs (the remaining job applications the user can make). The calculation I expect is that if a user subscribes to a plan offering 500 jobs and has submitted 50 applications, the remaining jobs should be 450, which is derived from the equation available_jobs = total_jobs - jobs_applied. However, I’m encountering issues in production; the available_jobs sometimes displays negative values or equals jobs_applied. For instance, if a user has applied for 27 jobs out of a total of 50, it occasionally shows available_jobs as -27 or 27, but it never accurately reflects the expected result of 23. I suspect there may be inefficiencies in my implementation, and as I’m not an SQL expert, I’m seeking advice on how to resolve these problems. Below is a simplified overview of my SQL structure and cron jobs designed to manage the metrics:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT
);
CREATE TABLE metrics (
user_id INT REFERENCES users(id),
total_jobs NUMERIC,
jobs_applied NUMERIC,
available_jobs NUMERIC
);
CREATE TABLE applications (
user_id INT REFERENCES users(id)
);
CREATE TABLE plans (
plan_id TEXT DEFAULT 'id_AAAAAAAAAAAAAAAAAAAAAAAA',
user_id INT REFERENCES users(id)
);
INSERT INTO users(username) VALUES ('user1');
INSERT INTO metrics(user_id) VALUES (1);
INSERT INTO applications(user_id) VALUES (1), (1), (1);
INSERT INTO plans(user_id) VALUES (1);
-- Cron job to update jobs applied every hour
SELECT
cron.schedule (
'0 * * * *',
$$
UPDATE metrics
SET jobs_applied = (SELECT COUNT(user_id) FROM applications WHERE applications.user_id = metrics.user_id);
$$
);
-- Cron job to update total jobs from plans every hour
SELECT
cron.schedule (
'0 * * * *',
$$
UPDATE metrics
SET total_jobs = CASE plan_id
WHEN 'id_AAAAAAAAAAAAAAAAAAAAAAAA' THEN 100
WHEN 'id_BBBBBBBBBBBBBBBBBBBBBBBB' THEN 150
END
FROM plans
WHERE metrics.user_id = plans.user_id;
$$
);
-- Cron job to calculate available jobs
SELECT
cron.schedule (
'0 * * * *',
$$
UPDATE metrics
SET available_jobs = total_jobs - jobs_applied;
$$
);
Though this setup worked fine during development, I’m now facing these unexpected results in production and would appreciate any insight into improving my implementation.