espace-paie-odentas/VIREMENTS_SALAIRES_MIGRATION_SQL.md

Migration SQL - Table salary_transfers

Structure recommandée de la table

Si la table salary_transfers n'existe pas encore ou nécessite des ajustements, voici la structure complète recommandée :

-- Création de la table salary_transfers
CREATE TABLE IF NOT EXISTS salary_transfers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  
  -- Période du virement
  period_month DATE NOT NULL, -- 1er jour du mois (ex: 2025-01-01)
  period_label TEXT, -- Libellé lisible (ex: "Janvier 2025")
  
  -- Paramètres du virement
  mode TEXT NOT NULL, -- SEPA, VIREMENT, odentas_reverse
  deadline DATE NOT NULL, -- Date d'échéance du virement
  total_net NUMERIC(10,2), -- Montant total net (peut être NULL, calculé lors du PDF)
  
  -- Fichiers et documents
  callsheet_url TEXT, -- URL du PDF généré (S3)
  
  -- Notifications
  notification_sent BOOLEAN DEFAULT false,
  notification_ok BOOLEAN DEFAULT false,
  
  -- Suivi
  client_wire_received_at TIMESTAMP WITH TIME ZONE, -- Date de réception du virement client
  notes TEXT, -- Notes internes
  
  -- Timestamps
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index pour améliorer les performances
CREATE INDEX IF NOT EXISTS idx_salary_transfers_org_id ON salary_transfers(org_id);
CREATE INDEX IF NOT EXISTS idx_salary_transfers_period_month ON salary_transfers(period_month);
CREATE INDEX IF NOT EXISTS idx_salary_transfers_deadline ON salary_transfers(deadline);
CREATE INDEX IF NOT EXISTS idx_salary_transfers_mode ON salary_transfers(mode);
CREATE INDEX IF NOT EXISTS idx_salary_transfers_created_at ON salary_transfers(created_at);

-- Trigger pour mettre à jour updated_at automatiquement
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_salary_transfers_updated_at
  BEFORE UPDATE ON salary_transfers
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Policies RLS (Row Level Security)

Si vous utilisez RLS sur Supabase, voici les policies recommandées :

-- Activer RLS sur la table
ALTER TABLE salary_transfers ENABLE ROW LEVEL SECURITY;

-- Policy 1 : Les utilisateurs staff peuvent tout faire
CREATE POLICY "Staff can do everything on salary_transfers"
  ON salary_transfers
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM users
      WHERE users.id = auth.uid()
      AND users.is_staff = true
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM users
      WHERE users.id = auth.uid()
      AND users.is_staff = true
    )
  );

-- Policy 2 : Les utilisateurs peuvent voir leurs propres virements
CREATE POLICY "Users can view their organization salary_transfers"
  ON salary_transfers
  FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM user_organizations
      WHERE user_id = auth.uid()
    )
  );

-- Policy 3 : Service role (utilisé par les API routes) peut tout faire
-- Cette policy est implicite avec le service role key

Activer Realtime

Pour que les mises à jour en temps réel fonctionnent :

-- Publier la table pour Realtime
-- Dans le dashboard Supabase :
-- 1. Aller dans Database > Replication
-- 2. Activer la réplication pour la table "salary_transfers"

-- Ou via SQL (si disponible) :
ALTER PUBLICATION supabase_realtime ADD TABLE salary_transfers;

Migration pour une table existante

Si la table existe déjà mais manque certaines colonnes :

-- Ajouter les colonnes manquantes si nécessaire
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS period_label TEXT;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS mode TEXT;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS total_net NUMERIC(10,2);
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS callsheet_url TEXT;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS notification_sent BOOLEAN DEFAULT false;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS notification_ok BOOLEAN DEFAULT false;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS notes TEXT;
ALTER TABLE salary_transfers ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Mettre à jour les valeurs par défaut
UPDATE salary_transfers SET notification_sent = false WHERE notification_sent IS NULL;
UPDATE salary_transfers SET notification_ok = false WHERE notification_ok IS NULL;
UPDATE salary_transfers SET updated_at = created_at WHERE updated_at IS NULL;

-- Ajouter les contraintes si nécessaire
ALTER TABLE salary_transfers ALTER COLUMN mode SET NOT NULL;
ALTER TABLE salary_transfers ALTER COLUMN deadline SET NOT NULL;
ALTER TABLE salary_transfers ALTER COLUMN period_month SET NOT NULL;

Vérifications post-migration

-- 1. Vérifier la structure de la table
SELECT 
  column_name, 
  data_type, 
  is_nullable, 
  column_default
FROM information_schema.columns
WHERE table_name = 'salary_transfers'
ORDER BY ordinal_position;

-- 2. Vérifier les index
SELECT 
  indexname, 
  indexdef
FROM pg_indexes
WHERE tablename = 'salary_transfers';

-- 3. Vérifier les policies RLS
SELECT 
  policyname, 
  permissive, 
  roles, 
  cmd, 
  qual, 
  with_check
FROM pg_policies
WHERE tablename = 'salary_transfers';

-- 4. Vérifier si Realtime est activé
SELECT 
  schemaname,
  tablename,
  tableowner
FROM pg_publication_tables
WHERE tablename = 'salary_transfers';

-- 5. Tester l'insertion
INSERT INTO salary_transfers (
  org_id,
  period_month,
  period_label,
  mode,
  deadline,
  total_net,
  notes
) VALUES (
  'ORG_ID_TEST', -- Remplacer par un vrai org_id
  '2025-01-01',
  'Janvier 2025',
  'SEPA',
  '2025-01-15',
  1000.00,
  'Test de migration'
) RETURNING *;

