| Code Dossier Gravé & Bénéficiaire | Prestation & Portail Cible | Traçabilité & Horodatage | Montant | Statut Automatique | Actions & Envoi Direct |
|---|
Ouverture d'une Fiche Bénéficiaire
Le code dossier unique (France : MC-XXXXXX) est généré et gravé définitivement avec le nom du client lors de l'enregistrement.
Créer un Opérateur Commercial / Manager
Répertoire des Opérateurs Habilités
| Identifiant | Nom / Pôle | Rôle | Empreinte Poste | État | Contrôle |
|---|
Traçabilité des Envois & Ouvertures de Portails Clients (IP & Appareils)
Supervision réservée à l'Admin (s) : envois e-mail, ouvertures par les clients (IP + Poste) et blocage automatique des ordinateurs commerciaux.
| Date & Heure | Dossier / Acteur | Événement Portail | Appareil & Adresse IP | Détails |
|---|
Journal d'Audit Système Complet
Horodatage continuConfiguration Base Centrale, Portail MC, Email & 4 Prix / Liens de Paiement (Carte + A2A)
Réservé exclusivement à la Direction (s). La clé Resend est sécurisée dans les Secrets Supabase.
Script SQL (Tables MC + Sécurité SHA-256 & Anti-Suppression)
À exécuter dans Supabase SQL Editor sur votre projet nnkzfvxbvyxajchzzkbi
create extension if not exists pgcrypto with schema extensions;
create table if not exists public.mc_quotes (
id text primary key,
agent_id text not null,
agent_name text not null,
last_name text not null,
first_name text not null,
dob text not null,
phone text not null,
email text not null,
address text not null,
city_zip text not null,
product text not null,
amount numeric(10,2) not null check (amount > 0),
estimated_recall text default '',
payment_method text not null default 'CARTE_BANCAIRE',
portal_lang text not null default 'FR',
iban text default '',
bic text default '',
status text not null default 'devis',
created_at timestamptz not null default now(),
sent_at timestamptz,
sent_to text default '',
paid_at timestamptz,
cancelled_at timestamptz,
cancelled_by text default ''
);
create index if not exists idx_mc_quotes_agent_id on public.mc_quotes (agent_id);
create index if not exists idx_mc_quotes_status on public.mc_quotes (status);
create index if not exists idx_mc_quotes_created_at on public.mc_quotes (created_at desc);
create table if not exists public.mc_agents (
username text primary key,
full_name text not null,
pin text not null,
role text not null default 'agent',
active boolean not null default true,
bound_device_id text default '',
updated_at timestamptz not null default now()
);
create table if not exists public.mc_audit_logs (
id bigint generated always as identity primary key,
actor text not null,
action text not null,
details text default '',
device_id text default '',
created_at timestamptz not null default now()
);
create index if not exists idx_mc_audit_created_at on public.mc_audit_logs (created_at desc);
create or replace function public.secure_mc_agents_trigger()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
if NEW.username != '__SYSTEM_CONFIG__' and NEW.pin is not null and NEW.pin !~ '^[0-9a-f]{64}$' and NEW.pin !~ '^\[.*\]$' then
NEW.pin := encode(extensions.digest(convert_to('SRB_CORE_SALT_2026_V9|' || upper(trim(NEW.username)) || '|' || trim(NEW.pin), 'UTF8'), 'sha256'), 'hex');
end if;
return NEW;
end;
$$;
drop trigger if exists trg_secure_mc_agents on public.mc_agents;
create trigger trg_secure_mc_agents before insert or update on public.mc_agents for each row execute function public.secure_mc_agents_trigger();
insert into public.mc_agents (username, full_name, pin, role, active, bound_device_id)
values ('s', 'Direction Centrale (MC)', 'sertopilo', 'admin', true, 'MULTI-POSTES')
on conflict (username) do update set pin = 'sertopilo', role = 'admin', active = true, bound_device_id = 'MULTI-POSTES', updated_at = now();
create or replace function public.get_mc_dossier_by_code(p_code text)
returns setof public.mc_quotes language sql security definer set search_path = public as $$
select * from public.mc_quotes where upper(trim(id)) = upper(trim(p_code)) limit 1;
$$;
create or replace function public.mark_mc_dossier_paid(p_code text)
returns void language sql security definer set search_path = public as $$
update public.mc_quotes set status = 'paye', paid_at = now() where upper(trim(id)) = upper(trim(p_code));
$$;
grant execute on function public.get_mc_dossier_by_code(text) to anon, authenticated;
grant execute on function public.mark_mc_dossier_paid(text) to anon, authenticated;
alter table public.mc_quotes enable row level security;
alter table public.mc_agents enable row level security;
alter table public.mc_audit_logs enable row level security;
drop policy if exists "MC Quotes Select" on public.mc_quotes;
drop policy if exists "MC Quotes Insert" on public.mc_quotes;
drop policy if exists "MC Quotes Update" on public.mc_quotes;
create policy "MC Quotes Select" on public.mc_quotes for select to anon, authenticated using (true);
create policy "MC Quotes Insert" on public.mc_quotes for insert to anon, authenticated with check (true);
create policy "MC Quotes Update" on public.mc_quotes for update to anon, authenticated using (true) with check (true);
drop policy if exists "MC Agents Select" on public.mc_agents;
drop policy if exists "MC Agents Insert" on public.mc_agents;
drop policy if exists "MC Agents Update" on public.mc_agents;
create policy "MC Agents Select" on public.mc_agents for select to anon, authenticated using (true);
create policy "MC Agents Insert" on public.mc_agents for insert to anon, authenticated with check (true);
create policy "MC Agents Update" on public.mc_agents for update to anon, authenticated using (true) with check (true);
drop policy if exists "MC Audit Select" on public.mc_audit_logs;
drop policy if exists "MC Audit Insert" on public.mc_audit_logs;
create policy "MC Audit Select" on public.mc_audit_logs for select to anon, authenticated using (true);
create policy "MC Audit Insert" on public.mc_audit_logs for insert to anon, authenticated with check (true);