27 lines
1.1 KiB
SQL
27 lines
1.1 KiB
SQL
|
-- 사용자 인증 + 포트폴리오 (5~20명 지인 대상, JWT 기반)
|
||
|
|
-- idempotent: 여러 번 실행해도 안전
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
||
|
|
password_hash VARCHAR(255) NOT NULL,
|
||
|
|
role VARCHAR(20) NOT NULL DEFAULT 'user',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
last_login_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_portfolio (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
stock_code VARCHAR(10) NOT NULL,
|
||
|
|
stock_name VARCHAR(100) NOT NULL DEFAULT '',
|
||
|
|
buy_price INTEGER NOT NULL,
|
||
|
|
qty INTEGER NOT NULL,
|
||
|
|
memo TEXT DEFAULT '',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_portfolio_user ON user_portfolio(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_portfolio_user_code ON user_portfolio(user_id, stock_code);
|