-- Ne pas oublier de supprimer le test
DELETE FROM salary_transfers WHERE notes = 'Test de migration';

Contraintes de données

Contrainte sur le mode

-- Limiter les valeurs possibles pour le champ mode
ALTER TABLE salary_transfers 
ADD CONSTRAINT check_mode_values 
CHECK (mode IN ('SEPA', 'VIREMENT', 'odentas_reverse'));

Contrainte sur le period_month

-- S'assurer que period_month est toujours le 1er du mois
ALTER TABLE salary_transfers 
ADD CONSTRAINT check_period_month_first_day 
CHECK (EXTRACT(DAY FROM period_month) = 1);

Contrainte sur la deadline

-- S'assurer que la deadline est dans le futur ou le mois courant
ALTER TABLE salary_transfers 
ADD CONSTRAINT check_deadline_reasonable 
CHECK (deadline >= period_month);

Types personnalisés (optionnel)

Si vous préférez utiliser des ENUM au lieu de TEXT pour le mode :

-- Créer un type ENUM pour le mode
CREATE TYPE salary_transfer_mode AS ENUM ('SEPA', 'VIREMENT', 'odentas_reverse');

-- Modifier la colonne pour utiliser le type ENUM
ALTER TABLE salary_transfers 
ALTER COLUMN mode TYPE salary_transfer_mode 
USING mode::salary_transfer_mode;

Données de test

Pour créer des données de test :

-- Insérer des organisations de test (si nécessaire)
INSERT INTO organizations (id, name, created_at) VALUES
  ('00000000-0000-0000-0000-000000000001', 'Organisation Test 1', NOW()),
  ('00000000-0000-0000-0000-000000000002', 'Organisation Test 2', NOW())
ON CONFLICT (id) DO NOTHING;

-- Insérer des virements de test
INSERT INTO salary_transfers (
  org_id,
  period_month,
  period_label,
  mode,
  deadline,
  total_net,
  notes
) VALUES
  (
    '00000000-0000-0000-0000-000000000001',
    '2025-01-01',
    'Janvier 2025',
    'SEPA',
    '2025-01-15',
    5000.00,
    'Virement de test - Janvier'
  ),
  (
    '00000000-0000-0000-0000-000000000001',
    '2025-02-01',
    'Février 2025',
    'VIREMENT',
    '2025-02-15',
    4500.00,
    'Virement de test - Février'
  ),
  (
    '00000000-0000-0000-0000-000000000002',
    '2025-01-01',
    'Janvier 2025',
    'odentas_reverse',
    '2025-01-20',
    3000.00,
    'Virement de test - Odentas'
  );

-- Insérer des contrats de test pour ces périodes
INSERT INTO cddu_contracts (
  org_id,
  employee_first_name,
  employee_last_name,
  employee_iban,
  payment_date,
  net_amount,
  gross_amount
) VALUES
  (
    '00000000-0000-0000-0000-000000000001',
    'Jean',
    'Dupont',
    'FR7612345678901234567890123',
    '2025-01-20',
    1500.00,
    2000.00
  ),
  (
    '00000000-0000-0000-0000-000000000001',
    'Marie',
    'Martin',
    'FR7698765432109876543210987',
    '2025-01-25',
    1800.00,
    2400.00
  );

Nettoyage des données de test

-- Supprimer les virements de test
DELETE FROM salary_transfers WHERE notes LIKE '%test%';

-- Supprimer les organisations de test
DELETE FROM organizations WHERE id IN (
  '00000000-0000-0000-0000-000000000001',
  '00000000-0000-0000-0000-000000000002'
);

-- Supprimer les contrats de test
DELETE FROM cddu_contracts WHERE org_id IN (
  '00000000-0000-0000-0000-000000000001',
  '00000000-0000-0000-0000-000000000002'
);

Sauvegarde et restauration

Sauvegarder uniquement la table salary_transfers

pg_dump -h <host> -U <user> -d <database> -t salary_transfers -f salary_transfers_backup.sql

Restaurer la table

psql -h <host> -U <user> -d <database> -f salary_transfers_backup.sql

Monitoring

Vue pour surveiller les virements

CREATE OR REPLACE VIEW v_salary_transfers_summary AS
SELECT 
  st.id,
  st.period_month,
  st.period_label,
  o.name as organization_name,
  st.mode,
  st.deadline,
  st.total_net,
  CASE WHEN st.callsheet_url IS NOT NULL THEN 'Généré' ELSE 'En attente' END as pdf_status,
  st.notification_sent,
  st.notification_ok,
  st.client_wire_received_at,
  CASE 
    WHEN st.client_wire_received_at IS NOT NULL THEN 'Reçu'
    WHEN st.deadline < CURRENT_DATE THEN 'En retard'
    ELSE 'En attente'
  END as payment_status,
  st.created_at,
  st.updated_at
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id
ORDER BY st.period_month DESC, st.created_at DESC;

-- Utilisation
SELECT * FROM v_salary_transfers_summary;

Checklist de migration

• Table salary_transfers créée avec toutes les colonnes
• Index créés pour optimiser les performances
• Trigger updated_at configuré
• RLS activé avec les bonnes policies
• Realtime activé pour la table
• Contraintes de validation ajoutées
• Données de test créées et validées
• Vérifications post-migration exécutées
• Vue de monitoring créée
• Documentation mise à jour

Support

En cas de problème avec la migration :

1. Vérifier les logs Supabase
2. Vérifier les permissions de l'utilisateur
3. Tester avec des données simples d'abord
4. Consulter la documentation Supabase pour RLS et Realtime