57 lines
3.3 KiB
SQL
57 lines
3.3 KiB
SQL
-- Migration 001: Premium "My Case" tier
|
|
-- Run against dobetternorge_maindb:
|
|
-- mysql -u root dobetternorge_maindb < scripts/sql/001_premium_my_case.sql
|
|
--
|
|
-- This migration:
|
|
-- 1. Creates case_tool_results — persists every tool run for paid users
|
|
-- 2. Adds trial_* columns to user_tool_credits — mirrors Stripe trial state
|
|
-- 3. Simplifies tier enums to the MVP ladder (free|plus|pro)
|
|
|
|
START TRANSACTION;
|
|
|
|
-- ── 1. case_tool_results ────────────────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS case_tool_results (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id INT UNSIGNED NOT NULL COMMENT 'Session user (may be a family member)',
|
|
owner_user_id INT UNSIGNED NOT NULL COMMENT 'caseResolveClientId result; whose corpus was queried',
|
|
tool VARCHAR(40) NOT NULL COMMENT 'korrespond | advocate | barnevernet | deep-research | discrepancy | timeline',
|
|
title VARCHAR(200) NULL COMMENT 'User-editable; defaults to first 80 chars of input',
|
|
used_case_context TINYINT(1) NOT NULL DEFAULT 0,
|
|
case_doc_ids JSON NULL COMMENT 'case_documents.id values that contributed chunks',
|
|
input_payload JSON NOT NULL COMMENT 'Full request body for re-run',
|
|
output_payload JSON NOT NULL COMMENT 'Full tool response',
|
|
model VARCHAR(60) NULL COMMENT 'Azure deployment name actually used',
|
|
latency_ms INT UNSIGNED NULL,
|
|
credits_charged TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
pinned TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME NULL,
|
|
PRIMARY KEY (id),
|
|
KEY idx_user_created (user_id, created_at DESC),
|
|
KEY idx_owner_tool (owner_user_id, tool, created_at DESC),
|
|
KEY idx_pinned (user_id, pinned, created_at DESC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Saved tool outputs per case — premium "My Case" feature';
|
|
|
|
-- ── 2. trial columns on user_tool_credits ───────────────────────────────────
|
|
ALTER TABLE user_tool_credits
|
|
ADD COLUMN trial_started_at DATETIME NULL AFTER tier,
|
|
ADD COLUMN trial_expires_at DATETIME NULL AFTER trial_started_at,
|
|
ADD COLUMN trial_downgraded_at DATETIME NULL AFTER trial_expires_at;
|
|
|
|
-- ── 3. simplify tier enum ───────────────────────────────────────────────────
|
|
-- No production subscribers exist (demo configuration only) — safe to collapse.
|
|
UPDATE user_tool_credits SET tier = 'plus' WHERE tier = 'light';
|
|
UPDATE user_tool_credits SET tier = 'pro' WHERE tier = 'pro_plus';
|
|
|
|
ALTER TABLE user_tool_credits
|
|
MODIFY COLUMN tier ENUM('free','plus','pro') NOT NULL DEFAULT 'free';
|
|
|
|
UPDATE user_subscriptions SET tier = 'plus' WHERE tier = 'light';
|
|
UPDATE user_subscriptions SET tier = 'pro' WHERE tier = 'pro_plus';
|
|
|
|
ALTER TABLE user_subscriptions
|
|
MODIFY COLUMN tier ENUM('plus','pro') NOT NULL;
|
|
|
|
COMMIT;
|