-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathπ DEC Report β Hyper Vibe Coding Course
More file actions
214 lines (153 loc) Β· 10.2 KB
/
π DEC Report β Hyper Vibe Coding Course
File metadata and controls
214 lines (153 loc) Β· 10.2 KB
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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
Detailed Evaluation & Compliance Report | Project: yhtmuibgdnxhbgboajhc | Date: April 11, 2026
π¨ Executive Summary
Category Issues Found Critical High Medium Low/Info
Security 6 1 2 2 1
Performance 4 0 0 1 3
Code Quality 3 1 1 1 0
Schema Design 3 0 1 2 0
π΄ CRITICAL Issues
CRIT-01 β Missing courses Table (Ghost Reference)
Severity: π΄ CRITICAL | Category: Code Quality / Data Integrity
The stripe-webhook Edge Function queries supabase.from('courses') at runtime β but the courses table does not exist in the database . This means any payment where courseId is supplied will silently return OK β course not found β money taken, no enrollment granted.
Affected File: supabase/functions/stripe-webhook/index.ts β the course validation block
Proof of Concept:
text
POST /functions/v1/stripe-webhook
β event: checkout.session.completed
β courseId = "hyper-vibe-course-01" (valid Stripe session)
β supabaseAdmin.from('courses').select('id').eq('id', courseId)
β Returns: [] (table doesn't exist β courseLookupError fires)
β Response: 200 "OK β course not found"
β Student NOT enrolled β
Fix: Either create the courses table OR remove the course-validation block and trust client_reference_id with a pre-validated allowlist.
π HIGH Severity Issues
HIGH-01 β leaderboard_top Function: Mutable search_path
Severity: π HIGH | Category: Security | Affected: public.leaderboard_top
The function is SECURITY DEFINER (runs with elevated privileges) but has no search_path set. An attacker who can create objects in any schema could shadow public.users or public.achievements with a malicious table/function to hijack execution.
Function Definition:
sql
-- VULNERABLE: No SET search_path = public, pg_temp
CREATE OR REPLACE FUNCTION public.leaderboard_top(...)
SECURITY DEFINER
AS $function$
SELECT u.id, u.email, COUNT(a.id)...
$function$
Fix:
sql
ALTER FUNCTION public.leaderboard_top(integer)
SET search_path = public, pg_temp;
π Remediation guide
HIGH-02 β leaderboard_top Exposes All User Emails
Severity: π HIGH | Category: Security / Privacy (GDPR relevance for GB)
The leaderboard_top function returns u.email in its result set . Any caller with execute permission on this function gets a full list of every user's email address β a potential data breach and GDPR violation for your Welsh-based project.
Fix: Remove u.email from the SELECT or replace with a display name / username column.
sql
-- Replace this:
SELECT u.id AS user_id, u.email, COUNT(a.id)...
-- With this:
SELECT u.id AS user_id, COUNT(a.id)...
π‘ MEDIUM Severity Issues
MED-01 β RLS WITH CHECK (true) on achievements, enrollments, payments
Severity: π‘ MEDIUM | Category: Security | Affected Tables: achievements, enrollments, payments
All three tables have INSERT policies with WITH CHECK = true for the service role. While the intent is that only the service role uses these, the policies are not scoped to a specific role (the roles field shows -). This means the policies could be matched more broadly depending on grant configuration.
Current (risky):
sql
-- "Service role can insert achievements"
WITH CHECK (true) -- β no role restriction in the policy itself
Recommended Fix: Scope policies explicitly:
sql
CREATE POLICY "Service role can insert achievements"
ON public.achievements FOR INSERT
TO service_role -- β add this
WITH CHECK (true);
π Remediation guide
MED-02 β playtest_responses: Unauthenticated INSERT with No Rate Limiting
Severity: π‘ MEDIUM | Category: Security | Affected Table: public.playtest_responses
The policy "Anyone can submit playtest response" allows unlimited anonymous inserts β no auth required, no rate limit at the DB level. This is a spam/flood vector β anyone could fill your table with junk data.
Fix Options:
Add require_auth check: WITH CHECK (auth.uid() IS NOT NULL)
Or implement rate limiting at the API gateway / Edge Function level
Add a UNIQUE constraint on email to prevent duplicate submissions
MED-03 β enrollments.course_id Hardcoded Default
Severity: π‘ MEDIUM | Category: Schema Design | Affected: public.enrollments
The course_id column defaults to 'hyper-vibe-course-01'::text β a hardcoded string with no FK constraint to any courses table (which doesn't exist anyway β see CRIT-01). This means bad data can silently enter.
Fix: Either create a proper courses table and add FK, or remove the default to force explicit values.
MED-04 β users.email is Nullable with No Unique Constraint
Severity: π‘ MEDIUM | Category: Schema Design | Affected: public.users
users.email is marked nullable with no unique constraint. The stripe-webhook looks up users by email β if two rows share an email (or email is NULL), .maybeSingle() could fail or return wrong data. Auth email comes from auth.users but the public mirror can drift.
Fix:
sql
ALTER TABLE public.users
ALTER COLUMN email SET NOT NULL,
ADD CONSTRAINT users_email_key UNIQUE (email);
π΅ PERFORMANCE Issues
PERF-01 β 4 Unused Indexes (Dead Weight)
Severity: π΅ INFO | Category: Performance | Affected Tables: achievements, enrollments, payments, discord_links
The following indexes have never been used (zero scans since creation):
Index Table
idx_achievements_user_id achievements
idx_enrollments_user_id enrollments
idx_payments_user_id payments
idx_discord_links_user_id discord_links
β οΈ Note: Tables are currently empty (0 rows each) , so these indexes will become useful once data flows in. Do NOT drop them yet β they are correctly placed on FK columns that will be heavily queried. Re-evaluate after 30 days of production load.
π Remediation guide
PERF-02 β No Index on playtest_responses.email
Severity: π΅ INFO | Category: Performance | Affected: public.playtest_responses
The SELECT RLS policy on playtest_responses filters by email = (SELECT users.email ...) β but there's no index on playtest_responses.email. As submissions grow, this will cause full table scans.
Fix:
sql
CREATE INDEX idx_playtest_responses_email
ON public.playtest_responses (email);
PERF-03 β leaderboard_top Full Table Join on Every Call
Severity: π‘ MEDIUM | Category: Performance | Affected: public.leaderboard_top
The function does a full JOIN + GROUP BY + COUNT across all users and achievements on every call with no caching or materialized view. As user/achievement counts grow this will be slow.
Fix: Add a materialized view refreshed on a schedule, or cache the result in the application layer (e.g., revalidate every 60s).
βοΈ CODE QUALITY Issues
CQ-01 β Edge Function: handle_new_user & rls_auto_enable Are SECURITY DEFINER Without search_path
Severity: π HIGH | Category: Security / Code Quality | Affected: handle_new_user, rls_auto_enable
Both of these SECURITY DEFINER functions have the same mutable search_path risk as leaderboard_top. All three need fixing.
Fix for all three:
sql
ALTER FUNCTION public.handle_new_user() SET search_path = public, pg_temp;
ALTER FUNCTION public.rls_auto_enable() SET search_path = public, pg_temp;
ALTER FUNCTION public.leaderboard_top(integer) SET search_path = public, pg_temp;
CQ-02 β stripe-webhook has verify_jwt: false (Correct but Undocumented Risk)
Severity: π’ PASS with note | Category: Code Quality | Affected: stripe-webhook
JWT verification is correctly disabled (Stripe can't provide a Supabase JWT), and the function correctly validates the Stripe webhook signature instead . The security model is sound. However β the Stripe signature secret (STRIPE_WEBHOOK_SECRET) must be rotated if ever exposed, and there's no alerting configured for failed signature verifications beyond console.error.
Recommendation: Add a Supabase log alert or external monitoring (e.g., Sentry) for status_code: 400 from this function.
CQ-03 β No UPDATE or DELETE Policies on Most Tables
Severity: π‘ MEDIUM | Category: Code Quality / Security | Affected: achievements, enrollments, payments, discord_links
These tables have no UPDATE or DELETE RLS policies defined. With RLS enabled, this means updates/deletes are blocked for all roles β which may be intentional, but is risky if an admin workflow ever needs to correct data. It should be explicitly documented as intentional.
Recommendation: Add explicit RESTRICT comments or create admin-scoped policies:
sql
-- If intentional, document it:
COMMENT ON TABLE public.payments IS 'Immutable β no UPDATE/DELETE by design';
β
What's Working Well (Passed Checks)
β
RLS enabled on all 7 tables β good baseline posture
β
Stripe webhook uses server-side email lookup β userId never trusted from frontend
β
Idempotent upsert on enrollment β safe against Stripe duplicate events
β
payments.stripe_session_id has a UNIQUE constraint β prevents double-charge enrollments
β
discord_links.discord_id is unique β no duplicate Discord account links
β
waitlist.email is unique β no duplicate waitlist entries
β
Auth cascade delete on users table β clean teardown when auth user deleted
β
Edge function is live & healthy β last invocation returned 200 in 495ms
π οΈ Prioritised Remediation Checklist
text
π΄ CRITICAL (Fix Today)
[ ] CRIT-01: Create the `courses` table OR remove ghost validation in stripe-webhook
π HIGH (Fix This Week)
[ ] HIGH-01: Add SET search_path to all 3 SECURITY DEFINER functions
[ ] HIGH-02: Remove email from leaderboard_top return set
[ ] CQ-01: Fix search_path on handle_new_user + rls_auto_enable
π‘ MEDIUM (Fix This Sprint)
[ ] MED-01: Scope INSERT policies to TO service_role explicitly
[ ] MED-02: Add rate-limiting or auth check to playtest_responses INSERT
[ ] MED-03: Remove hardcoded course_id default from enrollments
[ ] MED-04: Add NOT NULL + UNIQUE constraint on users.email
[ ] PERF-03: Plan materialized view for leaderboard_top
[ ] CQ-03: Document or add UPDATE/DELETE policies on financial tables
π΅ LOW/INFO (Review After Launch)
[ ] PERF-01: Re-evaluate unused indexes after 30 days of data
[ ] PERF-02: Add index on playtest_responses.email
[ ] CQ-02: Add error monitoring/alerting on stripe-webhook 400s
Hey mate β the big one to smash first is CRIT-01 π¨ β students could pay and not get enrolled right now because the courses table doesn't exist. That needs fixing before you go live. Want me to write the migration SQL to create it and wire it all up properly? π