[Feature]: Add primary keys for database tables

[pveierland]

Is your feature request related to a problem?

The tables apalis.jobs and apalis.workers in apalis-postgres version 1.0.0-beta.3 do not appear to have primary keys specified. They have indexes, but without primary keys certain tooling can fail in different ways, e.g. diesel was having issues as a result of primary keys not being specified.

db=# \d apalis.jobs
                               Table "apalis.jobs"
    Column    |           Type           | Collation | Nullable |     Default
--------------+--------------------------+-----------+----------+-----------------
 job          | bytea                    |           | not null |
 id           | text                     |           | not null |
 job_type     | text                     |           | not null |
 status       | text                     |           | not null | 'Pending'::text
 attempts     | integer                  |           | not null | 0
 max_attempts | integer                  |           | not null | 25
 run_at       | timestamp with time zone |           | not null | now()
 last_result  | jsonb                    |           |          |
 lock_at      | timestamp with time zone |           |          |
 lock_by      | text                     |           |          |
 done_at      | timestamp with time zone |           |          |
 priority     | integer                  |           |          | 0
 metadata     | jsonb                    |           |          |
Indexes:
    "jtidx" btree (job_type)
    "lidx" btree (lock_by)
    "sidx" btree (status)
    "tidx" btree (id)
    "unique_job_id" UNIQUE, btree (id)
Foreign-key constraints:
    "fk_worker_lock_by" FOREIGN KEY (lock_by) REFERENCES apalis.workers(id)
Triggers:
    notify_workers AFTER INSERT ON apalis.jobs FOR EACH ROW EXECUTE FUNCTION apalis.notify_new_jobs()

db=# \d apalis.workers
                          Table "apalis.workers"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+----------
 id           | text                     |           | not null |
 worker_type  | text                     |           | not null |
 storage_name | text                     |           | not null |
 layers       | text                     |           | not null | ''::text
 last_seen    | timestamp with time zone |           | not null | now()
 started_at   | timestamp with time zone |           |          |
Indexes:
    "idx" btree (id)
    "lsidx" btree (last_seen)
    "unique_worker_id" UNIQUE, btree (id)
    "wtidx" btree (worker_type)
Referenced by:
    TABLE "apalis.jobs" CONSTRAINT "fk_worker_lock_by" FOREIGN KEY (lock_by) REFERENCES apalis.workers(id)

Describe the solution you'd like

Make the id column of apalis.jobs and apalis.workers explicit primary keys.

Describe alternatives you've considered

No response

Use case

Adding explicit primary keys should facilitate better interoperability for apalis-postgres.

Additional context

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[geofmureithi]

This should be considered a bug. Not sure how I missed this. Will pick up the issues on pg later today.

[geofmureithi]

@pveierland Could you document the other issue you mentioned?

[pveierland]

I believe that was a misunderstanding on my side and not an actual issue. I've mostly integrated the beta now, and will file further issues as I uncover them 👍

[geofmureithi]

@pveierland noted.
If you can do a quick migration PR on this, that would be great.