40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
-- Migration: Add new columns and tables for Aletheia v0.2
|
|
-- Safe: uses IF NOT EXISTS / ADD COLUMN IF NOT EXISTS (PostgreSQL 11+)
|
|
|
|
-- User profile fields
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS allergies TEXT DEFAULT '[]';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS health_profile VARCHAR DEFAULT 'normal';
|
|
|
|
-- Achievements table
|
|
CREATE TABLE IF NOT EXISTS achievements (
|
|
id SERIAL PRIMARY KEY,
|
|
code VARCHAR UNIQUE NOT NULL,
|
|
name VARCHAR NOT NULL,
|
|
description VARCHAR NOT NULL,
|
|
emoji VARCHAR DEFAULT '🏆',
|
|
target INTEGER DEFAULT 1
|
|
);
|
|
|
|
-- User achievements
|
|
CREATE TABLE IF NOT EXISTS user_achievements (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) NOT NULL,
|
|
achievement_id INTEGER REFERENCES achievements(id) NOT NULL,
|
|
unlocked_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Shopping list
|
|
CREATE TABLE IF NOT EXISTS shopping_list (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) NOT NULL,
|
|
product_name VARCHAR NOT NULL,
|
|
barcode VARCHAR,
|
|
checked BOOLEAN DEFAULT FALSE,
|
|
added_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_user_achievements_user ON user_achievements(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_shopping_list_user ON shopping_list(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_scans_user_date ON scans(user_id, scanned_at);
|