File size: 1,805 Bytes
ac179f4 44fe6ac |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
-- objects table (simplified)
CREATE TABLE IF NOT EXISTS objects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
namespace TEXT NOT NULL,
type TEXT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
location JSONB,
severity INT,
confirmed BOOLEAN DEFAULT FALSE,
images JSONB,
provenance JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
-- ledger / payouts
CREATE TABLE IF NOT EXISTS payouts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fault_id UUID REFERENCES objects(id) ON DELETE CASCADE,
amount_minor_units BIGINT NOT NULL,
currency VARCHAR(8) NOT NULL DEFAULT 'ZAR',
payee_id TEXT NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'created', -- created|queued|processing|settled|failed
tx_ref TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
settled_at TIMESTAMPTZ
);
CREATE INDEX idx_objects_namespace ON objects(namespace);
CREATE INDEX idx_objects_confirmed ON objects(confirmed);
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS objects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
namespace TEXT NOT NULL,
type TEXT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
location JSONB,
severity INT,
confirmed BOOLEAN DEFAULT FALSE,
images JSONB,
provenance JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS payouts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fault_id UUID REFERENCES objects(id) ON DELETE CASCADE,
amount_minor_units BIGINT NOT NULL,
currency VARCHAR(8) NOT NULL DEFAULT 'ZAR',
payee_id TEXT NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'created',
tx_ref TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
settled_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_objects_namespace ON objects(namespace);
CREATE INDEX IF NOT EXISTS idx_objects_confirmed ON objects(confirmed);
|