-- This is the schema of Yarn's database.
-- This is purely provided for convenient perusal, not actually used for anything.
-- The migrations in the migrations folder are what actually defines the database.
-- Use `just generate_schema` to regenerate this file.

\restrict 5TwZEqvfJAXp4Vgsv1dy0VWECvJ7sjDYUriACmqyR3gGzsoIGhlpdOZZtrwcYJE

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA yarn;

ALTER SCHEMA yarn OWNER TO yarn;

CREATE FUNCTION yarn.update_timestamp() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    new.updated_at = transaction_timestamp();
    return new;
end;
$$;

ALTER FUNCTION yarn.update_timestamp() OWNER TO yarn;

COMMENT ON FUNCTION yarn.update_timestamp() IS 'Used to update updated_at fields on yarn''s tables via an "on update" trigger.';

SET default_tablespace = '';

SET default_table_access_method = heap;

CREATE TABLE public._sqlx_migrations (
    version bigint NOT NULL,
    description text NOT NULL,
    installed_on timestamp with time zone DEFAULT now() NOT NULL,
    success boolean NOT NULL,
    checksum bytea NOT NULL,
    execution_time bigint NOT NULL
);

ALTER TABLE public._sqlx_migrations OWNER TO yarn;

CREATE TABLE yarn.regular_tasks (
    regular_task_id uuid DEFAULT gen_random_uuid() NOT NULL,
    "interval" interval NOT NULL,
    last_submitted_at timestamp with time zone DEFAULT '2000-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    payload jsonb NOT NULL,
    max_runs_override integer,
    created_at timestamp with time zone DEFAULT transaction_timestamp() NOT NULL,
    updated_at timestamp with time zone DEFAULT transaction_timestamp() NOT NULL
);

ALTER TABLE yarn.regular_tasks OWNER TO yarn;

COMMENT ON TABLE yarn.regular_tasks IS 'Regularly scheduled tasks that keep being submitted over and over at certain intervals.
This is used mostly for cleaning up old resources, like cleaning posts that have been deleted for a long time.
Ensure you know what you are doing before you add, delete or edit rows for this table, as this will affect the behavior of the worker.';

COMMENT ON COLUMN yarn.regular_tasks."interval" IS 'The interval at which this task will be regularly submitted.
Note that the worker only checks for regular tasks to submit once every minute,
so setting this interval lower than a minute is useless.';

COMMENT ON COLUMN yarn.regular_tasks.last_submitted_at IS 'Timestamp at which the last run of this task was submitted to the task queue.
The default is a past date that ensures that the task will be considered ready to be submitted before its first submission.';

COMMENT ON COLUMN yarn.regular_tasks.payload IS 'The payload of the task to be regularly submitted.
This will be copied as is into the tasks.payload column, see that column for more documentation.';

COMMENT ON COLUMN yarn.regular_tasks.max_runs_override IS 'An override for the maximum number of times a task may run.
This will be copied as is to the tasks.max_runs_override, see that column for more documentation.';

CREATE TABLE yarn.tasks (
    task_id uuid DEFAULT gen_random_uuid() NOT NULL,
    payload jsonb NOT NULL,
    status text DEFAULT 'Pending'::text NOT NULL,
    runs integer DEFAULT 0 NOT NULL,
    max_runs_override integer,
    msg text[] DEFAULT '{}'::text[] NOT NULL,
    run_at timestamp with time zone DEFAULT transaction_timestamp() NOT NULL,
    started_at timestamp with time zone[] DEFAULT '{}'::timestamp with time zone[] NOT NULL,
    ended_at timestamp with time zone[] DEFAULT '{}'::timestamp with time zone[] NOT NULL,
    created_at timestamp with time zone DEFAULT transaction_timestamp() NOT NULL,
    updated_at timestamp with time zone DEFAULT transaction_timestamp() NOT NULL
);

ALTER TABLE yarn.tasks OWNER TO yarn;

COMMENT ON TABLE yarn.tasks IS 'Tasks to be run by the worker.

Tasks _must_ be idempotent, as they may be retried upon failure.
Completed tasks can be safely removed from this table.';

