47 lines
1.2 KiB
SQL
47 lines
1.2 KiB
SQL
-- SECURITY FIX: Providers must not see email/whatsapp before purchasing a lead.
|
|
-- Create a view that masks PII for unpurchased leads.
|
|
|
|
CREATE OR REPLACE VIEW leads_for_providers AS
|
|
SELECT
|
|
l.id,
|
|
l.trip_id,
|
|
l.destination,
|
|
l.country,
|
|
l.start_date,
|
|
l.end_date,
|
|
l.number_of_travelers,
|
|
l.interests,
|
|
l.planned_activities,
|
|
l.base_price,
|
|
l.calculated_price,
|
|
l.final_price,
|
|
l.override_price,
|
|
l.status,
|
|
l.trigger_source,
|
|
l.created_at,
|
|
-- Only reveal contact info if this provider has purchased the lead
|
|
CASE
|
|
WHEN EXISTS (
|
|
SELECT 1 FROM lead_purchases lp
|
|
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
|
) THEN l.email
|
|
ELSE '***@***.***'
|
|
END AS email,
|
|
CASE
|
|
WHEN EXISTS (
|
|
SELECT 1 FROM lead_purchases lp
|
|
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
|
) THEN l.whatsapp
|
|
ELSE '+90 *** *** ****'
|
|
END AS whatsapp,
|
|
-- Flag so frontend knows if this lead is already purchased
|
|
EXISTS (
|
|
SELECT 1 FROM lead_purchases lp
|
|
WHERE lp.lead_id = l.id AND lp.provider_id = auth.uid()
|
|
) AS is_purchased
|
|
FROM leads l
|
|
WHERE l.consent_given = true;
|
|
|
|
-- Grant read access to authenticated users (RLS on underlying table still applies)
|
|
GRANT SELECT ON leads_for_providers TO authenticated;
|