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  );