mirror of
https://git.meshkee.com/Meshkee/backend.git
synced 2026-08-11 22:30:59 +04:30
Ali Reza
bb59d5e9ba
NestJS backend with Prisma, Docker Compose for Postgres/Redis, and deploy docs for the production VM.
115 lines
3.7 KiB
SQL
115 lines
3.7 KiB
SQL
-- Meshkee CMS — payment transactions
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- enums
|
|
-- ---------------------------------------------------------------------------
|
|
DO $$ BEGIN
|
|
CREATE TYPE transaction_type AS ENUM (
|
|
'pos',
|
|
'cash',
|
|
'transfer',
|
|
'e_payment_gate'
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE transaction_status AS ENUM (
|
|
'pending',
|
|
'completed',
|
|
'failed',
|
|
'refunded'
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- transactions
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE transactions (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
order_id BIGINT,
|
|
user_id BIGINT NOT NULL,
|
|
type transaction_type NOT NULL,
|
|
status transaction_status NOT NULL DEFAULT 'pending',
|
|
amount NUMERIC(12, 2) NOT NULL,
|
|
pos_type VARCHAR(100),
|
|
gateway_type VARCHAR(100),
|
|
transfer_account VARCHAR(255),
|
|
transfer_ref_number VARCHAR(100),
|
|
notes TEXT,
|
|
created_by BIGINT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT transactions_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
|
|
CONSTRAINT transactions_order_id_fkey
|
|
FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE SET NULL,
|
|
CONSTRAINT transactions_user_id_fkey
|
|
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
|
|
CONSTRAINT transactions_created_by_fkey
|
|
FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
|
|
CONSTRAINT transactions_amount_non_negative CHECK (amount >= 0),
|
|
CONSTRAINT transactions_type_fields_check CHECK (
|
|
(type = 'pos'
|
|
AND pos_type IS NOT NULL
|
|
AND gateway_type IS NULL
|
|
AND transfer_account IS NULL
|
|
AND transfer_ref_number IS NULL)
|
|
OR (type = 'cash'
|
|
AND pos_type IS NULL
|
|
AND gateway_type IS NULL
|
|
AND transfer_account IS NULL
|
|
AND transfer_ref_number IS NULL)
|
|
OR (type = 'transfer'
|
|
AND transfer_account IS NOT NULL
|
|
AND transfer_ref_number IS NOT NULL
|
|
AND pos_type IS NULL
|
|
AND gateway_type IS NULL)
|
|
OR (type = 'e_payment_gate'
|
|
AND gateway_type IS NOT NULL
|
|
AND pos_type IS NULL
|
|
AND transfer_account IS NULL
|
|
AND transfer_ref_number IS NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX idx_transactions_business_created
|
|
ON transactions (business_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_transactions_order_id
|
|
ON transactions (order_id);
|
|
|
|
CREATE INDEX idx_transactions_business_user
|
|
ON transactions (business_id, user_id, created_at DESC);
|
|
|
|
CREATE TRIGGER transactions_set_updated_at
|
|
BEFORE UPDATE ON transactions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- permissions
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO permissions (name, slug, group_name, description) VALUES
|
|
('View transactions', 'transactions.read', 'transactions', 'View payment transactions')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug = 'transactions.read'
|
|
WHERE r.slug IN ('business_owner', 'owner', 'admin')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug = 'transactions.read'
|
|
WHERE r.slug = 'editor'
|
|
ON CONFLICT DO NOTHING;
|