1ZVXLiEg

1
2CREATE TABLE IF NOT EXISTS L_ACCOUNTS (
3  ACCOUNT_NAME VARCHAR(255) NOT NULL
4    CONSTRAINT L_PK_ACCOUNTS PRIMARY KEY,
5  FINGERPRINT VARCHAR(255)
6    CONSTRAINT L_AK_ACCOUNTS_FINGERPRINT UNIQUE,
7  BALANCE NUMERIC(14, 4) DEFAULT 0 NOT NULL
8    CONSTRAINT L_CHECK_ACCOUNTS_BALANCE
9      CHECK (BALANCE >= MINIMUM_ALLOWED_BALANCE),
10  MINIMUM_ALLOWED_BALANCE NUMERIC(14, 4) DEFAULT 0,
11  NOTIFICATION_URL VARCHAR(255),
12  IS_DISABLED BOOLEAN DEFAULT FALSE NOT NULL
13);
14
15-- L_TRANSFERS is insert-only (no updates)
16CREATE TABLE IF NOT EXISTS L_TRANSFERS (
17  TRANSFER_INDEX BIGSERIAL NOT NULL
18    CONSTRAINT L_PK_TRANSFERS PRIMARY KEY,
19  TRANSFER_ID UUID NOT NULL
20    CONSTRAINT L_NK_TRANSFERS UNIQUE,
21  DEBIT_ACCOUNT VARCHAR(255) NOT NULL
22    CONSTRAINT L_FK_TRANSFERS_DEBIT_ACCOUNT
23      REFERENCES L_ACCOUNTS (ACCOUNT_NAME),
24  CREDIT_ACCOUNT VARCHAR(255) NOT NULL
25    CONSTRAINT L_FK_TRANSFERS_CREDIT_ACCOUNT
26      REFERENCES L_ACCOUNTS (ACCOUNT_NAME),
27  AMOUNT NUMERIC(14, 4) NOT NULL,
28  EXECUTION_CONDITION VARCHAR(4000),
29  CANCELLATION_CONDITION VARCHAR(4000),
30  MEMO VARCHAR(4000),
31  CREATED_DTTM TIMESTAMP DEFAULT NOW() NOT NULL
32);
33
34-- L_FULFILLMENTS is insert-only (no updates)
35CREATE TABLE IF NOT EXISTS L_FULFILLMENTS (
36  TRANSFER_INDEX BIGINT NOT NULL
37    CONSTRAINT L_PK_FULFILLMENTS PRIMARY KEY
38    CONSTRAINT L_FK_FULFILLMENTS_TRANSFER
39      REFERENCES L_TRANSFERS (TRANSFER_INDEX),
40  CONDITION_FULFILLMENT VARCHAR(4000),
41  IS_EXECUTION BOOLEAN NOT NULL,
42  FINALIZED_DTTM TIMESTAMP DEFAULT NOW() NOT NULL
43);
44
45-- L_ENTRIES is insert-only (no-updates)
46CREATE TABLE IF NOT EXISTS L_ENTRIES (
47  ENTRY_INDEX BIGSERIAL NOT NULL
48    CONSTRAINT L_PK_ENTRIES PRIMARY KEY,
49  TRANSFER_INDEX BIGINT NOT NULL
50    CONSTRAINT L_FK_ENTRIES_TRANSFER_INDEX
51      REFERENCES L_TRANSFERS (TRANSFER_INDEX),
52  TRANSFER_ID UUID NOT NULL    -- denormalized for faster reports
53    CONSTRAINT L_FK_ENTRIES_TRANSFER_ID
54      REFERENCES L_TRANSFERS (TRANSFER_ID),
55  ACCOUNT VARCHAR(255) NOT NULL
56    CONSTRAINT L_FK_ENTRIES_ACCOUNT
57      REFERENCES L_ACCOUNTS (ACCOUNT_NAME),
58  BALANCE_CHANGE NUMERIC(14, 4) NOT NULL,
59  CREATED_DTTM TIMESTAMP DEFAULT NOW() NOT NULL
60);