FixFlow Cloud Storage Setup

This adds cross-device cloud storage for FixFlow using Supabase.

1. Create a Supabase project
- Go to https://supabase.com
- Create a new project.
- Open Project Settings, then API.
- Copy the Project URL.
- Copy the anon public key.

2. Create the work orders table
In Supabase, open SQL Editor and run:

create table if not exists public.work_orders (
  client_id text not null default 'northstar',
  id text not null,
  data jsonb not null,
  updated_at timestamptz not null default now(),
  primary key (client_id, id)
);

alter table public.work_orders enable row level security;

grant usage on schema public to anon;
grant select, insert, update, delete on public.work_orders to anon;

create policy "Allow public FixFlow read"
on public.work_orders
for select
using (true);

create policy "Allow public FixFlow insert"
on public.work_orders
for insert
with check (true);

create policy "Allow public FixFlow update"
on public.work_orders
for update
using (true)
with check (true);

create policy "Allow public FixFlow delete"
on public.work_orders
for delete
using (true);

3. Create access monitoring and approval tables
In the Supabase SQL Editor, also run:

create table if not exists public.access_logs (
  id bigint generated by default as identity primary key,
  event_type text not null,
  user_email text,
  owner_email text,
  occurred_at timestamptz not null default now(),
  app_url text,
  user_agent text
);

create table if not exists public.access_requests (
  email text primary key,
  requested_at timestamptz not null default now()
);

create table if not exists public.approved_users (
  email text primary key,
  approved boolean not null default true,
  approved_at timestamptz not null default now()
);

create table if not exists public.manager_access (
  email text primary key,
  access_code text not null unique,
  role text not null default 'manager',
  active boolean not null default true,
  created_at timestamptz not null default now(),
  created_by text
);

create extension if not exists pgcrypto;

create table if not exists public.registered_users (
  email text primary key,
  code_hash text not null,
  active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.fixflow_user_profiles (
  email text primary key,
  first_name text not null,
  surname text not null,
  phone text not null,
  registered_at timestamptz not null default now()
);

create table if not exists public.main_admin_access (
  email text primary key,
  name text not null,
  access_code text not null unique,
  active boolean not null default true,
  protected boolean not null default false
);

create table if not exists public.parts_index (
  client_id text not null default 'northstar',
  id text not null,
  name text not null,
  category text,
  work_area text not null default 'General Maintenance',
  quantity numeric not null default 0,
  unit_cost numeric not null default 0,
  updated_at timestamptz not null default now(),
  primary key (client_id, id)
);

create table if not exists public.app_updates (
  id bigint generated by default as identity primary key,
  app_name text not null default 'FixFlow',
  version text not null,
  message text not null,
  published_by text,
  published_at timestamptz not null default now()
);

For an existing parts_index table, also run:

alter table public.parts_index
add column if not exists work_area text not null default 'General Maintenance';

For existing work_orders and parts_index tables, add client_id and migrate the primary keys before using multi-company cloud sync. Back up your data first:

alter table public.work_orders add column if not exists client_id text not null default 'northstar';
alter table public.parts_index add column if not exists client_id text not null default 'northstar';

alter table public.work_orders drop constraint if exists work_orders_pkey;
alter table public.work_orders add primary key (client_id, id);

alter table public.parts_index drop constraint if exists parts_index_pkey;
alter table public.parts_index add primary key (client_id, id);

alter table public.access_logs enable row level security;
alter table public.access_requests enable row level security;
alter table public.approved_users enable row level security;
alter table public.manager_access enable row level security;
alter table public.registered_users enable row level security;
alter table public.fixflow_user_profiles enable row level security;
alter table public.main_admin_access enable row level security;
alter table public.parts_index enable row level security;
alter table public.app_updates enable row level security;

grant select, insert, update, delete on public.parts_index to anon;
grant select, insert, update on public.fixflow_user_profiles to anon;
grant select, insert on public.app_updates to anon;

create policy "Allow FixFlow access log insert"
on public.access_logs for insert with check (true);

create policy "Allow FixFlow access log admin read"
on public.access_logs for select using (true);

create policy "Allow FixFlow access request insert"
on public.access_requests for insert with check (true);

create policy "Allow FixFlow access request admin read"
on public.access_requests for select using (true);

create policy "Allow FixFlow access request admin delete"
on public.access_requests for delete using (true);

create policy "Allow FixFlow approved user admin read"
on public.approved_users for select using (true);

create policy "Allow FixFlow approved user admin insert"
on public.approved_users for insert with check (true);

create policy "Allow FixFlow approved user admin update"
on public.approved_users for update using (true) with check (true);

create policy "Allow FixFlow approved user admin delete"
on public.approved_users for delete using (true);

create policy "Allow FixFlow profile upsert"
on public.fixflow_user_profiles for insert with check (true);

create policy "Allow FixFlow profile update"
on public.fixflow_user_profiles for update using (true) with check (true);

create policy "Allow FixFlow profile read"
on public.fixflow_user_profiles for select using (true);

create or replace function public.check_fixflow_approval(check_email text)
returns boolean
language sql
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.approved_users
    where lower(email) = lower(check_email) and approved = true
  );
