28 lines
1.1 KiB
PL/PgSQL
28 lines
1.1 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TABLE device_claim (
|
||
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
public_id uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id uuid NOT NULL REFERENCES tenant(id),
|
||
|
|
device_id uuid NOT NULL,
|
||
|
|
token_digest char(64) NOT NULL UNIQUE,
|
||
|
|
expires_at timestamptz NOT NULL,
|
||
|
|
revoked_at timestamptz,
|
||
|
|
used_at timestamptz,
|
||
|
|
used_binding_id uuid REFERENCES device_binding(id),
|
||
|
|
failed_attempts integer NOT NULL DEFAULT 0 CHECK (failed_attempts >= 0),
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT device_claim_device_tenant_fk
|
||
|
|
FOREIGN KEY (device_id, tenant_id) REFERENCES device(id, tenant_id),
|
||
|
|
CONSTRAINT device_claim_use_consistency CHECK (
|
||
|
|
(used_at IS NULL AND used_binding_id IS NULL) OR
|
||
|
|
(used_at IS NOT NULL AND used_binding_id IS NOT NULL)
|
||
|
|
)
|
||
|
|
);
|
||
|
|
CREATE INDEX device_claim_device_status_idx
|
||
|
|
ON device_claim (tenant_id, device_id, expires_at DESC);
|
||
|
|
CREATE UNIQUE INDEX device_claim_one_unused_per_device
|
||
|
|
ON device_claim (device_id) WHERE used_at IS NULL AND revoked_at IS NULL;
|
||
|
|
|
||
|
|
COMMIT;
|