DatabaseCreation: TradingMK2 Create2.ddl

File TradingMK2 Create2.ddl, 9.2 KB (added by 231020, 4 days ago)
Line 
1-- =========================
2-- USERS
3-- =========================
4CREATE TABLE users (
5 id BIGSERIAL PRIMARY KEY,
6 email VARCHAR(255) NOT NULL UNIQUE,
7 password VARCHAR(255) NOT NULL,
8 role VARCHAR(20) NOT NULL,
9 username VARCHAR(255) NOT NULL UNIQUE,
10
11 CONSTRAINT chk_user_role
12 CHECK (role IN ('USER','ADMIN')),
13
14 CONSTRAINT chk_user_email
15 CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$')
16);
17
18-- =========================
19-- BANK ACCOUNT
20-- =========================
21CREATE TABLE bank_account (
22 id BIGSERIAL PRIMARY KEY,
23 account_number BIGINT NOT NULL,
24 bank VARCHAR(100) NOT NULL,
25 users_id BIGINT NOT NULL,
26
27 CONSTRAINT fk_bank_user
28 FOREIGN KEY (users_id)
29 REFERENCES users(id)
30 ON DELETE CASCADE
31);
32
33-- =========================
34-- PORTFOLIOS
35-- =========================
36CREATE TABLE portfolios (
37 id BIGSERIAL PRIMARY KEY,
38 balance NUMERIC(18,2) NOT NULL,
39 user_id BIGINT NOT NULL,
40
41 CONSTRAINT fk_portfolio_user
42 FOREIGN KEY (user_id)
43 REFERENCES users(id)
44 ON DELETE CASCADE
45);
46
47-- =========================
48-- STOCK
49-- =========================
50CREATE TABLE stock (
51 id BIGSERIAL PRIMARY KEY,
52 symbol VARCHAR(20) NOT NULL UNIQUE,
53 name VARCHAR(255),
54 current_price FLOAT8,
55 last_price FLOAT8,
56 percentage FLOAT8,
57 turnover FLOAT8,
58 last_updated TIMESTAMP,
59
60 CONSTRAINT chk_stock_symbol
61 CHECK (symbol ~ '^[A-Z0-9\.]{1,20}$')
62);
63
64-- =========================
65-- PORTFOLIO HOLDINGS
66-- =========================
67CREATE TABLE portfolio_holdings (
68 id BIGSERIAL PRIMARY KEY,
69 quantity INT NOT NULL,
70 avg_price NUMERIC(38,2) NOT NULL,
71 stock_id BIGINT NOT NULL,
72 portfolio_id BIGINT NOT NULL,
73
74 CONSTRAINT fk_holdings_portfolio
75 FOREIGN KEY (portfolio_id)
76 REFERENCES portfolios(id)
77 ON DELETE CASCADE,
78
79 CONSTRAINT fk_holdings_stock
80 FOREIGN KEY (stock_id)
81 REFERENCES stock(id)
82 ON DELETE RESTRICT
83);
84
85-- =========================
86-- STOCK HISTORY
87-- =========================
88CREATE TABLE stock_history (
89 id BIGSERIAL PRIMARY KEY,
90 price FLOAT8 NOT NULL,
91 stock_id BIGINT NOT NULL,
92 timestamp DATE NOT NULL,
93
94 CONSTRAINT fk_stock_history
95 FOREIGN KEY (stock_id)
96 REFERENCES stock(id)
97 ON DELETE RESTRICT
98);
99
100-- =========================
101-- TRADE REQUEST
102-- =========================
103CREATE TABLE trade_request (
104 id BIGSERIAL PRIMARY KEY,
105 portfolio_id BIGINT NOT NULL,
106 price_per_unit FLOAT8 NOT NULL,
107 quantity INT NOT NULL,
108 status VARCHAR(20),
109 stock_symbol VARCHAR(20),
110 timestamp TIMESTAMP,
111 type VARCHAR(10),
112
113 CONSTRAINT fk_trade_portfolio
114 FOREIGN KEY (portfolio_id)
115 REFERENCES portfolios(id)
116 ON DELETE CASCADE,
117
118 CONSTRAINT chk_trade_status
119 CHECK (status IN ('OPEN','CLOSED','CANCELLED')),
120
121 CONSTRAINT chk_trade_type
122 CHECK (type IN ('BUY','SELL'))
123);
124
125-- =========================
126-- BROKER ORDER
127-- =========================
128CREATE TABLE broker_order (
129 id BIGSERIAL PRIMARY KEY,
130 order_type VARCHAR(10) NOT NULL,
131 limit_price FLOAT8 NOT NULL,
132 quantity INT NOT NULL,
133 status VARCHAR(20) NOT NULL,
134 executed_price FLOAT8,
135 broker_fee FLOAT8 NOT NULL,
136 mse_reference_number VARCHAR(50) NOT NULL,
137 submitted_at TIMESTAMP NOT NULL,
138 executed_at TIMESTAMP,
139 trade_request_id BIGINT NOT NULL,
140
141 CONSTRAINT chk_order_type
142 CHECK (order_type IN ('BUY','SELL')),
143
144 CONSTRAINT chk_order_status
145 CHECK (status IN ('PENDING','EXECUTED','CANCELLED')),
146
147 CONSTRAINT chk_mse_ref
148 CHECK (mse_reference_number ~ '^[A-Z0-9\-]{5,50}$'),
149
150 CONSTRAINT fk_broker_trade
151 FOREIGN KEY (trade_request_id)
152 REFERENCES trade_request(id)
153 ON DELETE CASCADE
154);
155
156-- =========================
157-- ORDER MATCHING LOG
158-- =========================
159CREATE TABLE order_matching_log (
160 id BIGSERIAL PRIMARY KEY,
161 broker_order_id BIGINT NOT NULL,
162 checked_price FLOAT8 NOT NULL,
163 result VARCHAR(20) NOT NULL,
164 checked_at TIMESTAMP NOT NULL,
165
166 CONSTRAINT chk_match_result
167 CHECK (result IN ('MATCHED','NO_MATCH','PARTIAL')),
168
169 CONSTRAINT fk_match_order
170 FOREIGN KEY (broker_order_id)
171 REFERENCES broker_order(id)
172 ON DELETE CASCADE
173);
174
175-- =========================
176-- CFD POSITION
177-- =========================
178CREATE TABLE CFD_position (
179 id BIGSERIAL PRIMARY KEY,
180 status VARCHAR(20) NOT NULL,
181 quantity INT NOT NULL,
182 profit_loss FLOAT8 NOT NULL,
183 opened_at DATE NOT NULL,
184 close_price FLOAT8,
185 open_price FLOAT8 NOT NULL,
186 users_id BIGINT NOT NULL,
187 stock_id BIGINT NOT NULL,
188
189 CONSTRAINT chk_cfd_status
190 CHECK (status IN ('OPEN','CLOSED')),
191
192 CONSTRAINT fk_cfd_user
193 FOREIGN KEY (users_id)
194 REFERENCES users(id)
195 ON DELETE CASCADE,
196
197 CONSTRAINT fk_cfd_stock
198 FOREIGN KEY (stock_id)
199 REFERENCES stock(id)
200 ON DELETE RESTRICT
201);
202
203-- =========================
204-- DIVIDEND
205-- =========================
206CREATE TABLE dividend (
207 id BIGSERIAL PRIMARY KEY,
208 amount_per_share FLOAT8 NOT NULL,
209 paymentDate DATE NOT NULL,
210 stock_id BIGINT NOT NULL,
211
212 CONSTRAINT fk_dividend_stock
213 FOREIGN KEY (stock_id)
214 REFERENCES stock(id)
215 ON DELETE RESTRICT
216);
217
218-- =========================
219-- DIVIDEND PAYMENT
220-- =========================
221CREATE TABLE dividend_payment (
222 id BIGSERIAL PRIMARY KEY,
223 totalAmount FLOAT8 NOT NULL,
224 paid_at DATE NOT NULL,
225 dividend_id BIGINT NOT NULL,
226 users_id BIGINT NOT NULL,
227
228 CONSTRAINT fk_div_payment_dividend
229 FOREIGN KEY (dividend_id)
230 REFERENCES dividend(id)
231 ON DELETE CASCADE,
232
233 CONSTRAINT fk_div_payment_user
234 FOREIGN KEY (users_id)
235 REFERENCES users(id)
236 ON DELETE CASCADE
237);
238
239-- =========================
240-- PAYMENT TRANSACTION
241-- =========================
242CREATE TABLE payment_transaction (
243 id BIGSERIAL PRIMARY KEY,
244 amount BIGINT NOT NULL,
245 type VARCHAR(20) NOT NULL,
246 status VARCHAR(20) NOT NULL,
247 timestamp TIMESTAMP NOT NULL,
248 bank_account_id BIGINT NOT NULL,
249
250 CONSTRAINT chk_tx_type
251 CHECK (type IN ('DEPOSIT','WITHDRAWAL')),
252
253 CONSTRAINT chk_tx_status
254 CHECK (status IN ('PENDING','SUCCESS','FAILED')),
255
256 CONSTRAINT fk_tx_bank
257 FOREIGN KEY (bank_account_id)
258 REFERENCES bank_account(id)
259 ON DELETE CASCADE
260);
261
262-- =========================
263-- SETTLEMENT RECORD
264-- =========================
265CREATE TABLE settlement_record (
266 id BIGSERIAL PRIMARY KEY,
267 broker_order_id BIGINT NOT NULL,
268 user_id BIGINT NOT NULL,
269 stock_symbol VARCHAR(20) NOT NULL,
270 quantity INT NOT NULL,
271 settled_price FLOAT8 NOT NULL,
272 total_cost FLOAT8 NOT NULL,
273 settlement_status VARCHAR(20) NOT NULL,
274 settled_at TIMESTAMP NOT NULL,
275
276 CONSTRAINT chk_settlement_status
277 CHECK (settlement_status IN ('PENDING','SETTLED','FAILED')),
278
279 CONSTRAINT fk_settlement_order
280 FOREIGN KEY (broker_order_id)
281 REFERENCES broker_order(id)
282 ON DELETE RESTRICT,
283
284 CONSTRAINT fk_settlement_user
285 FOREIGN KEY (user_id)
286 REFERENCES users(id)
287 ON DELETE RESTRICT
288);
289
290-- =========================
291-- TRADE TRANSACTION (RENAMED)
292-- =========================
293CREATE TABLE trade_transaction (
294 id BIGSERIAL PRIMARY KEY,
295 price FLOAT8 NOT NULL,
296 quantity INT NOT NULL,
297 timestamp TIMESTAMP NOT NULL,
298 type VARCHAR(20),
299 origin VARCHAR(20) NOT NULL,
300 user_id BIGINT NOT NULL,
301 stock_id BIGINT NOT NULL,
302
303 CONSTRAINT chk_origin
304 CHECK (origin IN ('INTERNAL','EXTERNAL')),
305
306 CONSTRAINT fk_tx_user
307 FOREIGN KEY (user_id)
308 REFERENCES users(id)
309 ON DELETE RESTRICT,
310
311 CONSTRAINT fk_tx_stock
312 FOREIGN KEY (stock_id)
313 REFERENCES stock(id)
314 ON DELETE RESTRICT
315);
316
317-- =========================
318-- OAUTH PENDING LINKS
319-- =========================
320CREATE TABLE oauth_pending_links (
321 token VARCHAR(255) PRIMARY KEY,
322 email VARCHAR(255) NOT NULL,
323 provider VARCHAR(20) NOT NULL,
324 expires_at TIMESTAMP NOT NULL,
325 created_at TIMESTAMP NOT NULL,
326 user_id BIGINT NOT NULL,
327
328 CONSTRAINT chk_provider
329 CHECK (provider IN ('GOOGLE','INTERNAL')),
330
331 CONSTRAINT chk_oauth_email
332 CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
333
334 CONSTRAINT fk_oauth_user
335 FOREIGN KEY (user_id)
336 REFERENCES users(id)
337 ON DELETE CASCADE
338);
339
340-- =========================
341-- USER AUTH PROVIDERS
342-- =========================
343CREATE TABLE user_auth_providers (
344 user_id BIGINT NOT NULL,
345 auth_providers VARCHAR(20) NOT NULL,
346
347 CONSTRAINT chk_auth_provider
348 CHECK (auth_providers IN ('INTERNAL','GOOGLE')),
349
350 CONSTRAINT fk_auth_user
351 FOREIGN KEY (user_id)
352 REFERENCES users(id)
353 ON DELETE CASCADE
354);
355
356-- =========================
357-- WATCHLIST
358-- =========================
359CREATE TABLE watchlist (
360 id BIGSERIAL PRIMARY KEY,
361 price_above FLOAT8,
362 price_below FLOAT8,
363 stock_id BIGINT NOT NULL,
364 user_id BIGINT NOT NULL,
365
366 CONSTRAINT fk_watch_stock
367 FOREIGN KEY (stock_id)
368 REFERENCES stock(id)
369 ON DELETE RESTRICT,
370
371 CONSTRAINT fk_watch_user
372 FOREIGN KEY (user_id)
373 REFERENCES users(id)
374 ON DELETE CASCADE
375);