33 lines
1.4 KiB
PL/PgSQL
33 lines
1.4 KiB
PL/PgSQL
BEGIN;
|
|
-- Keeping the encoded object in PostgreSQL makes object creation, bindings and
|
|
-- idempotency atomic. A future blob backend can replace bytes without changing
|
|
-- stable application URLs or canvas references.
|
|
CREATE TABLE canvas_assets (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
uploaded_by uuid NOT NULL,
|
|
mime_type text NOT NULL CHECK(mime_type IN('image/png','image/jpeg','image/gif','image/webp')),
|
|
bytes bytea NOT NULL CHECK(octet_length(bytes) BETWEEN 1 AND 20971520),
|
|
width integer NOT NULL CHECK(width>0),
|
|
height integer NOT NULL CHECK(height>0),
|
|
digest text NOT NULL,
|
|
idempotency_key uuid NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(project,workspace_id,id),
|
|
UNIQUE(project,workspace_id,uploaded_by,idempotency_key),
|
|
FOREIGN KEY(project,workspace_id) REFERENCES workspaces ON DELETE CASCADE,
|
|
FOREIGN KEY(project,uploaded_by) REFERENCES project_accounts
|
|
);
|
|
CREATE TABLE canvas_asset_bindings (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
asset_id uuid NOT NULL,
|
|
board_id uuid NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(project,workspace_id,asset_id,board_id),
|
|
FOREIGN KEY(project,workspace_id,asset_id) REFERENCES canvas_assets ON DELETE RESTRICT,
|
|
FOREIGN KEY(project,workspace_id,board_id) REFERENCES materials ON DELETE CASCADE
|
|
);
|
|
COMMIT;
|