# Safe SQL Execution Guide

The SQL files in this package are designed to be safe to run more than once in Supabase SQL Editor.

## Recommended order

Run these files in this order:

```sql
-- 1. Create or upgrade schema objects
-- docs/supabase-schema.sql

-- 2. Enable RLS and create guarded policies
-- docs/rls-policies.sql

-- 3. Optional demo data
-- docs/seed-demo.sql
```

## What "safe" means here

- Extensions use `create extension if not exists`.
- Enums are created through guarded `do $$` blocks.
- Enum values are added only when missing.
- Tables use `create table if not exists`.
- Upgrade columns use `alter table ... add column if not exists`.
- Indexes use `create index if not exists`.
- RLS is enabled only if each table exists.
- Policies are created only when the policy name does not already exist.
- Demo seed rows use deterministic UUIDs and `on conflict ... do update`, so rerunning does not duplicate demo data.

## Production caution

These scripts are non-destructive. They do not drop tables, truncate data, or remove policies.

For production, still take a Supabase backup before applying schema changes:

```bash
supabase db dump --file backup.sql
```

Then execute the SQL files through Supabase Dashboard → SQL Editor, or use your migration workflow.

## Add your own user to the demo organization

The seed does not create Supabase Auth users. After logging in, copy your user ID from Supabase Auth and run:

```sql
insert into public.organization_members (organization_id, user_id, role)
values (
  '00000000-0000-0000-0000-000000000001',
  '<YOUR_AUTH_USER_ID>',
  'owner'
)
on conflict (organization_id, user_id) do update set role = excluded.role;
```

## Enum safety correction

PostgreSQL does **not** support this pattern safely:

```sql
create type organization_role as enum ('owner', 'admin', 'operator', 'viewer');
```

That statement fails on rerun with `type already exists`. The schema now uses a temporary `pg_temp.ensure_enum_type(...)` helper to create enum types only when missing, verify incompatible existing objects, and add missing labels only when needed. The helper is created in pg_temp, so it is session-scoped and disappears automatically.

Correct files:

```txt
docs/supabase-schema.sql
docs/schema.sql
public/docs/supabase-schema.sql
```


## Fix: `column "key_prefix" does not exist`

This happens when an older or partial `node_api_keys` table already exists and the schema tries to create the lookup index before the upgrade columns exist.

Run this emergency patch first:

```sql
-- docs/key-prefix-migration-fix.sql
```

Then rerun, in order:

```txt
docs/supabase-schema.sql
docs/rls-policies.sql
docs/seed-demo.sql optional
```

## Function parameter-name fix

If PostgreSQL returns `ERROR 42P13: cannot change name of input parameter "org_id"`, run:

```sql
-- docs/function-param-fix.sql
```

Then rerun:

```text
1. docs/rls-policies.sql
2. docs/seed-demo.sql optional
```

The updated `docs/rls-policies.sql` already includes this protection at the top, so this standalone fix is mainly for databases that stopped halfway through an older migration run.

- `docs/audit-events-metadata-fix.sql` repairs older `audit_events` tables missing the v5 `metadata` column.