github.com/Goboolean/common@v0.0.0-20231130153141-cb54596b217d/api/sql/schema.sql (about) 1 /* metadata table for product data */ 2 CREATE TABLE product_meta ( 3 id varchar(32) NOT NULL, /* an identifier form : {type}.{name}.{location} */ 4 name varchar(32) NOT NULL, /* human readable stock name */ 5 symbol varchar(8) NOT NULL, /* stock symbol */ 6 description text, /* product description : can be gain by external apis, or written manually by admin */ 7 type varchar(8) NOT NULL, /* examples are stock, encrypt */ 8 exchange varchar(32) NOT NULL, /* examples are kospi, nasdaq. */ 9 location varchar(32), /* examples are kor, usa. when product type is coin location is null*/ 10 PRIMARY KEY (id) 11 ); 12 13 CREATE TABLE platform ( 14 name varchar(32) NOT NULL, /* available platform is buycycle, polygon, kis */ 15 description text, 16 PRIMARY KEY (name) 17 ); 18 19 CREATE TABLE product_platform ( 20 product_id varchar(32) NOT NULL, 21 platform_name varchar(32) NOT NULL, 22 identifier varchar(32) NOT NULL, /* a string that is used to specific stock on such platform query */ 23 24 PRIMARY KEY (product_id, platform_name), 25 FOREIGN KEY (product_id) REFERENCES product_meta (id), 26 FOREIGN KEY (platform_name) REFERENCES platform (name) 27 ); 28 29 CREATE TABLE store_log ( 30 product_id varchar(32) NOT NULL, 31 stored_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 32 "status" varchar(10) NOT NULL, 33 34 PRIMARY KEY (stored_at), 35 FOREIGN KEY (product_id) REFERENCES product_meta (id) 36 );