| |
| CREATE TABLE users ( |
| id BIGSERIAL PRIMARY KEY, |
| email TEXT UNIQUE NOT NULL, |
| name TEXT NOT NULL, |
| password TEXT NOT NULL, |
| plan TEXT NOT NULL DEFAULT 'free', |
| stripe_cid TEXT, |
| api_key TEXT, |
| is_verified BOOLEAN DEFAULT FALSE, |
| created_at TIMESTAMPTZ DEFAULT NOW() |
| ); |
|
|
| |
| CREATE TABLE sessions ( |
| token TEXT PRIMARY KEY, |
| user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| expires_at TIMESTAMPTZ NOT NULL |
| ); |
|
|
| |
| CREATE TABLE invoices ( |
| id BIGSERIAL PRIMARY KEY, |
| user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| filename TEXT NOT NULL, |
| vendor TEXT, |
| inv_number TEXT, |
| inv_date TEXT, |
| due_date TEXT, |
| amount NUMERIC(12,2), |
| vat_amount NUMERIC(12,2), |
| total NUMERIC(12,2), |
| currency TEXT DEFAULT 'USD', |
| status TEXT DEFAULT 'done', |
| is_duplicate BOOLEAN DEFAULT FALSE, |
| confidence NUMERIC(4,3), |
| raw_json JSONB, |
| created_at TIMESTAMPTZ DEFAULT NOW() |
| ); |
|
|
| |
| ALTER TABLE users ENABLE ROW LEVEL SECURITY; |
| ALTER TABLE sessions ENABLE ROW LEVEL SECURITY; |
| ALTER TABLE invoices ENABLE ROW LEVEL SECURITY; |
|
|
| |
| CREATE POLICY "Users can view own profile" ON users FOR SELECT USING (auth.uid() = id); |
| CREATE POLICY "Users can update own profile" ON users FOR UPDATE USING (auth.uid() = id); |
|
|
| CREATE POLICY "Users can view own invoices" ON invoices FOR SELECT USING (auth.uid() = user_id); |
| CREATE POLICY "Users can insert own invoices" ON invoices FOR INSERT WITH CHECK (auth.uid() = user_id); |
| CREATE POLICY "Users can delete own invoices" ON invoices FOR DELETE USING (auth.uid() = user_id); |
|
|
| CREATE INDEX idx_invoices_user_month ON invoices(user_id, created_at); |