COMMENT ON COLUMN yarn.tasks.payload IS 'The payload of the task. This is a JSON serialization of the task type.
This should hold all the relevant information that the task may need to run.';

COMMENT ON COLUMN yarn.tasks.status IS 'Status of the task. This has a few options:
* Pending: The task is scheduled to run at the time given in run_at, but has not yet been started by a worker.
           If runs > 0, this indicates that this task was attempted before and failed, but can now be retried.
* Running: The task has been started by a worker. It may or may not still be running, as the worker may have failed to report an updated status (e.g. due to a crash).
           Due to this possibility, if a task in this state is not updated for a long time, it may be retried.
* Failed: The task encountered some permanent error that makes retrying pointless. This task will not be attempted again and is left in the table as a record of the error messages.
* Completed: The task completed successfully. The task is left in the table for informational purposes, but can be safely deleted.';

COMMENT ON COLUMN yarn.tasks.runs IS 'Number of times this task has been run. Ideally, this should only go to 1, as that would mean the task ran once and succeeded.
However, if a task fails, it may have to retry. If a task retries too many times, it may not be retried again.';

COMMENT ON COLUMN yarn.tasks.max_runs_override IS 'An override for the maximum number of times a task may run. If null, the worker decides how many times it wants to retry.
If set, this value is used for the maximum number of runs instead. This can be used to force additional tries for a failing task that has already
failed more times than the worker allows, or it can be used to restrict a task to have fewer tries than the worker would otherwise try.
For example, regular tasks set max_runs_override = 1 so that they don''t retry at all (they just retry at the next interval, after all).';

COMMENT ON COLUMN yarn.tasks.msg IS 'A message associated with each run, e.g. an error message. A successful run may have no message.';

COMMENT ON COLUMN yarn.tasks.run_at IS 'The time at which the task should run. This may be a time in the future, for instance for scheduled posts.
It''s also used to retry failing tasks after a delay.
Tasks may run slightly later than this time (depending on worker configuration), but they will never run before this time.';

COMMENT ON COLUMN yarn.tasks.started_at IS 'The time at which each run started (always at or later than run_at).';

COMMENT ON COLUMN yarn.tasks.ended_at IS 'The time at which each run finished (whether successful or not).';

ALTER TABLE ONLY public._sqlx_migrations
    ADD CONSTRAINT _sqlx_migrations_pkey PRIMARY KEY (version);

ALTER TABLE ONLY yarn.regular_tasks
    ADD CONSTRAINT regular_tasks_pkey PRIMARY KEY (regular_task_id);

ALTER TABLE ONLY yarn.tasks
    ADD CONSTRAINT tasks_pkey PRIMARY KEY (task_id);

CREATE INDEX tasks_max_runs_override_idx ON yarn.tasks USING btree (max_runs_override);

COMMENT ON INDEX yarn.tasks_max_runs_override_idx IS 'Filtering tasks by max_runs_override is useful to not select tasks that have retried too many times.';

CREATE INDEX tasks_run_at_idx ON yarn.tasks USING btree (run_at);

COMMENT ON INDEX yarn.tasks_run_at_idx IS 'Sorting tasks by run_at is crucial to select tasks that are past their running time.';

CREATE INDEX tasks_runs_idx ON yarn.tasks USING btree (runs);

COMMENT ON INDEX yarn.tasks_runs_idx IS 'Filtering tasks by runs is useful to not select tasks that have retried too many times.';

CREATE INDEX tasks_status_idx ON yarn.tasks USING btree (status);

COMMENT ON INDEX yarn.tasks_status_idx IS 'Filtering tasks by status is useful for selecting only pending tasks.';

CREATE TRIGGER update_regular_tasks_timestamp BEFORE UPDATE ON yarn.regular_tasks FOR EACH ROW EXECUTE FUNCTION yarn.update_timestamp();

CREATE TRIGGER update_tasks_timestamp BEFORE UPDATE ON yarn.tasks FOR EACH ROW EXECUTE FUNCTION yarn.update_timestamp();

\unrestrict 5TwZEqvfJAXp4Vgsv1dy0VWECvJ7sjDYUriACmqyR3gGzsoIGhlpdOZZtrwcYJE

