2.0.0_payment_gateways.sql

main 39 lines · 1.4 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- Stripe / Mercado Pago transaction ledger.
2-- Safe to run more than once.
3
4CREATE TABLE IF NOT EXISTS `znote_payment_transactions` (
5 `id` bigint NOT NULL AUTO_INCREMENT,
6 `provider` varchar(32) NOT NULL,
7 `reference` varchar(128) NOT NULL,
8 `provider_reference` varchar(128) DEFAULT NULL,
9 `account_id` int NOT NULL,
10 `price` decimal(11,2) NOT NULL,
11 `currency` varchar(8) NOT NULL,
12 `points` int NOT NULL,
13 `status` varchar(32) NOT NULL DEFAULT 'pending',
14 `credited` tinyint NOT NULL DEFAULT '0',
15 `test_mode` tinyint NOT NULL DEFAULT '0',
16 `created_at` int NOT NULL,
17 `updated_at` int NOT NULL,
18 `credited_at` int DEFAULT NULL,
19 `payload` longtext,
20 PRIMARY KEY (`id`),
21 UNIQUE KEY `provider_reference_internal` (`provider`, `reference`),
22 KEY `provider_reference_external` (`provider`, `provider_reference`),
23 KEY `account_status` (`account_id`, `status`, `created_at`)
24) ENGINE=InnoDB;
25
26CREATE TABLE IF NOT EXISTS `znote_payment_events` (
27 `id` bigint NOT NULL AUTO_INCREMENT,
28 `provider` varchar(32) NOT NULL,
29 `event_id` varchar(128) NOT NULL,
30 `provider_reference` varchar(128) DEFAULT NULL,
31 `payment_reference` varchar(128) DEFAULT NULL,
32 `status` varchar(32) NOT NULL DEFAULT 'received',
33 `payload` longtext,
34 `received_at` int NOT NULL,
35 PRIMARY KEY (`id`),
36 UNIQUE KEY `provider_event` (`provider`, `event_id`),
37 KEY `payment_reference` (`provider`, `payment_reference`)
38) ENGINE=InnoDB;
39
Top