# Supabase Database Setup (First Time)

This guide connects the **Subscription Tracker** (`subscriptions.html`) to your Supabase project so every entry you add is saved permanently.

> **Important:** The `SUPABASE_API_KEY` in `.env` is for the **Management API** (reading org/project info in `index.html`).  
> The subscription tracker needs two **different** values from the dashboard: **Project URL** and **anon public key**.

Your project ref (from earlier setup): `vgumubfwtgmvzccecpov`  
Project URL will be: `https://vgumubfwtgmvzccecpov.supabase.co`

---

## Step 1 — Open your Supabase project

1. Go to [https://supabase.com/dashboard](https://supabase.com/dashboard)
2. Log in with the account that owns **apps-stockyle**
3. Click the project **apps-stockyle**

---

## Step 2 — Create the database tables

1. In the left sidebar, click **SQL Editor**
2. Click **New query**
3. Open the file `supabase/schema.sql` from this repo
4. Copy **all** of it and paste into the SQL Editor
5. Click **Run** (or press Ctrl+Enter)
6. You should see **Success. No rows returned**

This creates:
- `subscriptions` — every app/subscription you add manually
- `subscription_groups` — Apps, Amazon, Walmart, etc.
- `app_users` — Sign Up / Login accounts (for deploy; passwords stored as hash)

To verify: go to **Table Editor** in the sidebar — you should see all three tables.

> **Already ran schema.sql before?** Run only the `app_users` block at the bottom of `supabase/schema.sql` in SQL Editor (from `-- App login users` onward).

---

## Step 2b — Get the service role key (required for login on deploy)

Sign Up / Login is handled by your **Python server**, not the browser. The server needs the **service_role** key to read/write `app_users` securely.

1. **Project Settings** → **API**
2. Under **Project API keys**, find **`service_role`** (secret)
3. Copy it — **never** put this in `subscriptions.html` or frontend code

Add to `.env`:

```
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...
```

When this key is set, signup/login uses Supabase. Without it, the server falls back to local `data/users.json` (fine for local dev, **not** for deploy).

---

## Step 3 — Get your API credentials

1. In the left sidebar, click **Project Settings** (gear icon at bottom)
2. Click **API**
3. Copy these two values:

| Setting | Where to find it | Example |
|---------|------------------|---------|
| **Project URL** | Under "Project URL" | `https://vgumubfwtgmvzccecpov.supabase.co` |
| **anon public key** | Under "Project API keys" → `anon` `public` | `eyJhbGciOiJIUzI1NiIs...` (long string) |

Use the **anon public** key — NOT the `service_role` key (that one bypasses security and must never go in frontend code).

---

## Step 4 — Add credentials to `.env`

Open `.env` in the project root and add (or confirm):

```
SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIs...
```

Optional first admin (auto-created on server start if `app_users` is empty):

```
ADMIN_EMAIL=admin@stockyle.com
ADMIN_PASSWORD=your-secure-password
```

Restart the server after saving. The frontend loads URL + anon key automatically — no need to edit `subscriptions.html`.

---

## Step 5 — Run and test

```bash
python server.py
```

Open in browser:

- **Subscription tracker (manual entries):** [http://localhost:8080/subscriptions](http://localhost:8080/subscriptions)
- **Infrastructure sync (Render/Supabase API):** [http://localhost:8080/](http://localhost:8080/)

1. Click **+ Add Subscription**
2. Search/select a service (e.g. Render, Walmart, Google Workspace)
3. Fill in the fields and click **Save**
4. Refresh the page — your entry should still be there (loaded from Supabase)

**Test login (after Step 2b + Step 4):**
1. Sign Up with your email and password
2. In Supabase → **Table Editor** → `app_users` — confirm your row appears
3. Log out → Log in again with the same credentials

---

## Step 6 — Deploy (Render / Vercel)

When deploying, set these **environment variables** on your host:

| Variable | Required | Purpose |
|----------|----------|---------|
| `SUPABASE_URL` | Yes | Project URL |
| `SUPABASE_ANON_KEY` | Yes | Manual subscription saves (frontend) |
| `SUPABASE_SERVICE_ROLE_KEY` | Yes | Sign Up / Login (server only) |
| `RENDER_API_KEY` | For integrated Render | API sync |
| `SUPABASE_API_KEY` | For integrated Supabase | Management API sync |
| `ADMIN_EMAIL` | Optional | First admin if table is empty |
| `ADMIN_PASSWORD` | Optional | First admin password |

On **Render**, use the included `render.yaml` blueprint or add the same vars in **Environment**.

After deploy:
1. Run `schema.sql` in Supabase if you have not already (including `app_users`)
2. Set `SUPABASE_SERVICE_ROLE_KEY` on the host
3. Restart / redeploy — server creates bootstrap admin if table is empty
4. Open your app URL → **Sign Up** or **Login** — user is stored in `app_users`
5. Sign up once → log out → log in again with same email/password

**Verify in Supabase:** Table Editor → `app_users` → you should see your row (`email`, `name`, `password_hash` — not plain password).

---

## Troubleshooting

| Problem | Fix |
|---------|-----|
| "Configure Supabase" banner won't go away | Fill in `SUPABASE_CONFIG.url` and `anonKey` in `subscriptions.html` |
| `relation "subscriptions" does not exist` | Run `supabase/schema.sql` in SQL Editor (Step 2) |
| `relation "app_users" does not exist` | Run the `app_users` section at bottom of `schema.sql` |
| Sign up fails / "Invalid API key" | Add `SUPABASE_SERVICE_ROLE_KEY` to `.env` or host env vars |
| Login works locally but not after deploy | Deploy host must have `SUPABASE_SERVICE_ROLE_KEY`; `data/users.json` is not used on server |
| New user sees admin's subscriptions | Run `supabase/migrate_user_isolation.sql` in SQL Editor, then restart app and log in again |
| Admin lost subscriptions after migration | Rows without `user_id` need the migration UPDATE; change email in SQL if not admin@stockyle.com |
| Save fails with 401 / RLS error | Re-run the RLS policies at the bottom of `schema.sql` |
| Data disappears on refresh | Check browser console for errors; confirm Table Editor shows rows |

---

## Security note

- **App login (`app_users`):** Passwords are stored as a SHA-256 hash on the server — never plain text.
- **Subscription login fields (`subscriptions` table):** Stored as entered (manual credential tracking the client requested).

For production, consider restricting who can access the app and tightening RLS on `subscriptions`.

---

## Quick reference — what gets stored

| Field | Client requirement |
|-------|-------------------|
| Name | Service name (Render, Walmart, etc.) |
| Group | Apps / Amazon / Walmart / … |
| Free or paid | Billing type |
| Cost monthly/yearly | How much you pay |
| Renewal date | When next payment is due |
| Login URL, email, username, password | Access credentials |
| Requester | Who requested the subscription |
| Main users | Who uses it |
| Purpose | What it's used for |
| Credit card | Which card is on file |
| Trial end date | Free trial alerts |
| Alert days before | Stop before auto-renewal |
| Status | Active / unused (for cancel alerts) |