anchorage/internal/pkg/store/postgres/queries/pins.sql

-- name: CreatePin :one
INSERT INTO pins (request_id, org_id, cid, name, meta, origins, status)
VALUES ($1, $2, $3, $4, $5, $6, 'queued')
RETURNING *;

-- name: GetPin :one
SELECT * FROM pins WHERE request_id = $1 AND org_id = $2;

-- name: GetExistingLivePinByCID :one
SELECT * FROM pins
WHERE org_id = $1 AND cid = $2 AND status <> 'failed'
LIMIT 1;

-- name: UpdatePinStatus :exec
UPDATE pins SET status = $2, failure_reason = $3 WHERE request_id = $1;

-- name: DeletePin :exec
DELETE FROM pins WHERE request_id = $1 AND org_id = $2;

-- name: ListPins :many
SELECT * FROM pins
WHERE org_id = $1
ORDER BY created DESC
LIMIT $2 OFFSET $3;

-- FilterPins honors the full IPFS Pinning API spec filter surface.
--
-- All filters are optional; empty arrays / NULL values short-circuit to
-- "no restriction" via the IS NULL / array-length guards. Name matching
-- supports four modes through a single `match` discriminator:
--
--   'exact'    — LIKE (case-sensitive, no wildcards)
--   'iexact'   — ILIKE anchored both ends
--   'partial'  — LIKE %name%
--   'ipartial' — ILIKE %name% (uses pg_trgm GIN)
--
-- `meta` is treated as a jsonb "contains" check — the pin's meta must
-- be a superset of the requested map.
--
-- name: FilterPins :many
SELECT *
FROM pins
WHERE org_id = $1
  AND (COALESCE(array_length(sqlc.arg(cids)::text[], 1), 0) = 0
       OR cid = ANY(sqlc.arg(cids)::text[]))
  AND (COALESCE(array_length(sqlc.arg(statuses)::text[], 1), 0) = 0
       OR status = ANY(sqlc.arg(statuses)::text[]))
  AND (sqlc.narg(name)::text IS NULL
       OR (sqlc.arg(match_mode)::text = 'exact'    AND name =           sqlc.narg(name)::text)
       OR (sqlc.arg(match_mode)::text = 'iexact'   AND lower(name) =    lower(sqlc.narg(name)::text))
       OR (sqlc.arg(match_mode)::text = 'partial'  AND name   LIKE  '%' || sqlc.narg(name)::text || '%')
       OR (sqlc.arg(match_mode)::text = 'ipartial' AND name  ILIKE  '%' || sqlc.narg(name)::text || '%'))
  AND (sqlc.narg(before)::timestamptz IS NULL OR created <  sqlc.narg(before)::timestamptz)
  AND (sqlc.narg(after)::timestamptz  IS NULL OR created >= sqlc.narg(after)::timestamptz)
  AND (sqlc.arg(meta_filter)::jsonb = '{}'::jsonb
       OR meta @> sqlc.arg(meta_filter)::jsonb)
ORDER BY created DESC
LIMIT sqlc.arg(pin_limit)
OFFSET sqlc.arg(pin_offset);

-- ReplacePin swaps a pin's CID atomically. Used by POST /v1/pins/{rid}
-- (pin replace per spec). Placements + refcount are left to the caller
-- to rearrange in the same transaction.
--
-- name: ReplacePin :one
UPDATE pins
SET cid = $3,
    name = $4,
    meta = $5,
    origins = $6,
    status = 'queued',
    failure_reason = NULL
WHERE request_id = $1 AND org_id = $2
RETURNING *;

-- name: InsertPlacement :one
INSERT INTO pin_placements (request_id, node_id, status, fence)
VALUES ($1, $2, 'queued', $3)
RETURNING *;

-- name: GetPlacement :one
SELECT * FROM pin_placements WHERE request_id = $1 AND node_id = $2;

-- name: ListPlacementsForPin :many
SELECT pp.*, n.multiaddrs
FROM pin_placements pp JOIN nodes n ON n.id = pp.node_id
WHERE pp.request_id = $1;

-- name: ListPlacementsForNode :many
SELECT * FROM pin_placements
WHERE node_id = $1 AND status = $2
ORDER BY created_at;

-- name: UpdatePlacementStatusFenced :execrows
UPDATE pin_placements
SET status = $3, failure_reason = $4, attempts = attempts + 1
WHERE request_id = $1 AND node_id = $2 AND fence = $5;

-- name: ReplacePlacementFence :one
UPDATE pin_placements
SET node_id = $3, fence = fence + 1, status = 'queued', failure_reason = NULL
WHERE request_id = $1 AND node_id = $2
RETURNING *;

-- name: StuckPlacements :many
SELECT request_id, node_id, fence
FROM pin_placements
WHERE status = 'queued' AND created_at < (now() - make_interval(secs => $1));

-- name: IncRefcount :exec
INSERT INTO pin_refcount (node_id, cid, count)
VALUES ($1, $2, 1)
ON CONFLICT (node_id, cid) DO UPDATE SET count = pin_refcount.count + 1;

-- name: DecRefcount :one
UPDATE pin_refcount
SET count = count - 1
WHERE node_id = $1 AND cid = $2
RETURNING count;

-- name: DeleteRefcount :exec
DELETE FROM pin_refcount WHERE node_id = $1 AND cid = $2 AND count <= 0;