File size: 4,127 Bytes
2010013 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | -- Migration script for WebAI verification worker
-- Run this in your PostgreSQL database to add verification tables
-- WebAI verification results table
CREATE TABLE IF NOT EXISTS public.webai_verifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tender_id UUID NOT NULL REFERENCES public.tenders(id) ON DELETE CASCADE,
analysis JSONB NOT NULL,
comparison JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_webai_verifications_tender_id ON public.webai_verifications(tender_id);
CREATE INDEX IF NOT EXISTS idx_webai_verifications_created_at ON public.webai_verifications(created_at);
-- Add verification columns to tenders table
ALTER TABLE public.tenders
ADD COLUMN IF NOT EXISTS verification_status TEXT DEFAULT 'PENDING' CHECK (verification_status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED')),
ADD COLUMN IF NOT EXISTS verification_score FLOAT DEFAULT 0.0 CHECK (verification_score >= 0.0 AND verification_score <= 1.0),
ADD COLUMN IF NOT EXISTS last_verified_at TIMESTAMP WITH TIME ZONE;
-- Add verification job type to processing_jobs
ALTER TABLE public.processing_jobs
ADD CONSTRAINT IF NOT EXISTS check_job_type
CHECK (job_type IN ('ANALYZE', 'DRAFT', 'VERIFY'));
-- Create trigger to update updated_at on webai_verifications
CREATE OR REPLACE FUNCTION update_webai_verifications_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language plpgsql;
CREATE TRIGGER webai_verifications_updated_at
BEFORE UPDATE ON public.webai_verifications
FOR EACH ROW
EXECUTE FUNCTION update_webai_verifications_updated_at();
-- Add RLS policies if using Supabase
ALTER TABLE public.webai_verifications ENABLE ROW LEVEL SECURITY;
-- Policy for service role to manage verifications
CREATE POLICY "Service role can manage verifications" ON public.webai_verifications
FOR ALL USING (auth.jwt() ->> 'role' = 'service_role')
WITH CHECK (auth.jwt() ->> 'role' = 'service_role');
-- Policy for authenticated users to read verifications for their org
CREATE POLICY "Users can read own org verifications" ON public.webai_verifications
FOR SELECT USING (
auth.uid() IS NOT NULL AND
EXISTS (
SELECT 1 FROM public.tenders t
WHERE t.id = public.webai_verifications.tender_id
AND t.organization_id = (
SELECT organization_id FROM public.users
WHERE id = auth.uid()
)
)
);
-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON public.webai_verifications TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.webai_verifications TO service_role;
GRANT USAGE ON SEQUENCE public.webai_verifications_id_seq TO authenticated;
GRANT USAGE ON SEQUENCE public.webai_verifications_id_seq TO service_role;
-- Create view for verification summary
CREATE OR REPLACE VIEW public.verification_summary AS
SELECT
t.id as tender_id,
t.title as tender_title,
t.organization_id,
t.status as tender_status,
t.verification_status,
t.verification_score,
t.last_verified_at,
wv.analysis->>'tenderTitle' as webai_title,
wv.analysis->>'procuringEntity' as webai_entity,
wv.comparison->>'agreement_score' as agreement_score,
wv.comparison->'recommendation_comparison' as bid_comparison,
wv.created_at as verification_date,
CASE
WHEN wv.comparison->>'agreement_score'::float >= 0.8 THEN 'HIGH_AGREEMENT'
WHEN wv.comparison->>'agreement_score'::float >= 0.6 THEN 'MEDIUM_AGREEMENT'
WHEN wv.comparison->>'agreement_score'::float >= 0.4 THEN 'LOW_AGREEMENT'
ELSE 'DISAGREEMENT'
END as agreement_level
FROM public.tenders t
LEFT JOIN public.webai_verifications wv ON t.id = wv.tender_id
WHERE t.status = 'ANALYSIS_READY' OR t.verification_status != 'PENDING';
-- Grant view permissions
GRANT SELECT ON public.verification_summary TO authenticated;
GRANT SELECT ON public.verification_summary TO service_role;
|