William Gill
12bf35caf8
Greenfield Go multi-tenant IPFS Pinning Service wire-compatible with the
IPFS Pinning Services API spec. Paired 1:1 with Kubo over localhost RPC,
clustered via embedded NATS JetStream, Postgres source-of-truth with
RLS-enforced tenancy, Fiber + huma v2 for the HTTP surface, Authentik
OIDC for session login with kid-rotated HS256 JWT API tokens.
Feature-complete against the 22-milestone build plan, including the
ship-it v1.0 gap items:
* admin CLIs: drain/uncordon, maintenance, mint-token, rotate-key,
prune-denylist, rebalance --dry-run, cache-stats, cluster-presences
* TTL leader election via NATS KV, fence tokens, JetStream dedup
* rebalancer (plan/apply split), reconciler, requeue sweeper
* ristretto caches with NATS-backed cross-node invalidation
(placements live-nodes + token denylist)
* maintenance watchdog for stuck cluster-pause flag
* Prometheus /metrics with CIDR ACL, HTTP/pin/scheduler/cache gauges
* rate limiting: session (10/min) + anonymous global (120/min)
* integration tests: rebalance, refcount multi-org, RLS belt
* goreleaser (tar + deb/rpm/apk + Alpine Docker) targeting Gitea
Stack: Cobra/Viper, Fiber v2 + huma v2, embedded NATS JetStream,
pgx/sqlc/golang-migrate, ristretto, TypeID, prometheus/client_golang,
testcontainers-go.
218 lines
10 KiB
PL/PgSQL
218 lines
10 KiB
PL/PgSQL
-- anchorage initial schema.
|
|
--
|
|
-- Every tenant-scoped table carries org_id and enables row-level security
|
|
-- so a Go-layer bug cannot bleed rows across orgs. The application sets
|
|
-- current_setting('anchorage.org_id') per-transaction via the auth middleware.
|
|
|
|
BEGIN;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS citext;
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- orgs
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE orgs (
|
|
id text PRIMARY KEY CHECK (id LIKE 'org_%'),
|
|
slug text NOT NULL UNIQUE,
|
|
name text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- users
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE users (
|
|
id text PRIMARY KEY CHECK (id LIKE 'usr_%'),
|
|
authentik_sub text UNIQUE,
|
|
email citext NOT NULL UNIQUE,
|
|
display_name text NOT NULL DEFAULT '',
|
|
is_sysadmin boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- memberships
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE memberships (
|
|
org_id text NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role text NOT NULL CHECK (role IN ('orgadmin', 'member')),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (org_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX memberships_user_idx ON memberships (user_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- api_tokens — JWT metadata only; the signed token is never stored
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE api_tokens (
|
|
jti text PRIMARY KEY CHECK (jti LIKE 'tok_%'),
|
|
org_id text NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
label text NOT NULL,
|
|
scopes text[] NOT NULL DEFAULT '{}',
|
|
expires_at timestamptz NOT NULL,
|
|
revoked_at timestamptz,
|
|
last_used_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX api_tokens_org_idx ON api_tokens (org_id, created_at DESC);
|
|
CREATE INDEX api_tokens_expiry_idx ON api_tokens (expires_at) WHERE revoked_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- token_denylist — explicit revocation cache, TTL'd by expires_at
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE token_denylist (
|
|
jti text PRIMARY KEY,
|
|
expires_at timestamptz NOT NULL,
|
|
reason text NOT NULL DEFAULT 'revoked',
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX token_denylist_expiry_idx ON token_denylist (expires_at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- nodes — cluster registry populated on boot and maintained by heartbeats
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE nodes (
|
|
id text PRIMARY KEY CHECK (id LIKE 'nod_%'),
|
|
display_name text NOT NULL DEFAULT '',
|
|
multiaddrs text[] NOT NULL DEFAULT '{}',
|
|
rpc_url text NOT NULL,
|
|
status text NOT NULL CHECK (status IN ('up', 'down', 'drained')),
|
|
last_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
joined_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX nodes_status_idx ON nodes (status, last_seen_at DESC);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- pins — logical pin records (spec: PinStatus)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE pins (
|
|
request_id text PRIMARY KEY CHECK (request_id LIKE 'pin_%'),
|
|
org_id text NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
cid text NOT NULL,
|
|
name text,
|
|
meta jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
origins text[] NOT NULL DEFAULT '{}',
|
|
status text NOT NULL CHECK (status IN ('queued', 'pinning', 'pinned', 'failed')),
|
|
failure_reason text,
|
|
created timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX pins_org_created_idx ON pins (org_id, created DESC);
|
|
CREATE INDEX pins_org_status_created_idx ON pins (org_id, status, created DESC);
|
|
CREATE INDEX pins_org_cid_idx ON pins (org_id, cid);
|
|
CREATE INDEX pins_org_lower_name_idx ON pins (org_id, lower(name) text_pattern_ops);
|
|
CREATE INDEX pins_name_trgm_idx ON pins USING gin (lower(name) gin_trgm_ops);
|
|
CREATE INDEX pins_meta_gin_idx ON pins USING gin (meta jsonb_path_ops);
|
|
|
|
-- Idempotent create: `POST /v1/pins` with the same (org_id, cid) collapses
|
|
-- to the existing live row. Failed pins may be retried with the same CID.
|
|
CREATE UNIQUE INDEX pins_org_cid_live_uniq
|
|
ON pins (org_id, cid) WHERE status <> 'failed';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- pin_placements — per-node scheduling rows; fence guards against zombies
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE pin_placements (
|
|
request_id text NOT NULL REFERENCES pins(request_id) ON DELETE CASCADE,
|
|
node_id text NOT NULL REFERENCES nodes(id) ON DELETE RESTRICT,
|
|
status text NOT NULL CHECK (status IN ('queued', 'pinning', 'pinned', 'failed')),
|
|
failure_reason text,
|
|
attempts int NOT NULL DEFAULT 0,
|
|
fence bigint NOT NULL DEFAULT 1,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (request_id, node_id)
|
|
);
|
|
|
|
CREATE INDEX pin_placements_node_status_idx ON pin_placements (node_id, status);
|
|
CREATE INDEX pin_placements_request_id_idx ON pin_placements (request_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- pin_refcount — per-(node, cid) reference counting; row deletion = unpin
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE pin_refcount (
|
|
node_id text NOT NULL REFERENCES nodes(id) ON DELETE RESTRICT,
|
|
cid text NOT NULL,
|
|
count int NOT NULL CHECK (count >= 0),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (node_id, cid)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- audit_log — append-only; bigserial is fine here (not user-facing)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE audit_log (
|
|
id bigserial PRIMARY KEY,
|
|
org_id text REFERENCES orgs(id) ON DELETE CASCADE,
|
|
actor_user_id text REFERENCES users(id) ON DELETE SET NULL,
|
|
actor_token_jti text,
|
|
action text NOT NULL,
|
|
target text NOT NULL,
|
|
result text NOT NULL CHECK (result IN ('ok', 'error')),
|
|
detail jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
created timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX audit_log_org_created_idx ON audit_log (org_id, created DESC);
|
|
CREATE INDEX audit_log_actor_created_idx ON audit_log (actor_user_id, created DESC);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- updated_at trigger
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION anchorage_touch_updated_at() RETURNS trigger
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
NEW.updated_at := now();
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER orgs_touch BEFORE UPDATE ON orgs FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER users_touch BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER memberships_touch BEFORE UPDATE ON memberships FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER api_tokens_touch BEFORE UPDATE ON api_tokens FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER nodes_touch BEFORE UPDATE ON nodes FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER pins_touch BEFORE UPDATE ON pins FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER pin_placements_touch BEFORE UPDATE ON pin_placements FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
CREATE TRIGGER pin_refcount_touch BEFORE UPDATE ON pin_refcount FOR EACH ROW EXECUTE FUNCTION anchorage_touch_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Row-level security — belt-and-suspenders tenant isolation
|
|
-- ---------------------------------------------------------------------------
|
|
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE api_tokens ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE pins ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- current_setting with the second arg = true returns NULL for unset keys
|
|
-- instead of raising. An unset GUC means "no tenant context" and returns
|
|
-- zero rows, which is the safe default.
|
|
CREATE POLICY memberships_tenant_isolation ON memberships
|
|
FOR ALL
|
|
USING (org_id = current_setting('anchorage.org_id', true))
|
|
WITH CHECK (org_id = current_setting('anchorage.org_id', true));
|
|
|
|
CREATE POLICY api_tokens_tenant_isolation ON api_tokens
|
|
FOR ALL
|
|
USING (org_id = current_setting('anchorage.org_id', true))
|
|
WITH CHECK (org_id = current_setting('anchorage.org_id', true));
|
|
|
|
CREATE POLICY pins_tenant_isolation ON pins
|
|
FOR ALL
|
|
USING (org_id = current_setting('anchorage.org_id', true))
|
|
WITH CHECK (org_id = current_setting('anchorage.org_id', true));
|
|
|
|
COMMIT;
|