$$;

grant execute on function public.check_fixflow_approval(text) to anon;

create or replace function public.validate_manager_access(check_email text, check_code text)
returns boolean
language sql
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.manager_access
    where lower(email) = lower(check_email)
      and access_code = check_code
      and active = true
      and role = 'manager'
  );
$$;

grant execute on function public.validate_manager_access(text, text) to anon;

create or replace function public.register_fixflow_user(new_email text, new_code text)
returns boolean
language plpgsql
security definer
set search_path = public
as $$
begin
  if length(trim(new_email)) = 0 or length(new_code) < 6 then
    return false;
  end if;

  if exists (select 1 from public.registered_users where lower(email) = lower(new_email)) then
    return false;
  end if;

  insert into public.registered_users (email, code_hash, active)
  values (lower(new_email), crypt(new_code, gen_salt('bf')), true);
  return true;
end;
$$;

grant execute on function public.register_fixflow_user(text, text) to anon;

create or replace function public.validate_fixflow_user(check_email text, check_code text)
returns boolean
language sql
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.registered_users
    where lower(email) = lower(check_email)
      and active = true
      and code_hash = crypt(check_code, code_hash)
  );
$$;

grant execute on function public.validate_fixflow_user(text, text) to anon;

create or replace function public.resolve_main_admin_access(check_email text, check_code text)
returns jsonb
language sql
security definer
set search_path = public
as $$
  select to_jsonb(result)
  from (
    select name, email, access_code as code, protected
    from public.main_admin_access
    where lower(email) = lower(check_email)
      and access_code = check_code
      and active = true
      and (
        access_code <> 'FFADMIN-1994'
        or lower(email) = 'vandeventer2016@gmail.com'
      )
    limit 1
  ) result;
$$;

grant execute on function public.resolve_main_admin_access(text, text) to anon;

create policy "Allow Super Admin main admin read"
on public.main_admin_access for select using (true);

create policy "Allow Super Admin main admin insert"
on public.main_admin_access for insert with check (true);

create policy "Allow Super Admin main admin update"
on public.main_admin_access for update using (protected = false) with check (true);

create policy "Allow Super Admin main admin delete"
on public.main_admin_access for delete using (protected = false);

create policy "Allow FixFlow parts read"
on public.parts_index for select using (true);

create policy "Allow Admin parts insert"
on public.parts_index for insert with check (true);

create policy "Allow Admin parts update"
on public.parts_index for update using (true) with check (true);

create policy "Allow Admin parts delete"
on public.parts_index for delete using (true);

create policy "Allow FixFlow app update read"
on public.app_updates for select using (true);

create policy "Allow Super Admin app update insert"
on public.app_updates for insert with check (true);

Seed the five Main Admin records after changing placeholder emails:

insert into public.main_admin_access (name, email, access_code, active, protected)
values
  ('Conrad', 'Vandeventer2016@gmail.com', 'FFADMIN-1994', true, true),
  ('Jacques', '123456', 'FFADMIN-0610', true, false),
  ('Amanda', 'amanda.berry51515@gmail.com', 'FFADMIN-2016', true, false),
  ('Main Admin 4', 'admin4@example.com', 'FFADMIN-2024', true, false),
  ('Main Admin 5', 'admin5@example.com', 'FFADMIN-2026', true, false)
on conflict (email) do update set
  name = excluded.name,
  access_code = excluded.access_code,
  active = excluded.active,
  protected = excluded.protected;

