alkstore/tasks/pg-engine-schema.md

id, name, status, depends_on, scope, risk, impact, level, tags

id	name	status	depends_on	scope	risk	impact	level	tags
pg-engine-schema	Postgres engine — schema bootstrap (engine-owned schema, table family)	completed		narrow	low	component	implementation	wave-4 postgres-engine

Description

Stand up the pg engine's data model: the engine-owned PostgreSQL schema and its table family — the dependency root every later wave-4 task rides. Per ADR-010 §8 (one engine-owned schema, default alkstore, queues as rows — no per-queue tables/schemas) and engine-postgres.md's mapping table.

A schema.rs module in alkstore-postgres:

• Schema name is engine configuration (default alkstore) — it rides PgOpts (the open task wires it; this task's bootstrap takes the schema name as an argument).
• Bootstrap is idempotent DDL, runnable at open on any connection: CREATE SCHEMA IF NOT EXISTS + CREATE TABLE IF NOT EXISTS + indexes. All engine-owned objects live in the one schema; consumer tables co-tenant the instance untouched (deployment.md's co-tenancy posture).
• Table family (column sets follow the contract's value shapes — the #[doc(hidden)] constructors' full field lists in alkstore/src/{job,stream,schedule}.rs are the field lists of record; ADR-019 §1/§3, ADR-015 §3, ADR-009 §3, ADR-021 §2):
  • job table (live rows — pending and processing): queue name, payload, enqueue/ready-time resolution columns (run_at, expires_at), priority, the stamped QueueOpts columns (visibility_timeout_s, max_attempts, backoff_base_s, dead_letter_retention_s — ADR-010 §3a), attempts, worker_id, claimed_at, claim_expires_at, last_error. Claim-ordering index per ADR-010's ordering row (priority DESC, run_at ASC, enqueue order).
  • dead table: the job row's diagnosis fields + died_at, last_error; retention index for the sweep's retention enforcement.
  • stream events table: id BIGSERIAL (the offset — engine- assigned, monotone, immutable, never renumbered), stream name, nullable key column (carried metadata, ADR-015 §1), payload, created_at (unix-seconds, informational). The (stream, id) index serving the offset ASC read paths.
  • offsets table: (stream, consumer) → offset, the monotone checkpoint upsert target.
  • schedule table (ADR-009): name (unique), @every spec, target queue, payload, the ScheduleOpts stamps, and the boundary-state columns the tick math advances (the substrate's __alkstore_scheduler_tasks shape is the reference — reference-honker-machinery.md).
  • locks table: name (unique), owner, expiry timestamp — the TTL lock machinery's storage (the substrate's lock-table semantics, re-derived; the locks task builds ops on it).
• Payload columns store the exact encoded bytes (bytea) — not JSONB: JSONB normalization (key reordering, duplicate folding) would break the contract suite's byte-identical-rows row (ADR-020 §4). The engine stores exactly what encode_payload returned.
• No notifications table — pg notify is native (pg_notify); the SQLite notify-table carriage has no pg counterpart. The 8000-byte check is client-side (the seam task), not storage-shaped.
• No migration machinery in v1 — tables are created fresh per schema; append-column migrations are the substrate's evolution discipline, not a greenfield v1 need (note the posture in the module docs; wave 7's release pass revisits if the deployment story demands in-place upgrades).

Tests use a raw tokio_postgres::Connection/Client against the harness server (no engine code yet): bootstrap runs twice cleanly (idempotence), every table exists with its pinned columns, the bigserial offset behaves (monotone, gap-on-delete), the claim-ordering index exists.

Acceptance Criteria

• schema.rs with an idempotent bootstrap(conn, schema_name) creating the full table family + indexes in the one schema
• Column sets match the contract's value shapes (the constructors' field lists; stamp columns per ADR-010 §3a; claimed_at per ADR-021 §2)
• Payload columns are bytea (exact-bytes posture, JSONB rejected — documented in the module docs with the reason)
• Schema name is a parameter (default alkstore decided at the PgOpts layer, next task)
• Indexes: idempotence + table-shape tests green against the harness server
• cargo test -p alkstore-postgres, clippy -D warnings, fmt clean

References

