ipfs storage for images and other nontext items. for use with etica - runs on etica network and currencys
https://collect.etica-stats.org
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
62 lines
2.4 KiB
62 lines
2.4 KiB
-- /home/def/IPFSapp/backend/migrations/bootstrap.sql |
|
-- Idempotent DB bootstrap for IPFS Web Uploader |
|
-- Runs safely on every start; only adds what’s missing. |
|
|
|
BEGIN; |
|
|
|
-- ---------- upload_quotes (FK target for payments.quote_id) ---------- |
|
CREATE TABLE IF NOT EXISTS upload_quotes ( |
|
quote_id BIGINT PRIMARY KEY, |
|
address TEXT, |
|
size_bytes BIGINT, |
|
currency TEXT DEFAULT 'EGAZ', |
|
price_egaz NUMERIC, |
|
price_eti NUMERIC, |
|
pay_to TEXT, |
|
expires_at TIMESTAMPTZ DEFAULT (now() + interval '30 minutes'), |
|
created_at TIMESTAMPTZ DEFAULT now() |
|
); |
|
|
|
-- ---------- payments table shape used by the app ---------- |
|
-- Tracking columns |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS confs INTEGER; |
|
ALTER TABLE payments ALTER COLUMN confs SET DEFAULT 0; |
|
UPDATE payments SET confs = COALESCE(confs, 0); |
|
|
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS required_confs INTEGER; |
|
ALTER TABLE payments ALTER COLUMN required_confs SET DEFAULT 3; |
|
UPDATE payments SET required_confs = COALESCE(required_confs, 3); |
|
|
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS mined_block BIGINT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS latest_block BIGINT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS tx_hash TEXT; |
|
|
|
-- Upload metadata |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS cid TEXT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS path TEXT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS label TEXT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS filename TEXT; |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS size_bytes BIGINT; |
|
|
|
-- Expiration used by /api/files |
|
ALTER TABLE payments ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ; |
|
UPDATE payments |
|
SET expires_at = COALESCE(expires_at, COALESCE(created_at, now()) + interval '30 minutes'); |
|
ALTER TABLE payments ALTER COLUMN expires_at SET DEFAULT (now() + interval '30 minutes'); |
|
|
|
-- Status constraint: allow these values (and keep legacy rows valid) |
|
DO $$ |
|
BEGIN |
|
IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'payments_status_chk') THEN |
|
ALTER TABLE payments DROP CONSTRAINT payments_status_chk; |
|
END IF; |
|
END$$; |
|
|
|
ALTER TABLE payments |
|
ADD CONSTRAINT payments_status_chk |
|
CHECK (status = ANY (ARRAY['initial','pending','confirmed','paid','failed','expired','cancelled'])); |
|
|
|
-- Default any NULL statuses without a full-table rewrite |
|
UPDATE payments SET status = COALESCE(status, 'pending'); |
|
|
|
COMMIT;
|
|
|