create policy "Allow Main Admin manager access read"
on public.manager_access for select using (true);

create policy "Allow Main Admin manager access insert"
on public.manager_access for insert with check (true);

create policy "Allow Main Admin manager access update"
on public.manager_access for update using (true) with check (true);

To approve an email:

insert into public.approved_users (email, approved)
values ('person@example.com', true)
on conflict (email) do update set approved = true;

To remove access:

delete from public.approved_users where email = 'person@example.com';

4. Connect FixFlow to Supabase
Open cloud-config.js and fill in:

window.FIXFLOW_CLOUD = {
  supabaseUrl: "https://your-project.supabase.co",
  supabaseAnonKey: "your-anon-public-key",
  ownerEmail: "vandeventer2016@gmail.com",
  requireApprovedEmail: false
};

Set requireApprovedEmail to true when you want only emails listed in approved_users to enter FixFlow.

Main Admin emails and codes:
- Configure the five Main Admin email/code pairs in cloud-config.js and seed them into main_admin_access.
- A Main Admin code only works when entered with its connected email.
- FFADMIN-1994 is the protected Conrad Super Admin and cannot be updated or removed by another Main Admin.
- FFADMIN-1994 only works with Vandeventer2016@gmail.com.
- FFADMIN-2016 only works with amanda.berry51515@gmail.com.
- FFADMIN-0610 only works with admin ID 123456.
- The protected Super Admin can use Reset access to change other Main Admin emails/admin IDs and access codes.
- Only the FFADMIN-1994 Super Admin sees the Main Admin management panel.

Manager access:
- Main Admins generate Manager codes in FixFlow Admin.
- Each generated Manager code only works with its connected email.
- Managers can access user add/remove/limit tools and access logs.
- Only Main Admins can generate/limit Manager codes or access cloud/deployment controls.

Normal-user registration:
- New users register their email and personal code from the FixFlow login page.
- Registration codes must be at least 6 characters.
- Codes are stored as password hashes in registered_users.
- Normal users can create/edit/complete/wait/reopen work orders.
- Normal users cannot permanently delete tickets or open Admin.

Parts Index:
- The parts_index table shares the full parts list across devices.
- Each part is assigned to a category and displayed inside its own category dropdown.
- Admin and Manager sessions can add, update, and remove parts.
- Normal users can view the Parts Index and select parts on work orders.
- Admin and Manager must choose a sub-tab/category whenever adding a new part.
- Parts deletion is verified against cloud storage before FixFlow reports success.

Multi-company demos:
- Northstar Manufacturing, Harborview Properties, and Greenfield Health Center are included.
- Work orders and parts are separated by client_id.
- Admin and Manager sessions can switch companies from the Clients button.

Remove an existing Plumbing 2 / Plumming 2 record:

delete from public.parts_index
where lower(name) in ('plumbing 2', 'plumming 2')
   or lower(id) in ('plumbing-2', 'plumming-2');

Remove the standalone Plumbing category while keeping Plumbing Parts:

update public.parts_index
set category = 'Plumbing Parts', work_area = null
where lower(category) = 'plumbing';

5. Email notifications
FixFlow records app access, failed login, access request, work-order edits, work-order deletes, work-order status changes, imports, and app installation events in access_logs.

A browser cannot safely send automatic emails by itself. To email vandeventer2016@gmail.com automatically, configure a Supabase Database Webhook on access_logs INSERT events and connect it to a Supabase Edge Function or email provider such as Resend. The webhook/Edge Function must use a private email API key stored on the server, never in cloud-config.js.

FixFlow can also call a private endpoint directly when notificationWebhookUrl is set in cloud-config.js. See EMAIL-UPDATES-SETUP.txt.

6. Re-upload FixFlow
Upload the updated work-order-tracker folder or FixFlow-website.zip to your website host.

How it works:
- FixFlow loads local saved orders first.
- When cloud storage is configured, it pulls cloud orders after login.
- Saving, reopening, completing, deleting, and importing orders syncs to the cloud.
- Access and installation events are recorded in access_logs.
- Optional approved-email restriction uses approved_users.
- If internet is unavailable, the app keeps local data and can sync later.

Security note:
These simple policies allow anyone with your website and anon key to use the admin REST operations. The three static admin codes protect the interface, but not the database API. For business-grade security, add Supabase Auth and user-based admin RLS policies.