migrations/schema.sql

-- Banking Fraud Detection System -- reproducible MySQL schema.
--
-- This file is generated (not hand-written) from the SQLAlchemy 2.0 typed
-- models in app/db/models.py, compiled against the MySQL dialect via
-- sqlalchemy.schema.CreateTable/CreateIndex. It is the human-reviewable
-- artifact for the DB-02 confirmation checkpoint: this exact DDL (applied
-- via app/db/session.py:create_all(), which calls Base.metadata.create_all())
-- is what gets executed against the database once approved.
--
-- Do not hand-edit this file and expect it to stay in sync with the ORM
-- models -- if the schema changes, regenerate this file from app/db/models.py.
--
-- Updated for the two-role (banking officer / client) auth extension:
--   * Added users (login accounts, bcrypt password hashes, officer/client
--     role) and sessions (server-side session tokens -- chosen over JWT so
--     access can be instantly revoked by deleting the row, rather than
--     waiting out a token's expiry).
--   * test_profiles / test_transactions / test_predictions were removed
--     entirely. test_profiles is replaced by `clients` (banking fields
--     only -- starting_balance, account_type -- plus a 1:1 FK to the users
--     row that logs into it; a client's name lives on users.name, not
--     duplicated). test_transactions/test_predictions are gone: client-
--     submitted transactions now flow into the SAME transactions ->
--     fraud_predictions -> fraud_alerts pipeline as everything else (via a
--     new nullable transactions.client_id), so the officer's alert queue
--     and a client's own alert view are just two filtered queries over one
--     table, not two separate systems.
--   * This was a from-scratch rebuild, not an in-place ALTER/RENAME
--     migration: all 6 prior tables were empty (pre-launch, no real user
--     data existed yet) when this change was made, confirmed with the user
--     before dropping them.
--
-- Design notes for reviewers:
--   * users.role and fraud_alerts.disposition are native MySQL ENUMs.
--   * sessions.token is a unique, indexed opaque random string (not a JWT)
--     -- validated by DB lookup on every authenticated request.
--   * clients.user_id is UNIQUE (1:1 with users) -- a login account either
--     isn't a client, or maps to exactly one client profile.
--   * transactions.client_id is nullable: NULL means officer-submitted
--     (via /predict or /transactions/batch, e.g. backfill/testing), set
--     means client-submitted via /client/transactions. ON DELETE SET NULL
--     preserves transaction history if a client account is ever removed.
--   * transactions.is_fraud is nullable: real-time submitted transactions
--     have no known label at score time; historical PaySim rows loaded for
--     reference/training carry the known label. PaySim's `isFlaggedFraud`
--     is deliberately NOT a column here at all (near-zero recall against
--     isFraud -- see .planning/research/PITFALLS.md Pitfall 2).
--   * fraud_predictions.top_features is a MySQL JSON column holding a
--     structured list of {"feature": <name>, "shap_value": <float>} objects,
--     ordered by descending absolute SHAP value -- queryable, not an opaque
--     blob. model_version records the artifact identifier (e.g. "model_v1").
--   * fraud_alerts.disposition is a native MySQL ENUM('open','reviewed',
--     'dismissed','rejected'), defaulting to 'open'; reviewed_at is nullable
--     and set when an officer moves an alert out of 'open' -- structured now
--     so a future analyst-labeling/retraining pipeline can consume it
--     without a schema rewrite (DB-04).
--   * transactions.reversed (added alongside 'rejected'): when an officer
--     rejects an alert, the underlying transaction is flagged reversed=1 and
--     excluded from balance calculations -- the reject action's real effect
--     is "give the money back." This was an in-place ALTER (not a rebuild):
--     real user data already existed in transactions/clients at the time.

CREATE TABLE users (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	email VARCHAR(255) NOT NULL,
	password_hash VARCHAR(255) NOT NULL,
	`role` ENUM('officer','client') NOT NULL,
	created_at DATETIME NOT NULL DEFAULT now(),
	PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_users_email ON users (email);

CREATE TABLE clients (
	id INTEGER NOT NULL AUTO_INCREMENT,
	user_id INTEGER NOT NULL,
	created_by_officer_id INTEGER NOT NULL,
	starting_balance NUMERIC(18, 2) NOT NULL,
	account_type VARCHAR(30) NOT NULL,
	created_at DATETIME NOT NULL DEFAULT now(),
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE,
	FOREIGN KEY(created_by_officer_id) REFERENCES users (id) ON DELETE RESTRICT
);

CREATE UNIQUE INDEX ix_clients_user_id ON clients (user_id);

CREATE TABLE sessions (
	id INTEGER NOT NULL AUTO_INCREMENT,
	token VARCHAR(64) NOT NULL,
	user_id INTEGER NOT NULL,
	created_at DATETIME NOT NULL DEFAULT now(),
	expires_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_sessions_token ON sessions (token);

CREATE TABLE transactions (
	id INTEGER NOT NULL AUTO_INCREMENT,
	client_id INTEGER,
	step INTEGER NOT NULL,
	type VARCHAR(20) NOT NULL,
	amount NUMERIC(18, 2) NOT NULL,
	name_orig VARCHAR(50) NOT NULL,
	oldbalance_org NUMERIC(18, 2) NOT NULL,
	newbalance_orig NUMERIC(18, 2) NOT NULL,
	name_dest VARCHAR(50) NOT NULL,
	oldbalance_dest NUMERIC(18, 2) NOT NULL,
	newbalance_dest NUMERIC(18, 2) NOT NULL,
	is_fraud BOOL,
	reversed BOOL NOT NULL DEFAULT 0,
	created_at DATETIME NOT NULL DEFAULT now(),
	PRIMARY KEY (id),
	FOREIGN KEY(client_id) REFERENCES clients (id) ON DELETE SET NULL
);

CREATE TABLE fraud_predictions (
	id INTEGER NOT NULL AUTO_INCREMENT,
	transaction_id INTEGER NOT NULL,
	probability NUMERIC(6, 5) NOT NULL,
	risk_tier VARCHAR(10) NOT NULL,
	model_version VARCHAR(50) NOT NULL,
	top_features JSON NOT NULL,
	created_at DATETIME NOT NULL DEFAULT now(),
	PRIMARY KEY (id),
	FOREIGN KEY(transaction_id) REFERENCES transactions (id) ON DELETE CASCADE
);

CREATE TABLE fraud_alerts (
	id INTEGER NOT NULL AUTO_INCREMENT,
	prediction_id INTEGER NOT NULL,
	risk_tier VARCHAR(10) NOT NULL,
	disposition ENUM('open','reviewed','dismissed','rejected') NOT NULL DEFAULT 'open',
	reviewed_at DATETIME,
	created_at DATETIME NOT NULL DEFAULT now(),
	PRIMARY KEY (id),
	FOREIGN KEY(prediction_id) REFERENCES fraud_predictions (id) ON DELETE CASCADE
);