299 lines
10 KiB
PL/PgSQL
299 lines
10 KiB
PL/PgSQL
-- This script defines PostgreSQL functions and triggers for managing service scores:
|
|
-- 1. Automatically calculates and updates privacy, trust, and overall scores
|
|
-- for services when services or their attributes change.
|
|
-- 2. Updates the isRecentlyApproved flag for services approved within the last 15 days.
|
|
-- 3. Queues asynchronous score recalculation in "ServiceScoreRecalculationJob"
|
|
-- when an "Attribute" definition (e.g., points) is updated, ensuring
|
|
-- efficient handling of widespread score updates.
|
|
|
|
-- Drop existing triggers first
|
|
DROP TRIGGER IF EXISTS service_score_update_trigger ON "Service";
|
|
DROP TRIGGER IF EXISTS service_attribute_change_trigger ON "ServiceAttribute";
|
|
DROP TRIGGER IF EXISTS attribute_change_trigger ON "Attribute";
|
|
|
|
-- Drop existing functions
|
|
DROP FUNCTION IF EXISTS calculate_service_scores();
|
|
DROP FUNCTION IF EXISTS calculate_privacy_score();
|
|
DROP FUNCTION IF EXISTS calculate_trust_score();
|
|
DROP FUNCTION IF EXISTS calculate_overall_score();
|
|
DROP FUNCTION IF EXISTS recalculate_scores_for_attribute();
|
|
|
|
-- Calculate privacy score based on service attributes and properties
|
|
CREATE OR REPLACE FUNCTION calculate_privacy_score(service_id INT)
|
|
RETURNS INT AS $$
|
|
DECLARE
|
|
privacy_score INT := 0;
|
|
kyc_factor INT;
|
|
clarification_factor INT := 0;
|
|
onion_or_i2p_factor INT := 0;
|
|
monero_factor INT := 0;
|
|
attributes_score INT := 0;
|
|
BEGIN
|
|
-- Get service data
|
|
SELECT
|
|
CASE
|
|
WHEN "kycLevel" = 0 THEN 25 -- No KYC is best for privacy
|
|
WHEN "kycLevel" = 1 THEN 10 -- Minimal KYC
|
|
WHEN "kycLevel" = 2 THEN -5 -- Moderate KYC
|
|
WHEN "kycLevel" = 3 THEN -15 -- More KYC
|
|
WHEN "kycLevel" = 4 THEN -25 -- Full mandatory KYC
|
|
ELSE 0 -- Default to no change
|
|
END
|
|
INTO kyc_factor
|
|
FROM "Service"
|
|
WHERE "id" = service_id;
|
|
|
|
-- Adjust score based on KYC level clarification modifiers
|
|
SELECT
|
|
CASE
|
|
WHEN "kycLevelClarification" = 'DEPENDS_ON_PARTNERS' THEN -5
|
|
ELSE 0 -- Default modifier when no clarification or unrecognized value
|
|
END
|
|
INTO clarification_factor
|
|
FROM "Service"
|
|
WHERE "id" = service_id;
|
|
|
|
-- Check for onion or i2p URLs
|
|
IF EXISTS (
|
|
SELECT 1 FROM "Service"
|
|
WHERE "id" = service_id AND (array_length("onionUrls", 1) > 0 OR array_length("i2pUrls", 1) > 0)
|
|
) THEN
|
|
onion_or_i2p_factor := 5;
|
|
END IF;
|
|
|
|
-- Check for Monero acceptance
|
|
IF EXISTS (
|
|
SELECT 1 FROM "Service"
|
|
WHERE "id" = service_id AND 'MONERO' = ANY("acceptedCurrencies")
|
|
) THEN
|
|
monero_factor := 5;
|
|
END IF;
|
|
|
|
-- Calculate score from privacy attributes - directly use the points
|
|
SELECT COALESCE(SUM(a."privacyPoints"), 0)
|
|
INTO attributes_score
|
|
FROM "ServiceAttribute" sa
|
|
JOIN "Attribute" a ON sa."attributeId" = a."id"
|
|
WHERE sa."serviceId" = service_id;
|
|
|
|
-- Calculate final privacy score (base 100)
|
|
privacy_score := 50 + kyc_factor + clarification_factor + onion_or_i2p_factor + monero_factor + attributes_score;
|
|
|
|
-- Ensure the score is in reasonable bounds (0-100)
|
|
privacy_score := GREATEST(0, LEAST(100, privacy_score));
|
|
|
|
RETURN privacy_score;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Calculate trust score based on service attributes and verification status
|
|
CREATE OR REPLACE FUNCTION calculate_trust_score(service_id INT)
|
|
RETURNS INT AS $$
|
|
DECLARE
|
|
trust_score INT := 0;
|
|
verification_factor INT;
|
|
attributes_score INT := 0;
|
|
recently_approved_factor INT := 0;
|
|
tos_penalty_factor INT := 0;
|
|
operating_since_factor INT := 0;
|
|
legally_registered_factor INT := 0;
|
|
BEGIN
|
|
-- Get verification status factor
|
|
SELECT
|
|
CASE
|
|
WHEN "verificationStatus" = 'VERIFICATION_SUCCESS' THEN 10
|
|
WHEN "verificationStatus" = 'APPROVED' THEN 5
|
|
WHEN "verificationStatus" = 'COMMUNITY_CONTRIBUTED' THEN 0
|
|
WHEN "verificationStatus" = 'VERIFICATION_FAILED' THEN -50
|
|
ELSE 0
|
|
END
|
|
INTO verification_factor
|
|
FROM "Service"
|
|
WHERE id = service_id;
|
|
|
|
-- Calculate score from trust attributes - directly use the points
|
|
SELECT COALESCE(SUM(a."trustPoints"), 0)
|
|
INTO attributes_score
|
|
FROM "ServiceAttribute" sa
|
|
JOIN "Attribute" a ON sa."attributeId" = a.id
|
|
WHERE sa."serviceId" = service_id;
|
|
|
|
-- Apply penalty if service was approved within the last 15 days
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM "Service"
|
|
WHERE id = service_id
|
|
AND "approvedAt" IS NOT NULL
|
|
AND "verificationStatus" = 'APPROVED'
|
|
AND (NOW() - "approvedAt") <= INTERVAL '15 days'
|
|
) THEN
|
|
recently_approved_factor := -10;
|
|
-- Update the isRecentlyApproved flag to true
|
|
UPDATE "Service"
|
|
SET "isRecentlyApproved" = TRUE
|
|
WHERE id = service_id;
|
|
ELSE
|
|
-- Update the isRecentlyApproved flag to false
|
|
UPDATE "Service"
|
|
SET "isRecentlyApproved" = FALSE
|
|
WHERE id = service_id;
|
|
END IF;
|
|
|
|
-- Apply penalty if ToS cannot be analyzed
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM "Service"
|
|
WHERE id = service_id
|
|
AND "tosReviewAt" IS NOT NULL
|
|
AND "tosReview" IS NULL
|
|
) THEN
|
|
tos_penalty_factor := -3;
|
|
END IF;
|
|
|
|
-- Determine trust adjustment based on operatingSince
|
|
SELECT
|
|
CASE
|
|
WHEN "operatingSince" IS NULL THEN 0
|
|
WHEN AGE(NOW(), "operatingSince") < INTERVAL '1 year' THEN -4 -- New service penalty
|
|
WHEN AGE(NOW(), "operatingSince") >= INTERVAL '2 years' THEN 5 -- Mature service bonus
|
|
ELSE 0
|
|
END
|
|
INTO operating_since_factor
|
|
FROM "Service"
|
|
WHERE id = service_id;
|
|
|
|
-- Check for legal registration (country code or company name)
|
|
IF EXISTS (
|
|
SELECT 1 FROM "Service"
|
|
WHERE id = service_id AND ("registrationCountryCode" IS NOT NULL OR "registeredCompanyName" IS NOT NULL)
|
|
) THEN
|
|
legally_registered_factor := 2;
|
|
END IF;
|
|
|
|
-- Calculate final trust score (base 100)
|
|
trust_score := 50 + verification_factor + attributes_score + recently_approved_factor + tos_penalty_factor + operating_since_factor + legally_registered_factor;
|
|
|
|
-- Ensure the score is in reasonable bounds (0-100)
|
|
trust_score := GREATEST(0, LEAST(100, trust_score));
|
|
|
|
RETURN trust_score;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Calculate overall score based on weighted average of privacy and trust scores
|
|
CREATE OR REPLACE FUNCTION calculate_overall_score(service_id INT, privacy_score INT, trust_score INT)
|
|
RETURNS INT AS $$
|
|
DECLARE
|
|
overall_score INT;
|
|
BEGIN
|
|
overall_score := CAST(((privacy_score * 0.6) + (trust_score * 0.4)) / 10.0 AS INT);
|
|
RETURN GREATEST(0, LEAST(10, overall_score));
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Main function to calculate all scores for a service
|
|
CREATE OR REPLACE FUNCTION calculate_service_scores()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
privacy_score INT;
|
|
trust_score INT;
|
|
overall_score INT;
|
|
service_id INT;
|
|
BEGIN
|
|
-- Determine which service ID to use based on the trigger context and table
|
|
IF TG_TABLE_NAME = 'Service' THEN
|
|
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
service_id := NEW."id";
|
|
END IF;
|
|
ELSIF TG_TABLE_NAME = 'ServiceAttribute' THEN
|
|
IF TG_OP = 'DELETE' THEN
|
|
service_id := OLD."serviceId";
|
|
ELSE -- INSERT or UPDATE
|
|
service_id := NEW."serviceId";
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Calculate each score
|
|
privacy_score := calculate_privacy_score(service_id);
|
|
trust_score := calculate_trust_score(service_id);
|
|
overall_score := calculate_overall_score(service_id, privacy_score, trust_score);
|
|
|
|
-- Cap score if service is flagged as scam (verificationStatus = 'VERIFICATION_FAILED')
|
|
IF (SELECT "verificationStatus" FROM "Service" WHERE "id" = service_id) = 'VERIFICATION_FAILED' THEN
|
|
IF overall_score > 3 THEN
|
|
overall_score := 3;
|
|
ELSIF overall_score < 0 THEN
|
|
overall_score := 0;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Update the service with the new scores
|
|
UPDATE "Service"
|
|
SET
|
|
"privacyScore" = privacy_score,
|
|
"trustScore" = trust_score,
|
|
"overallScore" = overall_score
|
|
WHERE "id" = service_id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create trigger to recalculate scores when service is created or updated
|
|
CREATE TRIGGER service_score_update_trigger
|
|
AFTER INSERT OR UPDATE
|
|
ON "Service"
|
|
FOR EACH ROW
|
|
WHEN (pg_trigger_depth() < 2) -- Prevent recursive triggering
|
|
EXECUTE FUNCTION calculate_service_scores();
|
|
|
|
-- Create trigger to recalculate scores when service attributes change
|
|
CREATE TRIGGER service_attribute_change_trigger
|
|
AFTER INSERT OR UPDATE OR DELETE
|
|
ON "ServiceAttribute"
|
|
FOR EACH ROW
|
|
WHEN (pg_trigger_depth() < 2) -- Prevent recursive triggering
|
|
EXECUTE FUNCTION calculate_service_scores();
|
|
|
|
-- Function to queue score recalculation for all services with a specific attribute
|
|
CREATE OR REPLACE FUNCTION queue_service_score_recalculation_for_attribute()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
service_rec RECORD;
|
|
BEGIN
|
|
-- Only trigger recalculation if relevant fields changed
|
|
IF (TG_OP = 'UPDATE' AND (
|
|
OLD."privacyPoints" != NEW."privacyPoints" OR
|
|
OLD."trustPoints" != NEW."trustPoints" OR
|
|
OLD."type" != NEW."type" OR
|
|
OLD."category" != NEW."category"
|
|
)) THEN
|
|
-- Find all services that have this attribute and queue a recalculation job
|
|
FOR service_rec IN
|
|
SELECT DISTINCT sa."serviceId"
|
|
FROM "ServiceAttribute" sa
|
|
WHERE sa."attributeId" = NEW.id
|
|
LOOP
|
|
-- Insert a job into the queue table
|
|
-- ON CONFLICT clause ensures we don't queue the same service multiple times per transaction
|
|
INSERT INTO "ServiceScoreRecalculationJob" ("serviceId", "createdAt", "processedAt")
|
|
VALUES (service_rec."serviceId", NOW(), NULL)
|
|
ON CONFLICT ("serviceId") DO UPDATE SET "processedAt" = NULL, "createdAt" = NOW();
|
|
|
|
END LOOP;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create constraint trigger to queue score recalculation when attributes are updated
|
|
DROP TRIGGER IF EXISTS attribute_change_trigger ON "Attribute";
|
|
CREATE CONSTRAINT TRIGGER attribute_change_trigger
|
|
AFTER UPDATE
|
|
ON "Attribute"
|
|
DEFERRABLE INITIALLY DEFERRED
|
|
FOR EACH ROW
|
|
WHEN (pg_trigger_depth() < 2)
|
|
EXECUTE FUNCTION queue_service_score_recalculation_for_attribute();
|