• docs/architecture/engine-postgres.md (Identity and posture; mapping table)
• docs/architecture/decisions/010-queue-semantics-depth.md §3a, §8
• docs/architecture/decisions/015-streams-depth.md §1, §3
• docs/architecture/decisions/009-scheduler-collapse.md §3
• docs/architecture/deployment.md (co-tenancy)
• docs/research/reference-honker-machinery.md (schema reference read)
• alkstore/src/job.rs, stream.rs, schedule.rs (the field lists of record)

Notes

Decisions of record made while implementing (the description didn't pin them):

• Table names: job, dead, stream_events, stream_offsets, schedule, locks — fixed constants exported as alkstore_postgres::tables for the later mechanism tasks' SQL. Table names do not ride configuration; only the schema name does.
• Index-name qualification: Postgres rejects schema-qualified index names at CREATE INDEX, but IF NOT EXISTS on a bare name checks existence across all schemas — a co-tenant's unrelated public.job_claim_idx would silently skip our index creation. So every engine index is named {schema}_{table}_{suffix} (job_claim_idx, job_pending_idx, job_deadline_idx, dead_retention_idx, events_read_idx, schedule_fire_idx, locks_expiry_idx, exported as indexes::) — schema-prefixed and quoted, tested against a co-tenant carrying the bare name. Sub-HOT-path indexes (job_pending, job_deadline, the substrate's trio) carried over from the reference shapes — the claim/heartbeat paths the queue task re-derives ride them.
• Reserved-word columns: the offsets checkpoint column is "offset" and the events key column "key" — both quoted here and flagged in the module docs so every later task's SQL keeps the quotes. The offsets column carries a non-negative CHECK (cursors start at 0); monotonicity itself stays in the streams task's GREATEST-guarded upsert.
• Identifier quoting: quote_identifier (doubles embedded ") is the engine's injection boundary for the consumer-supplied schema name, exported for later tasks; tested hostile (SQL-breakout schema name stays inert single-schema DDL).
• Schedule columns anchor to the substrate's __alkstore_scheduler_tasks concrete-stamp shape, enabled dropped (no pause/resume in the collapse surface): name PK, spec, queue, payload, priority, expires_s, next_fire_at (the tick's boundary column), max_attempts (default 3), visibility_timeout_s (default 300), backoff_base_s (default 5), dead_letter_retention_s. No last_fire_at (next_fire_at is the only state the tick math needs).
• Dead table mirrors the job table's live columns + the diagnosis fields (last_error, died_at NOT NULL), id a plain BIGINT PK (carried from the move, not a sequence); retention index (queue, died_at).
• Job table = the Job::from_row field list of record verbatim + the five stamp columns; state TEXT DEFAULT 'pending' (SQLite parity — the claim predicates read the text values); three indexes (claim-ordering partial WHERE state IN ('pending','processing') per ADR-010 §1's ordering row, pending (queue, run_at), processing deadline (queue, claim_expires_at)).
• thiserror added to the crate (v2, same as core/SQLite) for BootstrapError; the open task maps it onto Error::Database next task.
• Harness convention (ride env, never hardcoded, skip-clean server-less): ALKSTORE_PG_HOST/PORT/USER/PASSWORD/DB — host 127.0.0.1, port 15432, user/password postgres/poc, db blobs (the pglo-poc container's). The next tasks' tests should reuse these variable names. bootstrap itself takes any &tokio_postgres::Client (NoTls in tests; TLS is the open task's concern via deadpool config, not this module's).

Summary

Landed: alkstore-postgres/src/schema.rs — the engine-owned schema module (bootstrap(conn, schema_name) idempotent DDL over batch_execute; DEFAULT_SCHEMA = "alkstore"; quote_identifier; tables/indexes name constants; BootstrapError), wired into lib.rs re-exports, plus tests/schema_tests.rs (9 tests) driven against the harness server via env-carried DSN, skipping cleanly server-less. Table family per the acceptance criteria (job/dead/ stream_events/stream_offsets/schedule/locks, bytea payloads, bigserial offsets, claim-ordering partial index with the pinned ordering row). Verified: 9/9 green against the harness server (idempotence ×3 runs, per-table column sets, bytea types, bigserial monotone + gap-on-delete, claim index def fragments incl. priority DESC + partiality, schema name as parameter incl. reserved-word name select, co-tenancy re-bootstrap, co-tenant bare-index-name collision exclusion, hostile schema-name quoting); workspace cargo test green server-less (236 tests, pg tests skipped per convention); clippy -D warnings and fmt clean workspace-wide.