246 lines
10 KiB
SQL
246 lines
10 KiB
SQL
-- Baseline corresponding to legacy Alembic head 027.
|
||
|
||
-- Existing PostgreSQL databases must already have completed legacy migrations 001–027.
|
||
|
||
-- This file creates a fresh database; it does not rewrite existing financial data.
|
||
|
||
CREATE TABLE IF NOT EXISTS "posts" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"external_id" VARCHAR(64) NOT NULL UNIQUE,
|
||
"text" TEXT NOT NULL,
|
||
"source" VARCHAR(32) NOT NULL DEFAULT 'truth',
|
||
"published_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
"sentiment" VARCHAR(16) NOT NULL DEFAULT 'neutral',
|
||
"ai_confidence" INTEGER NOT NULL DEFAULT 0,
|
||
"relevant" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"price_impact_asset" VARCHAR(8),
|
||
"price_impact_m5" DOUBLE PRECISION,
|
||
"price_impact_m15" DOUBLE PRECISION,
|
||
"price_impact_m1h" DOUBLE PRECISION,
|
||
"price_at_post" DOUBLE PRECISION,
|
||
"signal" VARCHAR(8),
|
||
"ai_reasoning" TEXT,
|
||
"prefilter_reason" VARCHAR(32),
|
||
"analysis_version" VARCHAR(16),
|
||
"target_asset" VARCHAR(16),
|
||
"category" VARCHAR(24),
|
||
"expected_move_pct" DOUBLE PRECISION,
|
||
"invalidation_price" DOUBLE PRECISION,
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_posts_external_id ON "posts"("external_id");
|
||
|
||
CREATE TABLE IF NOT EXISTS "candles" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"asset" VARCHAR(8) NOT NULL,
|
||
"timeframe" VARCHAR(4) NOT NULL,
|
||
"time" BIGINT NOT NULL,
|
||
"open" DOUBLE PRECISION NOT NULL,
|
||
"high" DOUBLE PRECISION NOT NULL,
|
||
"low" DOUBLE PRECISION NOT NULL,
|
||
"close" DOUBLE PRECISION NOT NULL,
|
||
"volume" DOUBLE PRECISION NOT NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_candles_asset ON "candles"("asset");
|
||
CREATE INDEX IF NOT EXISTS ix_candles_time ON "candles"("time");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_candle_asset_tf_time ON "candles"("asset","timeframe","time");
|
||
|
||
CREATE TABLE IF NOT EXISTS "bot_trades" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"asset" VARCHAR(8) NOT NULL,
|
||
"side" VARCHAR(8) NOT NULL,
|
||
"entry_price" DOUBLE PRECISION NOT NULL,
|
||
"exit_price" DOUBLE PRECISION,
|
||
"pnl_usd" DOUBLE PRECISION,
|
||
"hold_seconds" INTEGER,
|
||
"trigger_post_id" INTEGER REFERENCES "posts"("id"),
|
||
"wallet_address" VARCHAR(64) NOT NULL,
|
||
"opened_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now()),
|
||
"closed_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"released_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"hl_order_id" VARCHAR(128),
|
||
"size_usd" DOUBLE PRECISION,
|
||
"leverage" INTEGER,
|
||
"eff_take_profit_pct" DOUBLE PRECISION,
|
||
"eff_stop_loss_pct" DOUBLE PRECISION,
|
||
"eff_trailing_stop_pct" DOUBLE PRECISION,
|
||
"eff_trailing_activate_pct" DOUBLE PRECISION,
|
||
"eff_max_hold_hours" INTEGER,
|
||
"eff_invalidation" VARCHAR(24),
|
||
"eff_invalidation_price" DOUBLE PRECISION,
|
||
"eff_min_hold_until_ts" DOUBLE PRECISION,
|
||
"realized_partial_pnl_usd" DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
||
"remaining_fraction" DOUBLE PRECISION NOT NULL DEFAULT 1.0,
|
||
"derisk_steps_done" INTEGER NOT NULL DEFAULT 0,
|
||
"base_size_usd" DOUBLE PRECISION,
|
||
"addon_steps_done" INTEGER NOT NULL DEFAULT 0,
|
||
"sys2_mode" VARCHAR(16),
|
||
"grow_mode" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"peak_gain_pct" DOUBLE PRECISION NOT NULL DEFAULT 0.0
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_bot_trades_trigger_post_id ON "bot_trades"("trigger_post_id");
|
||
CREATE INDEX IF NOT EXISTS ix_bot_trades_wallet_address ON "bot_trades"("wallet_address");
|
||
CREATE INDEX IF NOT EXISTS ix_bot_trades_wallet_closed ON "bot_trades"("wallet_address","closed_at");
|
||
|
||
CREATE TABLE IF NOT EXISTS "subscriptions" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"wallet_address" VARCHAR(64) NOT NULL UNIQUE,
|
||
"hl_api_key" VARCHAR(256),
|
||
"active" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"subscribed_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now()),
|
||
"leverage" INTEGER NOT NULL DEFAULT 3,
|
||
"position_size_usd" DOUBLE PRECISION NOT NULL DEFAULT 20.0,
|
||
"take_profit_pct" DOUBLE PRECISION,
|
||
"stop_loss_pct" DOUBLE PRECISION,
|
||
"min_confidence" INTEGER NOT NULL DEFAULT 80,
|
||
"daily_budget_usd" DOUBLE PRECISION,
|
||
"active_from" TIMESTAMP WITHOUT TIME ZONE,
|
||
"active_until" TIMESTAMP WITHOUT TIME ZONE,
|
||
"trailing_stop_pct" DOUBLE PRECISION,
|
||
"trailing_activate_at_pct" DOUBLE PRECISION,
|
||
"max_hold_hours" INTEGER NOT NULL DEFAULT 168,
|
||
"manual_window_until" TIMESTAMP WITHOUT TIME ZONE,
|
||
"paper_mode" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"circuit_breaker_tripped_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"circuit_breaker_reason" VARCHAR(32),
|
||
"sys2_budget_pct" DOUBLE PRECISION NOT NULL DEFAULT 0.7,
|
||
"sys2_cb_tripped_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"sys2_cb_reason" VARCHAR(32),
|
||
"sys2_leverage" INTEGER,
|
||
"sys2_mode" VARCHAR(16) NOT NULL DEFAULT 'standard',
|
||
"auto_trade" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"trump_enabled" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"macro_enabled" BOOLEAN NOT NULL DEFAULT FALSE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_subscriptions_wallet_address ON "subscriptions"("wallet_address");
|
||
|
||
CREATE TABLE IF NOT EXISTS "kol_posts" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"kol_handle" VARCHAR(64) NOT NULL,
|
||
"source" VARCHAR(16) NOT NULL,
|
||
"external_id" VARCHAR(512) NOT NULL,
|
||
"title" VARCHAR(512),
|
||
"url" VARCHAR(512) NOT NULL,
|
||
"published_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
"raw_text" TEXT NOT NULL,
|
||
"content_hash" VARCHAR(64) NOT NULL,
|
||
"summary" TEXT,
|
||
"tickers_json" TEXT,
|
||
"analyzed_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"analysis_model" VARCHAR(64),
|
||
"analysis_version" VARCHAR(16),
|
||
"tier" VARCHAR(16),
|
||
"post_type" VARCHAR(16),
|
||
"talks_vs_trades_flag" BOOLEAN DEFAULT FALSE,
|
||
"sentiment" VARCHAR(16),
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_kol_posts_kol_handle ON "kol_posts"("kol_handle");
|
||
CREATE INDEX IF NOT EXISTS ix_kol_posts_published_at ON "kol_posts"("published_at");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_kol_post_src_extid ON "kol_posts"("source","external_id");
|
||
|
||
CREATE TABLE IF NOT EXISTS "kol_wallets" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"handle" VARCHAR(64) NOT NULL,
|
||
"chain" VARCHAR(16) NOT NULL,
|
||
"address" VARCHAR(128) NOT NULL,
|
||
"label" VARCHAR(128),
|
||
"source_url" VARCHAR(256),
|
||
"active" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"added_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_kol_wallets_handle ON "kol_wallets"("handle");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_kol_wallet_chain_addr ON "kol_wallets"("chain","address");
|
||
|
||
CREATE TABLE IF NOT EXISTS "kol_holdings_snapshots" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"wallet_id" INTEGER NOT NULL REFERENCES "kol_wallets"("id"),
|
||
"snapshot_date" VARCHAR(10) NOT NULL,
|
||
"holdings_json" TEXT NOT NULL,
|
||
"total_usd" DOUBLE PRECISION,
|
||
"source" VARCHAR(16) NOT NULL,
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_kol_holdings_snapshots_wallet_id ON "kol_holdings_snapshots"("wallet_id");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_kol_snapshot_wallet_date ON "kol_holdings_snapshots"("wallet_id","snapshot_date");
|
||
|
||
CREATE TABLE IF NOT EXISTS "kol_holding_changes" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"wallet_id" INTEGER NOT NULL REFERENCES "kol_wallets"("id"),
|
||
"detected_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now()),
|
||
"ticker" VARCHAR(32) NOT NULL,
|
||
"change_type" VARCHAR(16) NOT NULL,
|
||
"usd_before" DOUBLE PRECISION,
|
||
"usd_after" DOUBLE PRECISION,
|
||
"pct_change" DOUBLE PRECISION,
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_kol_holding_changes_wallet_id ON "kol_holding_changes"("wallet_id");
|
||
CREATE INDEX IF NOT EXISTS ix_kol_holding_changes_detected_at ON "kol_holding_changes"("detected_at");
|
||
|
||
CREATE TABLE IF NOT EXISTS "kol_divergence" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"handle" VARCHAR(64) NOT NULL,
|
||
"ticker" VARCHAR(32) NOT NULL,
|
||
"post_id" INTEGER NOT NULL REFERENCES "kol_posts"("id"),
|
||
"post_action" VARCHAR(16) NOT NULL,
|
||
"post_conviction" DOUBLE PRECISION,
|
||
"post_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
"change_id" INTEGER NOT NULL REFERENCES "kol_holding_changes"("id"),
|
||
"onchain_action" VARCHAR(16) NOT NULL,
|
||
"usd_before" DOUBLE PRECISION,
|
||
"usd_after" DOUBLE PRECISION,
|
||
"onchain_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
"signal_type" VARCHAR(16) NOT NULL,
|
||
"direction" VARCHAR(8),
|
||
"days_apart" DOUBLE PRECISION,
|
||
"created_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now())
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_kol_divergence_handle ON "kol_divergence"("handle");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_kol_divergence_pair ON "kol_divergence"("post_id","change_id");
|
||
|
||
CREATE TABLE IF NOT EXISTS "telegram_bindings" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"wallet_address" VARCHAR(64),
|
||
"chat_id" BIGINT NOT NULL UNIQUE,
|
||
"tg_username" VARCHAR(64),
|
||
"bound_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now()),
|
||
"alerts_enabled" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"alert_trump" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"alert_btc_bottom" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"alert_funding" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"alert_kol_divergence" BOOLEAN NOT NULL DEFAULT FALSE,
|
||
"min_confidence" INTEGER NOT NULL DEFAULT 70,
|
||
"mute_from_hour" INTEGER,
|
||
"mute_until_hour" INTEGER,
|
||
"last_alert_at" TIMESTAMP WITHOUT TIME ZONE,
|
||
"total_alerts_sent" INTEGER NOT NULL DEFAULT 0,
|
||
"total_alerts_failed" INTEGER NOT NULL DEFAULT 0,
|
||
"digest_enabled" BOOLEAN NOT NULL DEFAULT TRUE,
|
||
"digest_hour_utc" INTEGER NOT NULL DEFAULT 12,
|
||
"last_digest_sent_at" TIMESTAMP WITHOUT TIME ZONE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_telegram_bindings_wallet_address ON "telegram_bindings"("wallet_address");
|
||
CREATE INDEX IF NOT EXISTS ix_telegram_bindings_chat_id ON "telegram_bindings"("chat_id");
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_telegram_wallet_nonnull ON telegram_bindings(wallet_address) WHERE wallet_address IS NOT NULL;
|
||
|
||
CREATE TABLE IF NOT EXISTS "macro_snapshots" (
|
||
"id" SERIAL PRIMARY KEY,
|
||
"snapshot_date" DATE NOT NULL UNIQUE,
|
||
"captured_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc',now()),
|
||
"ahr999" DOUBLE PRECISION,
|
||
"altcoin_season_index" DOUBLE PRECISION,
|
||
"fear_greed" INTEGER,
|
||
"fear_greed_label" VARCHAR(32),
|
||
"btc_dominance_pct" DOUBLE PRECISION,
|
||
"eth_btc_ratio" DOUBLE PRECISION,
|
||
"stablecoin_supply_usd" DOUBLE PRECISION,
|
||
"etf_flow_net_usd_1d" DOUBLE PRECISION,
|
||
"btc_open_interest_usd" DOUBLE PRECISION,
|
||
"composite_score" DOUBLE PRECISION,
|
||
"regime_label" VARCHAR(16),
|
||
"raw_json" TEXT
|
||
);
|
||
CREATE INDEX IF NOT EXISTS ix_macro_snapshots_snapshot_date ON "macro_snapshots"("snapshot_date");
|