askrobots / postblob Public

An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.

0 downloads

postblob/postblob_backup.sql
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/postblob.git
HTTPS
https://gitstable.com/git/askrobots/postblob.git
SSH
ssh://git@gitstable.com:2222/askrobots/postblob.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 docs: Add screenshots to README ae44e69 1 year, 1 month ago History
postblob_backup.sql · 105.9 KB · 2355 lines
1--
2-- PostgreSQL database dump
3--
4
5\restrict ZHowxS4FzILvodLbuq1kLjCcpmcpSQ5reyQdJ8KCxiHbPVSQVdN99Uni14IBD26
6
7-- Dumped from database version 14.19 (Homebrew)
8-- Dumped by pg_dump version 14.19 (Homebrew)
9
10SET statement_timeout = 0;
11SET lock_timeout = 0;
12SET idle_in_transaction_session_timeout = 0;
13SET client_encoding = 'UTF8';
14SET standard_conforming_strings = on;
15SELECT pg_catalog.set_config('search_path', '', false);
16SET check_function_bodies = false;
17SET xmloption = content;
18SET client_min_messages = warning;
19SET row_security = off;
20
21--
22-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
23--
24
25CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
26
27
28--
29-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner:
30--
31
32COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';
33
34
35--
36-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
37--
38
39CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
40
41
42--
43-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
44--
45
46COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
47
48
49--
50-- Name: ask(text); Type: FUNCTION; Schema: public; Owner: danq
51--
52
53CREATE FUNCTION public.ask(question text) RETURNS jsonb
54 LANGUAGE plpgsql
55 AS $_$
56DECLARE
57 sql_query text;
58 result jsonb;
59 word_count integer;
60BEGIN
61 -- Normalize input
62 question := lower(trim(question));
63
64 -- Simple pattern matching for common queries
65 sql_query := CASE
66 -- "show blobs" or "list blobs"
67 WHEN question ~ '^(show|list)\s+blobs?$' THEN
68 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 10'
69
70 -- "count blobs"
71 WHEN question ~ '^count\s+blobs?$' THEN
72 'SELECT COUNT(*) as count FROM blobs'
73
74 -- "show jobs" or "list jobs"
75 WHEN question ~ '^(show|list)\s+jobs?$' THEN
76 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10'
77
78 -- "pending jobs"
79 WHEN question ~ '^pending\s+jobs?$' THEN
80 'SELECT * FROM jobs WHERE status = ''pending'''
81
82 -- "failed jobs"
83 WHEN question ~ '^failed\s+jobs?$' THEN
84 'SELECT * FROM jobs WHERE status = ''failed'''
85
86 -- "completed jobs"
87 WHEN question ~ '^completed\s+jobs?$' THEN
88 'SELECT * FROM jobs WHERE status = ''completed'''
89
90 -- "show stats" or "statistics"
91 WHEN question ~ '^(show\s+)?stats|statistics$' THEN
92 'SELECT
93 (SELECT COUNT(*) FROM blobs) as blob_count,
94 (SELECT COUNT(*) FROM jobs) as job_count,
95 (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) as active_sessions'
96
97 -- "biggest blob" or "largest file"
98 WHEN question ~ '^(biggest|largest)\s+(blob|file)$' THEN
99 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1'
100
101 -- "newest blob" or "latest file"
102 WHEN question ~ '^(newest|latest)\s+(blob|file)$' THEN
103 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 1'
104
105 -- "show 5 blobs" (with number)
106 WHEN question ~ '^show\s+(\d+)\s+blobs?$' THEN
107 format('SELECT * FROM blobs ORDER BY created_at DESC LIMIT %s',
108 (regexp_match(question, 'show\s+(\d+)', 'i'))[1])
109
110 ELSE
111 NULL
112 END;
113
114 IF sql_query IS NULL THEN
115 RETURN jsonb_build_object(
116 'error', 'Could not understand query',
117 'original', question,
118 'hint', 'Try: "show blobs", "count jobs", "failed jobs", "show stats"'
119 );
120 END IF;
121
122 -- Execute the query
123 BEGIN
124 EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result;
125
126 IF result IS NULL THEN
127 result := '[]'::jsonb;
128 END IF;
129
130 RETURN jsonb_build_object(
131 'success', true,
132 'sql', sql_query,
133 'data', result
134 );
135 EXCEPTION WHEN OTHERS THEN
136 RETURN jsonb_build_object(
137 'error', SQLERRM,
138 'sql', sql_query,
139 'original', question
140 );
141 END;
142END;
143$_$;
144
145
146ALTER FUNCTION public.ask(question text) OWNER TO danq;
147
148--
149-- Name: base64_decode(text); Type: FUNCTION; Schema: public; Owner: danq
150--
151
152CREATE FUNCTION public.base64_decode(input text) RETURNS text
153 LANGUAGE plpgsql IMMUTABLE
154 AS $$
155BEGIN
156 RETURN convert_from(decode(input, 'base64'), 'UTF8');
157END;
158$$;
159
160
161ALTER FUNCTION public.base64_decode(input text) OWNER TO danq;
162
163--
164-- Name: base64_encode(text); Type: FUNCTION; Schema: public; Owner: danq
165--
166
167CREATE FUNCTION public.base64_encode(input text) RETURNS text
168 LANGUAGE plpgsql IMMUTABLE
169 AS $$
170BEGIN
171 RETURN encode(input::bytea, 'base64');
172END;
173$$;
174
175
176ALTER FUNCTION public.base64_encode(input text) OWNER TO danq;
177
178--
179-- Name: build_query_string(jsonb); Type: FUNCTION; Schema: public; Owner: danq
180--
181
182CREATE FUNCTION public.build_query_string(params jsonb) RETURNS text
183 LANGUAGE plpgsql IMMUTABLE
184 AS $$
185DECLARE
186 result text := '';
187 key text;
188 value text;
189 first boolean := true;
190BEGIN
191 IF params IS NULL THEN
192 RETURN '';
193 END IF;
194
195 FOR key, value IN SELECT * FROM jsonb_each_text(params) LOOP
196 IF NOT first THEN
197 result := result || '&';
198 END IF;
199 result := result || url_encode(key) || '=' || url_encode(value);
200 first := false;
201 END LOOP;
202
203 RETURN result;
204END;
205$$;
206
207
208ALTER FUNCTION public.build_query_string(params jsonb) OWNER TO danq;
209
210--
211-- Name: cache_get(text); Type: FUNCTION; Schema: public; Owner: danq
212--
213
214CREATE FUNCTION public.cache_get(p_key text) RETURNS jsonb
215 LANGUAGE plpgsql
216 AS $$
217BEGIN
218 -- Clean expired entries
219 DELETE FROM cache WHERE expires_at < now();
220
221 RETURN (SELECT value FROM cache WHERE key = p_key);
222END;
223$$;
224
225
226ALTER FUNCTION public.cache_get(p_key text) OWNER TO danq;
227
228--
229-- Name: cache_set(text, jsonb, integer); Type: FUNCTION; Schema: public; Owner: danq
230--
231
232CREATE FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer DEFAULT 3600) RETURNS void
233 LANGUAGE plpgsql
234 AS $$
235BEGIN
236 INSERT INTO cache (key, value, expires_at)
237 VALUES (p_key, p_value, now() + (p_ttl_seconds || ' seconds')::interval)
238 ON CONFLICT (key) DO UPDATE
239 SET value = EXCLUDED.value,
240 expires_at = EXCLUDED.expires_at,
241 created_at = now();
242END;
243$$;
244
245
246ALTER FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer) OWNER TO danq;
247
248--
249-- Name: code_crud(text, text, text, jsonb); Type: FUNCTION; Schema: public; Owner: danq
250--
251
252CREATE FUNCTION public.code_crud(p_method text, p_path text, p_content text DEFAULT NULL::text, p_metadata jsonb DEFAULT NULL::jsonb) RETURNS jsonb
253 LANGUAGE plpgsql
254 AS $$
255DECLARE
256 v_result jsonb;
257 v_file_id integer;
258 v_old_content text;
259 v_version integer;
260BEGIN
261 -- Debug log
262 RAISE NOTICE 'code_crud called with method=%, path=%', p_method, p_path;
263
264 CASE p_method
265 WHEN 'GET' THEN
266 -- Get code file
267 SELECT jsonb_build_object(
268 'id', id,
269 'path', path,
270 'content', content,
271 'language', language,
272 'version', version,
273 'metadata', metadata,
274 'updated_at', updated_at
275 ) INTO v_result
276 FROM code_files
277 WHERE path = p_path;
278
279 IF v_result IS NULL THEN
280 RAISE NOTICE 'File not found: %', p_path;
281 v_result := jsonb_build_object('error', 'File not found', 'requested_path', p_path);
282 END IF;
283
284 WHEN 'LIST' THEN
285 -- List all code files
286 SELECT jsonb_agg(
287 jsonb_build_object(
288 'path', path,
289 'language', language,
290 'version', version,
291 'size', length(content),
292 'updated_at', updated_at
293 ) ORDER BY path
294 ) INTO v_result
295 FROM code_files
296 WHERE is_active = true;
297
298 WHEN 'PUT', 'POST' THEN
299 -- Update or create code file
300 SELECT id, content, version
301 INTO v_file_id, v_old_content, v_version
302 FROM code_files
303 WHERE path = p_path;
304
305 IF v_file_id IS NOT NULL THEN
306 -- Save to history
307 INSERT INTO code_history (file_id, content, version)
308 VALUES (v_file_id, v_old_content, v_version);
309
310 -- Update file
311 UPDATE code_files
312 SET content = p_content,
313 version = version + 1,
314 metadata = COALESCE(p_metadata, metadata),
315 updated_at = now()
316 WHERE id = v_file_id
317 RETURNING jsonb_build_object(
318 'id', id,
319 'path', path,
320 'version', version,
321 'updated', true
322 ) INTO v_result;
323 ELSE
324 -- Create new file
325 INSERT INTO code_files (path, content, metadata)
326 VALUES (p_path, p_content, COALESCE(p_metadata, '{}'::jsonb))
327 RETURNING jsonb_build_object(
328 'id', id,
329 'path', path,
330 'version', version,
331 'created', true
332 ) INTO v_result;
333 END IF;
334
335 -- Auto hot-reload if it's a SQL function
336 IF p_path LIKE 'functions/%.sql' THEN
337 PERFORM hot_reload(p_path);
338 v_result := jsonb_set(v_result, '{hot_reloaded}', 'true'::jsonb);
339 END IF;
340
341 WHEN 'DELETE' THEN
342 -- Soft delete
343 UPDATE code_files
344 SET is_active = false,
345 updated_at = now()
346 WHERE path = p_path
347 RETURNING jsonb_build_object(
348 'path', path,
349 'deleted', true
350 ) INTO v_result;
351
352 IF v_result IS NULL THEN
353 v_result := jsonb_build_object('error', 'File not found');
354 END IF;
355
356 WHEN 'HISTORY' THEN
357 -- Get version history
358 SELECT jsonb_agg(
359 jsonb_build_object(
360 'version', h.version,
361 'changed_at', h.changed_at,
362 'changed_by', h.changed_by,
363 'size', length(h.content)
364 ) ORDER BY h.version DESC
365 ) INTO v_result
366 FROM code_history h
367 JOIN code_files f ON h.file_id = f.id
368 WHERE f.path = p_path;
369
370 ELSE
371 v_result := jsonb_build_object('error', 'Invalid method');
372 END CASE;
373
374 RETURN COALESCE(v_result, jsonb_build_object('error', 'Unknown error'));
375END;
376$$;
377
378
379ALTER FUNCTION public.code_crud(p_method text, p_path text, p_content text, p_metadata jsonb) OWNER TO danq;
380
381--
382-- Name: code_routes(text, text, jsonb); Type: FUNCTION; Schema: public; Owner: danq
383--
384
385CREATE FUNCTION public.code_routes(p_method text, p_path text, p_body jsonb DEFAULT '{}'::jsonb) RETURNS jsonb
386 LANGUAGE plpgsql
387 AS $$
388DECLARE
389 v_result jsonb;
390 v_file_path text;
391 v_content text;
392 v_crud_result jsonb;
393BEGIN
394 -- Route: GET /code (Editor UI)
395 IF p_method = 'GET' AND p_path = '/code' THEN
396 SELECT content INTO v_content
397 FROM code_files
398 WHERE path = 'ui/code_editor.html';
399
400 RETURN jsonb_build_object(
401 'status', 200,
402 'headers', jsonb_build_object('Content-Type', 'text/html'),
403 'body', v_content,
404 'html', true
405 );
406
407 -- Route: GET /code/list
408 ELSIF p_method = 'GET' AND p_path = '/code/list' THEN
409 v_crud_result := code_crud('LIST', NULL);
410 RETURN jsonb_build_object(
411 'status', 200,
412 'body', v_crud_result
413 );
414
415 -- Route: GET /code/file?path=...
416 ELSIF p_method = 'GET' AND p_path LIKE '/code/file%' THEN
417 -- Extract path parameter
418 IF position('?path=' in p_path) > 0 THEN
419 v_file_path := substring(p_path from position('?path=' in p_path) + 6);
420 ELSE
421 v_file_path := NULL;
422 END IF;
423
424 -- URL decode
425 IF v_file_path IS NOT NULL THEN
426 v_file_path := replace(v_file_path, '%2F', '/');
427 v_file_path := replace(v_file_path, '%20', ' ');
428 v_file_path := replace(v_file_path, '+', ' ');
429
430 -- Remove any trailing parameters
431 IF position('&' in v_file_path) > 0 THEN
432 v_file_path := substring(v_file_path from 1 for position('&' in v_file_path) - 1);
433 END IF;
434 END IF;
435
436 -- Get the file
437 v_crud_result := code_crud('GET', v_file_path);
438
439 RETURN jsonb_build_object(
440 'status', 200,
441 'body', v_crud_result
442 );
443
444 -- Route: POST /code/save
445 ELSIF p_method = 'POST' AND p_path = '/code/save' THEN
446 v_crud_result := code_crud('PUT', p_body->>'path', p_body->>'content');
447 RETURN jsonb_build_object(
448 'status', 200,
449 'body', v_crud_result
450 );
451
452 -- Route: POST /code/execute
453 ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN
454 v_crud_result := execute_code(
455 COALESCE(p_body->>'language', 'sql'),
456 p_body->>'code',
457 p_body->>'save_as'
458 );
459 RETURN jsonb_build_object(
460 'status', 200,
461 'body', v_crud_result
462 );
463
464 -- Route: POST /code/reload
465 ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN
466 v_crud_result := hot_reload(p_body->>'path');
467 RETURN jsonb_build_object(
468 'status', 200,
469 'body', v_crud_result
470 );
471
472 ELSE
473 -- Return NULL to indicate route not handled
474 RETURN NULL;
475 END IF;
476END;
477$$;
478
479
480ALTER FUNCTION public.code_routes(p_method text, p_path text, p_body jsonb) OWNER TO danq;
481
482--
483-- Name: execute_code(text, text, text); Type: FUNCTION; Schema: public; Owner: danq
484--
485
486CREATE FUNCTION public.execute_code(p_language text, p_code text, p_save_as text DEFAULT NULL::text) RETURNS jsonb
487 LANGUAGE plpgsql
488 AS $_$
489DECLARE
490 v_result jsonb;
491 v_do_block text;
492BEGIN
493 -- Execute based on language
494 CASE p_language
495 WHEN 'sql' THEN
496 BEGIN
497 EXECUTE p_code;
498 v_result := jsonb_build_object(
499 'success', true,
500 'message', 'SQL executed successfully'
501 );
502
503 -- Save if requested
504 IF p_save_as IS NOT NULL THEN
505 PERFORM code_crud('PUT', p_save_as, p_code,
506 jsonb_build_object('language', 'sql', 'auto_saved', true));
507 v_result := jsonb_set(v_result, '{saved_as}', to_jsonb(p_save_as));
508 END IF;
509
510 EXCEPTION WHEN OTHERS THEN
511 v_result := jsonb_build_object(
512 'success', false,
513 'error', SQLERRM,
514 'detail', SQLSTATE
515 );
516 END;
517
518 WHEN 'plpgsql' THEN
519 BEGIN
520 -- For PL/pgSQL, wrap in DO block if needed
521 IF NOT (p_code ILIKE 'CREATE%FUNCTION%' OR p_code ILIKE 'DO%') THEN
522 v_do_block := 'DO $BLOCK$ BEGIN ' || p_code || ' END $BLOCK$;';
523 EXECUTE v_do_block;
524 ELSE
525 EXECUTE p_code;
526 END IF;
527
528 v_result := jsonb_build_object(
529 'success', true,
530 'message', 'PL/pgSQL executed successfully'
531 );
532
533 EXCEPTION WHEN OTHERS THEN
534 v_result := jsonb_build_object(
535 'success', false,
536 'error', SQLERRM
537 );
538 END;
539
540 ELSE
541 v_result := jsonb_build_object(
542 'error', 'Unsupported language: ' || p_language
543 );
544 END CASE;
545
546 RETURN v_result;
547END;
548$_$;
549
550
551ALTER FUNCTION public.execute_code(p_language text, p_code text, p_save_as text) OWNER TO danq;
552
553--
554-- Name: format_bytes(bigint); Type: FUNCTION; Schema: public; Owner: danq
555--
556
557CREATE FUNCTION public.format_bytes(bytes bigint) RETURNS text
558 LANGUAGE plpgsql IMMUTABLE
559 AS $$
560DECLARE
561 units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB'];
562 unit_index int := 1;
563 size numeric := bytes;
564BEGIN
565 WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP
566 size := size / 1024;
567 unit_index := unit_index + 1;
568 END LOOP;
569
570 IF unit_index = 1 THEN
571 RETURN bytes || ' B';
572 ELSE
573 RETURN round(size, 2) || ' ' || units[unit_index];
574 END IF;
575END;
576$$;
577
578
579ALTER FUNCTION public.format_bytes(bytes bigint) OWNER TO danq;
580
581--
582-- Name: get_file_extension(text); Type: FUNCTION; Schema: public; Owner: danq
583--
584
585CREATE FUNCTION public.get_file_extension(filename text) RETURNS text
586 LANGUAGE plpgsql IMMUTABLE
587 AS $$
588DECLARE
589 pos int;
590BEGIN
591 pos := position('.' IN reverse(filename));
592 IF pos > 0 THEN
593 RETURN lower(right(filename, pos - 1));
594 END IF;
595 RETURN '';
596END;
597$$;
598
599
600ALTER FUNCTION public.get_file_extension(filename text) OWNER TO danq;
601
602--
603-- Name: get_version(); Type: FUNCTION; Schema: public; Owner: danq
604--
605
606CREATE FUNCTION public.get_version() RETURNS text
607 LANGUAGE plpgsql
608 AS $$
609BEGIN
610 RETURN 'Version 2.0 - Hot Reloaded!';
611END;
612$$;
613
614
615ALTER FUNCTION public.get_version() OWNER TO danq;
616
617--
618-- Name: handle_request(text, text, jsonb, jsonb, uuid); Type: FUNCTION; Schema: public; Owner: danq
619--
620
621CREATE FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb DEFAULT '{}'::jsonb, p_body jsonb DEFAULT '{}'::jsonb, p_session_id uuid DEFAULT NULL::uuid) RETURNS jsonb
622 LANGUAGE plpgsql
623 AS $$
624DECLARE
625 v_response jsonb;
626 v_route record;
627 v_blob_id uuid;
628 v_job_id integer;
629 v_stats jsonb;
630 v_html text;
631 v_blobs jsonb;
632 v_jobs jsonb;
633BEGIN
634 -- Default response
635 v_response := jsonb_build_object(
636 'status', 404,
637 'body', jsonb_build_object('error', 'Not found')
638 );
639
640 -- Route: GET / (HTML Dashboard)
641 IF p_method = 'GET' AND p_path = '/' THEN
642 -- Get stats
643 SELECT jsonb_build_object(
644 'blob_count', (SELECT COUNT(*) FROM blobs),
645 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),
646 'jobs_pending', (SELECT COUNT(*) FROM jobs WHERE status = 'pending'),
647 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),
648 'jobs_failed', (SELECT COUNT(*) FROM jobs WHERE status = 'failed'),
649 'total_exceptions', (SELECT COUNT(*) FROM exceptions),
650 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())
651 ) INTO v_stats;
652
653 -- Get recent blobs
654 SELECT COALESCE(jsonb_agg(
655 jsonb_build_object(
656 'id', id,
657 'filename', filename,
658 'size', size,
659 'created_at', created_at
660 ) ORDER BY created_at DESC
661 ), '[]'::jsonb)
662 INTO v_blobs
663 FROM (SELECT * FROM blobs ORDER BY created_at DESC LIMIT 5) b;
664
665 -- Get recent jobs
666 SELECT COALESCE(jsonb_agg(
667 jsonb_build_object(
668 'id', id,
669 'type', type,
670 'status', status,
671 'created_at', created_at
672 ) ORDER BY created_at DESC
673 ), '[]'::jsonb)
674 INTO v_jobs
675 FROM (SELECT * FROM jobs ORDER BY created_at DESC LIMIT 5) j;
676
677 -- Build HTML
678 v_html := format('<!DOCTYPE html>
679<html>
680<head>
681 <title>PostBlob - PostgreSQL Does Everything!</title>
682 <meta charset="utf-8">
683 <meta name="viewport" content="width=device-width, initial-scale=1">
684 <style>
685 * { margin: 0; padding: 0; box-sizing: border-box; }
686 body {
687 font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
688 line-height: 1.6;
689 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
690 min-height: 100vh;
691 padding: 20px;
692 }
693 .container {
694 max-width: 1200px;
695 margin: 0 auto;
696 }
697 header {
698 background: white;
699 border-radius: 12px;
700 padding: 30px;
701 margin-bottom: 30px;
702 box-shadow: 0 10px 40px rgba(0,0,0,0.1);
703 }
704 h1 {
705 color: #333;
706 margin-bottom: 10px;
707 font-size: 2.5em;
708 }
709 .subtitle {
710 color: #666;
711 font-size: 1.2em;
712 }
713 .grid {
714 display: grid;
715 grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
716 gap: 20px;
717 margin-bottom: 30px;
718 }
719 .card {
720 background: white;
721 border-radius: 12px;
722 padding: 25px;
723 box-shadow: 0 10px 40px rgba(0,0,0,0.1);
724 }
725 .card h2 {
726 color: #333;
727 margin-bottom: 20px;
728 font-size: 1.5em;
729 border-bottom: 2px solid #667eea;
730 padding-bottom: 10px;
731 }
732 .stat-grid {
733 display: grid;
734 grid-template-columns: repeat(auto-fit, minmax(120px, 1fr));
735 gap: 15px;
736 margin-bottom: 20px;
737 }
738 .stat {
739 text-align: center;
740 padding: 15px;
741 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
742 border-radius: 8px;
743 color: white;
744 }
745 .stat-value {
746 font-size: 2em;
747 font-weight: bold;
748 }
749 .stat-label {
750 font-size: 0.9em;
751 opacity: 0.9;
752 }
753 .list-item {
754 padding: 12px;
755 margin: 8px 0;
756 background: #f8f9fa;
757 border-radius: 6px;
758 border-left: 4px solid #667eea;
759 }
760 .list-item strong {
761 color: #333;
762 }
763 .status {
764 display: inline-block;
765 padding: 2px 8px;
766 border-radius: 4px;
767 font-size: 0.85em;
768 font-weight: bold;
769 }
770 .status.pending { background: #ffc107; color: #333; }
771 .status.completed { background: #28a745; color: white; }
772 .status.failed { background: #dc3545; color: white; }
773 .status.processing { background: #17a2b8; color: white; }
774 .endpoint {
775 padding: 15px;
776 margin: 10px 0;
777 background: #f8f9fa;
778 border-radius: 6px;
779 border-left: 4px solid #764ba2;
780 }
781 .method {
782 display: inline-block;
783 padding: 4px 8px;
784 border-radius: 4px;
785 font-weight: bold;
786 font-size: 0.85em;
787 margin-right: 10px;
788 }
789 .method.GET { background: #28a745; color: white; }
790 .method.POST { background: #007bff; color: white; }
791 .method.DELETE { background: #dc3545; color: white; }
792 .path {
793 font-family: "Monaco", "Menlo", monospace;
794 color: #333;
795 font-weight: bold;
796 }
797 .description {
798 color: #666;
799 margin-top: 5px;
800 }
801 .query-box {
802 margin-top: 20px;
803 }
804 input[type="text"] {
805 width: 100%%;
806 padding: 12px;
807 border: 2px solid #ddd;
808 border-radius: 6px;
809 font-size: 1em;
810 margin-bottom: 10px;
811 }
812 input[type="text"]:focus {
813 outline: none;
814 border-color: #667eea;
815 }
816 button {
817 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
818 color: white;
819 border: none;
820 padding: 12px 24px;
821 border-radius: 6px;
822 font-size: 1em;
823 cursor: pointer;
824 transition: transform 0.2s;
825 }
826 button:hover {
827 transform: translateY(-2px);
828 }
829 #queryResult {
830 margin-top: 15px;
831 padding: 15px;
832 background: #f8f9fa;
833 border-radius: 6px;
834 font-family: monospace;
835 white-space: pre-wrap;
836 display: none;
837 }
838 .footer {
839 text-align: center;
840 color: white;
841 margin-top: 40px;
842 }
843 .footer a {
844 color: white;
845 text-decoration: none;
846 font-weight: bold;
847 }
848 </style>
849</head>
850<body>
851 <div class="container">
852 <header>
853 <h1>🎯 PostBlob</h1>
854 <p class="subtitle">PostgreSQL Does Everything - Web UI</p>
855 </header>
856
857 <div class="grid">
858 <div class="card">
859 <h2>📊 Statistics</h2>
860 <div class="stat-grid">
861 <div class="stat">
862 <div class="stat-value">%s</div>
863 <div class="stat-label">Blobs</div>
864 </div>
865 <div class="stat">
866 <div class="stat-value">%s</div>
867 <div class="stat-label">Pending Jobs</div>
868 </div>
869 <div class="stat">
870 <div class="stat-value">%s</div>
871 <div class="stat-label">Completed</div>
872 </div>
873 <div class="stat">
874 <div class="stat-value">%s</div>
875 <div class="stat-label">Sessions</div>
876 </div>
877 </div>
878 <div class="list-item">
879 <strong>Total Storage:</strong> %s bytes
880 </div>
881 <div class="list-item">
882 <strong>Exceptions:</strong> %s
883 </div>
884 </div>
885
886 <div class="card">
887 <h2>💬 Natural Language Query</h2>
888 <div class="query-box">
889 <input type="text" id="queryInput" placeholder="Try: show blobs, failed jobs, show stats" />
890 <button onclick="runQuery()">Ask Database</button>
891 <div id="queryResult"></div>
892 </div>
893 <script>
894 async function runQuery() {
895 const input = document.getElementById("queryInput").value;
896 const result = document.getElementById("queryResult");
897
898 try {
899 const response = await fetch("/ask?q=" + encodeURIComponent(input));
900 const data = await response.json();
901 result.style.display = "block";
902 result.textContent = JSON.stringify(data, null, 2);
903 } catch (e) {
904 result.style.display = "block";
905 result.textContent = "Error: " + e.message;
906 }
907 }
908
909 document.getElementById("queryInput").addEventListener("keypress", (e) => {
910 if (e.key === "Enter") runQuery();
911 });
912 </script>
913 </div>
914 </div>
915
916 <div class="grid">
917 <div class="card">
918 <h2>📁 Recent Blobs</h2>
919 %s
920 <div style="margin-top: 20px;">
921 <a href="/blobs" style="color: #667eea;">View All Blobs →</a>
922 </div>
923 </div>
924
925 <div class="card">
926 <h2>⚡ Recent Jobs</h2>
927 %s
928 <div style="margin-top: 20px;">
929 <a href="/jobs" style="color: #667eea;">View All Jobs →</a>
930 </div>
931 </div>
932 </div>
933
934 <div class="card">
935 <h2>🔥 Hot Deploy Code Editor</h2>
936 <div class="endpoint">
937 <span class="method GET">GET</span>
938 <span class="path">/code</span>
939 <div class="description">Live code editor - Edit functions while the server is running!</div>
940 </div>
941 <div style="margin-top: 20px;">
942 <a href="/code" style="display: inline-block; background: linear-gradient(135deg, #ff6b6b 0%%, #feca57 100%%); color: white; padding: 12px 24px; border-radius: 6px; text-decoration: none; font-weight: bold; box-shadow: 0 4px 15px rgba(0,0,0,0.2); transition: transform 0.2s;">🔥 Open Code Editor</a>
943 </div>
944 </div>
945
946 <div class="card">
947 <h2>🚀 API Endpoints</h2>
948
949 <div class="endpoint">
950 <span class="method GET">GET</span>
951 <span class="path">/stats</span>
952 <div class="description">Get database statistics</div>
953 </div>
954
955 <div class="endpoint">
956 <span class="method GET">GET</span>
957 <span class="path">/blobs</span>
958 <div class="description">List all blobs</div>
959 </div>
960
961 <div class="endpoint">
962 <span class="method GET">GET</span>
963 <span class="path">/blob/{id}</span>
964 <div class="description">Get specific blob by ID</div>
965 </div>
966
967 <div class="endpoint">
968 <span class="method POST">POST</span>
969 <span class="path">/blob</span>
970 <div class="description">Upload new blob (JSON: filename, data, mimetype)</div>
971 </div>
972
973 <div class="endpoint">
974 <span class="method DELETE">DELETE</span>
975 <span class="path">/blob/{id}</span>
976 <div class="description">Delete a blob</div>
977 </div>
978
979 <div class="endpoint">
980 <span class="method GET">GET</span>
981 <span class="path">/jobs</span>
982 <div class="description">List all jobs</div>
983 </div>
984
985 <div class="endpoint">
986 <span class="method POST">POST</span>
987 <span class="path">/job</span>
988 <div class="description">Create new job (JSON: type, payload)</div>
989 </div>
990
991 <div class="endpoint">
992 <span class="method GET">GET</span>
993 <span class="path">/ask?q={query}</span>
994 <div class="description">Natural language database query</div>
995 </div>
996 </div>
997
998 <div class="footer">
999 <p>Powered by PostgreSQL • <a href="https://github.com/yourname/postblob">GitHub</a></p>
1000 </div>
1001 </div>
1002</body>
1003</html>',
1004 v_stats->>'blob_count',
1005 v_stats->>'jobs_pending',
1006 v_stats->>'jobs_completed',
1007 v_stats->>'active_sessions',
1008 v_stats->>'total_size',
1009 v_stats->>'total_exceptions',
1010 CASE
1011 WHEN jsonb_array_length(v_blobs) = 0 THEN '<p style="color: #999;">No blobs yet</p>'
1012 ELSE (
1013 SELECT string_agg(
1014 format('<div class="list-item"><strong>%s</strong><br>Size: %s bytes<br>%s</div>',
1015 b->>'filename',
1016 b->>'size',
1017 to_char((b->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')
1018 ), ''
1019 )
1020 FROM jsonb_array_elements(v_blobs) b
1021 )
1022 END,
1023 CASE
1024 WHEN jsonb_array_length(v_jobs) = 0 THEN '<p style="color: #999;">No jobs yet</p>'
1025 ELSE (
1026 SELECT string_agg(
1027 format('<div class="list-item"><strong>%s</strong><br><span class="status %s">%s</span><br>%s</div>',
1028 j->>'type',
1029 j->>'status',
1030 j->>'status',
1031 to_char((j->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')
1032 ), ''
1033 )
1034 FROM jsonb_array_elements(v_jobs) j
1035 )
1036 END
1037 );
1038
1039 v_response := jsonb_build_object(
1040 'status', 200,
1041 'headers', jsonb_build_object('Content-Type', 'text/html'),
1042 'body', v_html,
1043 'html', true
1044 );
1045
1046 -- Route: GET /ask (Natural Language Query)
1047 ELSIF p_method = 'GET' AND p_path LIKE '/ask%' THEN
1048 DECLARE
1049 v_query text;
1050 BEGIN
1051 -- Extract query parameter (simple parsing)
1052 v_query := substring(p_path from 'q=([^&]+)');
1053 v_query := replace(v_query, '%20', ' ');
1054
1055 IF v_query IS NOT NULL THEN
1056 v_response := jsonb_build_object(
1057 'status', 200,
1058 'body', ask(v_query)
1059 );
1060 ELSE
1061 v_response := jsonb_build_object(
1062 'status', 400,
1063 'body', jsonb_build_object('error', 'Missing query parameter')
1064 );
1065 END IF;
1066 END;
1067
1068 -- Route: GET /stats
1069 ELSIF p_method = 'GET' AND p_path = '/stats' THEN
1070 SELECT jsonb_build_object(
1071 'blob_count', (SELECT COUNT(*) FROM blobs),
1072 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),
1073 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),
1074 'total_exceptions', (SELECT COUNT(*) FROM exceptions),
1075 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())
1076 ) INTO v_stats;
1077
1078 v_response := jsonb_build_object(
1079 'status', 200,
1080 'body', v_stats
1081 );
1082
1083 -- Route: GET /blobs
1084 ELSIF p_method = 'GET' AND p_path = '/blobs' THEN
1085 v_response := jsonb_build_object(
1086 'status', 200,
1087 'body', (
1088 SELECT COALESCE(jsonb_agg(
1089 jsonb_build_object(
1090 'id', id,
1091 'filename', filename,
1092 'size', size,
1093 'mimetype', mimetype,
1094 'created_at', created_at
1095 ) ORDER BY created_at DESC
1096 ), '[]'::jsonb)
1097 FROM blobs
1098 LIMIT 100
1099 )
1100 );
1101
1102 -- Route: GET /blob/{id}
1103 ELSIF p_method = 'GET' AND p_path LIKE '/blob/%' THEN
1104 v_blob_id := substring(p_path from '/blob/(.+)')::uuid;
1105
1106 SELECT jsonb_build_object(
1107 'id', id,
1108 'filename', filename,
1109 'size', size,
1110 'mimetype', mimetype,
1111 'metadata', metadata,
1112 'created_at', created_at
1113 ) INTO v_response
1114 FROM blobs
1115 WHERE id = v_blob_id;
1116
1117 IF v_response IS NOT NULL THEN
1118 v_response := jsonb_build_object(
1119 'status', 200,
1120 'body', v_response
1121 );
1122 ELSE
1123 v_response := jsonb_build_object(
1124 'status', 404,
1125 'body', jsonb_build_object('error', 'Blob not found')
1126 );
1127 END IF;
1128
1129 -- Route: POST /blob
1130 ELSIF p_method = 'POST' AND p_path = '/blob' THEN
1131 INSERT INTO blobs (filename, mimetype, data, size, metadata)
1132 VALUES (
1133 COALESCE(p_body->>'filename', 'unnamed'),
1134 COALESCE(p_body->>'mimetype', 'application/octet-stream'),
1135 decode(p_body->>'data', 'base64'),
1136 length(decode(p_body->>'data', 'base64')),
1137 COALESCE(p_body->'metadata', '{}'::jsonb)
1138 )
1139 RETURNING id INTO v_blob_id;
1140
1141 v_response := jsonb_build_object(
1142 'status', 201,
1143 'body', jsonb_build_object(
1144 'id', v_blob_id,
1145 'url', '/blob/' || v_blob_id
1146 )
1147 );
1148
1149 -- Route: DELETE /blob/{id}
1150 ELSIF p_method = 'DELETE' AND p_path LIKE '/blob/%' THEN
1151 v_blob_id := substring(p_path from '/blob/(.+)')::uuid;
1152
1153 DELETE FROM blobs WHERE id = v_blob_id;
1154
1155 IF FOUND THEN
1156 v_response := jsonb_build_object(
1157 'status', 200,
1158 'body', jsonb_build_object('deleted', v_blob_id)
1159 );
1160 ELSE
1161 v_response := jsonb_build_object(
1162 'status', 404,
1163 'body', jsonb_build_object('error', 'Blob not found')
1164 );
1165 END IF;
1166
1167 -- Route: GET /jobs
1168 ELSIF p_method = 'GET' AND p_path = '/jobs' THEN
1169 v_response := jsonb_build_object(
1170 'status', 200,
1171 'body', (
1172 SELECT COALESCE(jsonb_agg(
1173 jsonb_build_object(
1174 'id', id,
1175 'type', type,
1176 'status', status,
1177 'created_at', created_at,
1178 'completed_at', completed_at
1179 ) ORDER BY created_at DESC
1180 ), '[]'::jsonb)
1181 FROM jobs
1182 LIMIT 100
1183 )
1184 );
1185
1186 -- Route: POST /job
1187 ELSIF p_method = 'POST' AND p_path = '/job' THEN
1188 INSERT INTO jobs (type, payload)
1189 VALUES (
1190 COALESCE(p_body->>'type', 'default'),
1191 COALESCE(p_body->'payload', '{}'::jsonb)
1192 )
1193 RETURNING id INTO v_job_id;
1194
1195 -- Notify job worker
1196 PERFORM pg_notify('new_job', v_job_id::text);
1197
1198 v_response := jsonb_build_object(
1199 'status', 201,
1200 'body', jsonb_build_object(
1201 'job_id', v_job_id,
1202 'status', 'pending'
1203 )
1204 );
1205
1206 -- Route: GET /job/{id}
1207 ELSIF p_method = 'GET' AND p_path LIKE '/job/%' THEN
1208 v_job_id := substring(p_path from '/job/(\d+)')::integer;
1209
1210 SELECT jsonb_build_object(
1211 'id', id,
1212 'type', type,
1213 'status', status,
1214 'payload', payload,
1215 'result', result,
1216 'created_at', created_at,
1217 'completed_at', completed_at
1218 ) INTO v_response
1219 FROM jobs
1220 WHERE id = v_job_id;
1221
1222 IF v_response IS NOT NULL THEN
1223 v_response := jsonb_build_object(
1224 'status', 200,
1225 'body', v_response
1226 );
1227 ELSE
1228 v_response := jsonb_build_object(
1229 'status', 404,
1230 'body', jsonb_build_object('error', 'Job not found')
1231 );
1232 END IF;
1233 END IF;
1234
1235 RETURN v_response;
1236END;
1237$$;
1238
1239
1240ALTER FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb, p_body jsonb, p_session_id uuid) OWNER TO danq;
1241
1242--
1243-- Name: hot_reload(text); Type: FUNCTION; Schema: public; Owner: danq
1244--
1245
1246CREATE FUNCTION public.hot_reload(file_path text DEFAULT NULL::text) RETURNS jsonb
1247 LANGUAGE plpgsql
1248 AS $$
1249DECLARE
1250 v_code text;
1251 v_result jsonb;
1252 v_file record;
1253BEGIN
1254 v_result := jsonb_build_object('reloaded', jsonb_array());
1255
1256 -- Reload specific file or all active files
1257 FOR v_file IN
1258 SELECT * FROM code_files
1259 WHERE is_active = true
1260 AND (file_path IS NULL OR path = file_path)
1261 AND language = 'sql'
1262 ORDER BY path
1263 LOOP
1264 BEGIN
1265 -- Execute the SQL code
1266 EXECUTE v_file.content;
1267
1268 -- Update executed timestamp
1269 UPDATE code_files
1270 SET executed_at = now()
1271 WHERE id = v_file.id;
1272
1273 -- Add to result
1274 v_result := jsonb_set(
1275 v_result,
1276 '{reloaded, 999999}',
1277 jsonb_build_object(
1278 'path', v_file.path,
1279 'version', v_file.version,
1280 'success', true
1281 )
1282 );
1283
1284 EXCEPTION WHEN OTHERS THEN
1285 -- Log error but continue
1286 v_result := jsonb_set(
1287 v_result,
1288 '{errors, 999999}',
1289 jsonb_build_object(
1290 'path', v_file.path,
1291 'error', SQLERRM
1292 )
1293 );
1294 END;
1295 END LOOP;
1296
1297 RETURN v_result;
1298END;
1299$$;
1300
1301
1302ALTER FUNCTION public.hot_reload(file_path text) OWNER TO danq;
1303
1304--
1305-- Name: html_decode(text); Type: FUNCTION; Schema: public; Owner: danq
1306--
1307
1308CREATE FUNCTION public.html_decode(input text) RETURNS text
1309 LANGUAGE plpgsql IMMUTABLE
1310 AS $$
1311BEGIN
1312 IF input IS NULL THEN
1313 RETURN NULL;
1314 END IF;
1315
1316 -- Replace HTML entities back to characters
1317 RETURN replace(
1318 replace(
1319 replace(
1320 replace(
1321 replace(input, '&lt;', '<'),
1322 '&gt;', '>'),
1323 '&quot;', '"'),
1324 '&#39;', ''''),
1325 '&amp;', '&');
1326END;
1327$$;
1328
1329
1330ALTER FUNCTION public.html_decode(input text) OWNER TO danq;
1331
1332--
1333-- Name: html_encode(text); Type: FUNCTION; Schema: public; Owner: danq
1334--
1335
1336CREATE FUNCTION public.html_encode(input text) RETURNS text
1337 LANGUAGE plpgsql IMMUTABLE
1338 AS $$
1339BEGIN
1340 IF input IS NULL THEN
1341 RETURN NULL;
1342 END IF;
1343
1344 -- Replace HTML special characters
1345 RETURN replace(
1346 replace(
1347 replace(
1348 replace(
1349 replace(input, '&', '&amp;'),
1350 '<', '&lt;'),
1351 '>', '&gt;'),
1352 '"', '&quot;'),
1353 '''', '&#39;');
1354END;
1355$$;
1356
1357
1358ALTER FUNCTION public.html_encode(input text) OWNER TO danq;
1359
1360--
1361-- Name: increment_version(); Type: FUNCTION; Schema: public; Owner: danq
1362--
1363
1364CREATE FUNCTION public.increment_version() RETURNS jsonb
1365 LANGUAGE plpgsql
1366 AS $_$
1367DECLARE
1368 v_current_version text;
1369 v_new_version text;
1370 v_new_code text;
1371 v_version_num numeric;
1372BEGIN
1373 -- Get current version
1374 v_current_version := get_version();
1375
1376 -- Parse and increment
1377 v_version_num := substring(v_current_version from '\d+\.\d+')::numeric + 0.1;
1378 v_new_version := 'Version ' || v_version_num::text;
1379
1380 -- Create new function code
1381 v_new_code := 'CREATE OR REPLACE FUNCTION get_version()
1382RETURNS text AS $FUNC$
1383BEGIN
1384 RETURN ' || quote_literal(v_new_version) || ';
1385END;
1386$FUNC$ LANGUAGE plpgsql;';
1387
1388 -- Update in database
1389 UPDATE code_files
1390 SET content = v_new_code,
1391 version = version + 1,
1392 updated_at = now()
1393 WHERE path = 'functions/self_modify.sql';
1394
1395 -- Hot reload
1396 EXECUTE v_new_code;
1397
1398 RETURN jsonb_build_object(
1399 'old_version', v_current_version,
1400 'new_version', v_new_version,
1401 'reloaded', true
1402 );
1403END;
1404$_$;
1405
1406
1407ALTER FUNCTION public.increment_version() OWNER TO danq;
1408
1409--
1410-- Name: is_valid_email(text); Type: FUNCTION; Schema: public; Owner: danq
1411--
1412
1413CREATE FUNCTION public.is_valid_email(email text) RETURNS boolean
1414 LANGUAGE plpgsql IMMUTABLE
1415 AS $_$
1416BEGIN
1417 RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
1418END;
1419$_$;
1420
1421
1422ALTER FUNCTION public.is_valid_email(email text) OWNER TO danq;
1423
1424--
1425-- Name: json_escape(text); Type: FUNCTION; Schema: public; Owner: danq
1426--
1427
1428CREATE FUNCTION public.json_escape(input text) RETURNS text
1429 LANGUAGE plpgsql IMMUTABLE
1430 AS $$
1431BEGIN
1432 IF input IS NULL THEN
1433 RETURN NULL;
1434 END IF;
1435
1436 -- Use PostgreSQL's built-in JSON escaping
1437 RETURN to_json(input)::text;
1438END;
1439$$;
1440
1441
1442ALTER FUNCTION public.json_escape(input text) OWNER TO danq;
1443
1444--
1445-- Name: notify_new_job(); Type: FUNCTION; Schema: public; Owner: danq
1446--
1447
1448CREATE FUNCTION public.notify_new_job() RETURNS trigger
1449 LANGUAGE plpgsql
1450 AS $$
1451BEGIN
1452 PERFORM pg_notify('new_job', NEW.id::text);
1453 RETURN NEW;
1454END;
1455$$;
1456
1457
1458ALTER FUNCTION public.notify_new_job() OWNER TO danq;
1459
1460--
1461-- Name: parse_query_string(text); Type: FUNCTION; Schema: public; Owner: danq
1462--
1463
1464CREATE FUNCTION public.parse_query_string(query_string text) RETURNS jsonb
1465 LANGUAGE plpgsql IMMUTABLE
1466 AS $$
1467DECLARE
1468 params jsonb := '{}'::jsonb;
1469 pair text;
1470 key text;
1471 value text;
1472 pos int;
1473BEGIN
1474 IF query_string IS NULL OR query_string = '' THEN
1475 RETURN params;
1476 END IF;
1477
1478 -- Remove leading ? if present
1479 IF left(query_string, 1) = '?' THEN
1480 query_string := substring(query_string, 2);
1481 END IF;
1482
1483 -- Split by & and process each pair
1484 FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP
1485 pos := position('=' IN pair);
1486 IF pos > 0 THEN
1487 key := url_decode(substring(pair, 1, pos - 1));
1488 value := url_decode(substring(pair, pos + 1));
1489 ELSE
1490 key := url_decode(pair);
1491 value := '';
1492 END IF;
1493
1494 -- Add to params
1495 params := params || jsonb_build_object(key, value);
1496 END LOOP;
1497
1498 RETURN params;
1499END;
1500$$;
1501
1502
1503ALTER FUNCTION public.parse_query_string(query_string text) OWNER TO danq;
1504
1505--
1506-- Name: process_job(integer); Type: FUNCTION; Schema: public; Owner: danq
1507--
1508
1509CREATE FUNCTION public.process_job(p_job_id integer) RETURNS void
1510 LANGUAGE plpgsql
1511 AS $$
1512DECLARE
1513 v_job record;
1514 v_result jsonb;
1515BEGIN
1516 -- Get job details
1517 SELECT * INTO v_job FROM jobs WHERE id = p_job_id FOR UPDATE;
1518
1519 IF NOT FOUND OR v_job.status != 'pending' THEN
1520 RETURN;
1521 END IF;
1522
1523 -- Mark as processing
1524 UPDATE jobs
1525 SET status = 'processing', started_at = now()
1526 WHERE id = p_job_id;
1527
1528 -- Process different job types
1529 BEGIN
1530 CASE v_job.type
1531 WHEN 'cleanup' THEN
1532 -- Clean old sessions and cache
1533 DELETE FROM sessions WHERE expires_at < now();
1534 DELETE FROM cache WHERE expires_at < now();
1535 v_result := jsonb_build_object(
1536 'status', 'success',
1537 'message', 'Cleanup completed'
1538 );
1539
1540 WHEN 'compute' THEN
1541 -- Simple computation
1542 v_result := jsonb_build_object(
1543 'result', (COALESCE((v_job.payload->>'value')::integer, 0) * 2)
1544 );
1545
1546 WHEN 'analyze_blobs' THEN
1547 -- Analyze blob storage
1548 v_result := jsonb_build_object(
1549 'total_blobs', (SELECT COUNT(*) FROM blobs),
1550 'total_size', (SELECT COALESCE(SUM(size), 0) FROM blobs),
1551 'avg_size', (SELECT COALESCE(AVG(size), 0) FROM blobs)
1552 );
1553
1554 ELSE
1555 -- Unknown job type
1556 v_result := jsonb_build_object(
1557 'error', format('Unknown job type: %s', v_job.type)
1558 );
1559 END CASE;
1560
1561 -- Mark as completed
1562 UPDATE jobs
1563 SET status = 'completed',
1564 result = v_result,
1565 completed_at = now()
1566 WHERE id = p_job_id;
1567
1568 EXCEPTION WHEN OTHERS THEN
1569 -- Mark as failed
1570 UPDATE jobs
1571 SET status = 'failed',
1572 result = jsonb_build_object(
1573 'error', SQLERRM,
1574 'detail', SQLSTATE
1575 ),
1576 attempts = attempts + 1
1577 WHERE id = p_job_id;
1578
1579 -- Log exception
1580 INSERT INTO exceptions (error_type, message, context, function_name)
1581 VALUES (
1582 SQLSTATE,
1583 SQLERRM,
1584 jsonb_build_object('job_id', p_job_id, 'job_type', v_job.type),
1585 'process_job'
1586 );
1587 END;
1588END;
1589$$;
1590
1591
1592ALTER FUNCTION public.process_job(p_job_id integer) OWNER TO danq;
1593
1594--
1595-- Name: random_string(integer); Type: FUNCTION; Schema: public; Owner: danq
1596--
1597
1598CREATE FUNCTION public.random_string(len integer) RETURNS text
1599 LANGUAGE plpgsql
1600 AS $$
1601DECLARE
1602 chars text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
1603 result text := '';
1604 i int;
1605BEGIN
1606 FOR i IN 1..len LOOP
1607 result := result || substring(chars, floor(random() * length(chars) + 1)::int, 1);
1608 END LOOP;
1609
1610 RETURN result;
1611END;
1612$$;
1613
1614
1615ALTER FUNCTION public.random_string(len integer) OWNER TO danq;
1616
1617--
1618-- Name: slugify(text); Type: FUNCTION; Schema: public; Owner: danq
1619--
1620
1621CREATE FUNCTION public.slugify(input text) RETURNS text
1622 LANGUAGE plpgsql IMMUTABLE
1623 AS $$
1624BEGIN
1625 IF input IS NULL THEN
1626 RETURN NULL;
1627 END IF;
1628
1629 RETURN lower(
1630 regexp_replace(
1631 regexp_replace(
1632 regexp_replace(
1633 input,
1634 '[^a-zA-Z0-9\s-]', '', 'g' -- Remove special chars
1635 ),
1636 '\s+', '-', 'g' -- Replace spaces with hyphens
1637 ),
1638 '-+', '-', 'g' -- Replace multiple hyphens with single
1639 )
1640 );
1641END;
1642$$;
1643
1644
1645ALTER FUNCTION public.slugify(input text) OWNER TO danq;
1646
1647--
1648-- Name: strip_tags(text); Type: FUNCTION; Schema: public; Owner: danq
1649--
1650
1651CREATE FUNCTION public.strip_tags(input text) RETURNS text
1652 LANGUAGE plpgsql IMMUTABLE
1653 AS $$
1654BEGIN
1655 IF input IS NULL THEN
1656 RETURN NULL;
1657 END IF;
1658
1659 -- Remove all HTML tags
1660 RETURN regexp_replace(input, '<[^>]+>', '', 'g');
1661END;
1662$$;
1663
1664
1665ALTER FUNCTION public.strip_tags(input text) OWNER TO danq;
1666
1667--
1668-- Name: truncate_text(text, integer, text); Type: FUNCTION; Schema: public; Owner: danq
1669--
1670
1671CREATE FUNCTION public.truncate_text(input text, max_length integer, ellipsis text DEFAULT '...'::text) RETURNS text
1672 LANGUAGE plpgsql IMMUTABLE
1673 AS $$
1674BEGIN
1675 IF input IS NULL OR length(input) <= max_length THEN
1676 RETURN input;
1677 END IF;
1678
1679 RETURN left(input, max_length - length(ellipsis)) || ellipsis;
1680END;
1681$$;
1682
1683
1684ALTER FUNCTION public.truncate_text(input text, max_length integer, ellipsis text) OWNER TO danq;
1685
1686--
1687-- Name: url_decode(text); Type: FUNCTION; Schema: public; Owner: danq
1688--
1689
1690CREATE FUNCTION public.url_decode(input text) RETURNS text
1691 LANGUAGE plpgsql IMMUTABLE
1692 AS $_$
1693DECLARE
1694 result text := '';
1695 i int := 1;
1696 len int;
1697 hex_str text;
1698BEGIN
1699 IF input IS NULL THEN
1700 RETURN NULL;
1701 END IF;
1702
1703 len := length(input);
1704
1705 WHILE i <= len LOOP
1706 IF substring(input, i, 1) = '%' AND i + 2 <= len THEN
1707 -- Decode %HH
1708 hex_str := substring(input, i + 1, 2);
1709 IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN
1710 result := result || chr(('x' || hex_str)::bit(8)::int);
1711 i := i + 3;
1712 ELSE
1713 -- Invalid hex sequence, keep the %
1714 result := result || '%';
1715 i := i + 1;
1716 END IF;
1717 ELSIF substring(input, i, 1) = '+' THEN
1718 -- + becomes space
1719 result := result || ' ';
1720 i := i + 1;
1721 ELSE
1722 -- Regular character
1723 result := result || substring(input, i, 1);
1724 i := i + 1;
1725 END IF;
1726 END LOOP;
1727
1728 RETURN result;
1729END;
1730$_$;
1731
1732
1733ALTER FUNCTION public.url_decode(input text) OWNER TO danq;
1734
1735--
1736-- Name: url_encode(text); Type: FUNCTION; Schema: public; Owner: danq
1737--
1738
1739CREATE FUNCTION public.url_encode(input text) RETURNS text
1740 LANGUAGE plpgsql IMMUTABLE
1741 AS $$
1742DECLARE
1743 result text := '';
1744 char_code int;
1745 i int;
1746BEGIN
1747 IF input IS NULL THEN
1748 RETURN NULL;
1749 END IF;
1750
1751 -- Encode each character
1752 FOR i IN 1..length(input) LOOP
1753 char_code := ascii(substring(input, i, 1));
1754
1755 -- Check if character needs encoding
1756 IF (char_code >= 48 AND char_code <= 57) OR -- 0-9
1757 (char_code >= 65 AND char_code <= 90) OR -- A-Z
1758 (char_code >= 97 AND char_code <= 122) OR -- a-z
1759 char_code IN (45, 46, 95, 126) THEN -- - . _ ~
1760 -- Safe character, don't encode
1761 result := result || substring(input, i, 1);
1762 ELSIF char_code = 32 THEN
1763 -- Space becomes +
1764 result := result || '+';
1765 ELSE
1766 -- Encode as %HH
1767 result := result || '%' || upper(to_hex(char_code));
1768 END IF;
1769 END LOOP;
1770
1771 RETURN result;
1772END;
1773$$;
1774
1775
1776ALTER FUNCTION public.url_encode(input text) OWNER TO danq;
1777
1778SET default_tablespace = '';
1779
1780SET default_table_access_method = heap;
1781
1782--
1783-- Name: blobs; Type: TABLE; Schema: public; Owner: danq
1784--
1785
1786CREATE TABLE public.blobs (
1787 id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
1788 filename text NOT NULL,
1789 mimetype text NOT NULL,
1790 data bytea NOT NULL,
1791 size bigint NOT NULL,
1792 metadata jsonb DEFAULT '{}'::jsonb,
1793 created_at timestamp with time zone DEFAULT now(),
1794 updated_at timestamp with time zone DEFAULT now()
1795);
1796
1797
1798ALTER TABLE public.blobs OWNER TO danq;
1799
1800--
1801-- Name: cache; Type: TABLE; Schema: public; Owner: danq
1802--
1803
1804CREATE TABLE public.cache (
1805 key text NOT NULL,
1806 value jsonb NOT NULL,
1807 expires_at timestamp with time zone,
1808 created_at timestamp with time zone DEFAULT now()
1809);
1810
1811
1812ALTER TABLE public.cache OWNER TO danq;
1813
1814--
1815-- Name: circuit_breakers; Type: TABLE; Schema: public; Owner: danq
1816--
1817
1818CREATE TABLE public.circuit_breakers (
1819 function_name text NOT NULL,
1820 state text DEFAULT 'closed'::text,
1821 failure_count integer DEFAULT 0,
1822 last_failure timestamp with time zone,
1823 last_success timestamp with time zone,
1824 CONSTRAINT circuit_breakers_state_check CHECK ((state = ANY (ARRAY['closed'::text, 'open'::text, 'half_open'::text])))
1825);
1826
1827
1828ALTER TABLE public.circuit_breakers OWNER TO danq;
1829
1830--
1831-- Name: code_files; Type: TABLE; Schema: public; Owner: danq
1832--
1833
1834CREATE TABLE public.code_files (
1835 id integer NOT NULL,
1836 path text NOT NULL,
1837 content text NOT NULL,
1838 language text DEFAULT 'sql'::text,
1839 version integer DEFAULT 1,
1840 is_active boolean DEFAULT true,
1841 created_at timestamp with time zone DEFAULT now(),
1842 updated_at timestamp with time zone DEFAULT now(),
1843 executed_at timestamp with time zone,
1844 metadata jsonb DEFAULT '{}'::jsonb
1845);
1846
1847
1848ALTER TABLE public.code_files OWNER TO danq;
1849
1850--
1851-- Name: code_files_id_seq; Type: SEQUENCE; Schema: public; Owner: danq
1852--
1853
1854CREATE SEQUENCE public.code_files_id_seq
1855 AS integer
1856 START WITH 1
1857 INCREMENT BY 1
1858 NO MINVALUE
1859 NO MAXVALUE
1860 CACHE 1;
1861
1862
1863ALTER TABLE public.code_files_id_seq OWNER TO danq;
1864
1865--
1866-- Name: code_files_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq
1867--
1868
1869ALTER SEQUENCE public.code_files_id_seq OWNED BY public.code_files.id;
1870
1871
1872--
1873-- Name: code_history; Type: TABLE; Schema: public; Owner: danq
1874--
1875
1876CREATE TABLE public.code_history (
1877 id integer NOT NULL,
1878 file_id integer,
1879 content text NOT NULL,
1880 version integer NOT NULL,
1881 changed_at timestamp with time zone DEFAULT now(),
1882 changed_by text DEFAULT CURRENT_USER
1883);
1884
1885
1886ALTER TABLE public.code_history OWNER TO danq;
1887
1888--
1889-- Name: code_history_id_seq; Type: SEQUENCE; Schema: public; Owner: danq
1890--
1891
1892CREATE SEQUENCE public.code_history_id_seq
1893 AS integer
1894 START WITH 1
1895 INCREMENT BY 1
1896 NO MINVALUE
1897 NO MAXVALUE
1898 CACHE 1;
1899
1900
1901ALTER TABLE public.code_history_id_seq OWNER TO danq;
1902
1903--
1904-- Name: code_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq
1905--
1906
1907ALTER SEQUENCE public.code_history_id_seq OWNED BY public.code_history.id;
1908
1909
1910--
1911-- Name: exceptions; Type: TABLE; Schema: public; Owner: danq
1912--
1913
1914CREATE TABLE public.exceptions (
1915 id integer NOT NULL,
1916 error_type text NOT NULL,
1917 message text NOT NULL,
1918 traceback text,
1919 context jsonb DEFAULT '{}'::jsonb,
1920 function_name text,
1921 occurred_at timestamp with time zone DEFAULT now(),
1922 resolved boolean DEFAULT false
1923);
1924
1925
1926ALTER TABLE public.exceptions OWNER TO danq;
1927
1928--
1929-- Name: exceptions_id_seq; Type: SEQUENCE; Schema: public; Owner: danq
1930--
1931
1932CREATE SEQUENCE public.exceptions_id_seq
1933 AS integer
1934 START WITH 1
1935 INCREMENT BY 1
1936 NO MINVALUE
1937 NO MAXVALUE
1938 CACHE 1;
1939
1940
1941ALTER TABLE public.exceptions_id_seq OWNER TO danq;
1942
1943--
1944-- Name: exceptions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq
1945--
1946
1947ALTER SEQUENCE public.exceptions_id_seq OWNED BY public.exceptions.id;
1948
1949
1950--
1951-- Name: jobs; Type: TABLE; Schema: public; Owner: danq
1952--
1953
1954CREATE TABLE public.jobs (
1955 id integer NOT NULL,
1956 type text NOT NULL,
1957 payload jsonb DEFAULT '{}'::jsonb,
1958 status text DEFAULT 'pending'::text,
1959 result jsonb,
1960 attempts integer DEFAULT 0,
1961 created_at timestamp with time zone DEFAULT now(),
1962 started_at timestamp with time zone,
1963 completed_at timestamp with time zone,
1964 CONSTRAINT jobs_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'failed'::text])))
1965);
1966
1967
1968ALTER TABLE public.jobs OWNER TO danq;
1969
1970--
1971-- Name: jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: danq
1972--
1973
1974CREATE SEQUENCE public.jobs_id_seq
1975 AS integer
1976 START WITH 1
1977 INCREMENT BY 1
1978 NO MINVALUE
1979 NO MAXVALUE
1980 CACHE 1;
1981
1982
1983ALTER TABLE public.jobs_id_seq OWNER TO danq;
1984
1985--
1986-- Name: jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq
1987--
1988
1989ALTER SEQUENCE public.jobs_id_seq OWNED BY public.jobs.id;
1990
1991
1992--
1993-- Name: routes; Type: TABLE; Schema: public; Owner: danq
1994--
1995
1996CREATE TABLE public.routes (
1997 id integer NOT NULL,
1998 method text NOT NULL,
1999 path_pattern text NOT NULL,
2000 handler text NOT NULL,
2001 description text
2002);
2003
2004
2005ALTER TABLE public.routes OWNER TO danq;
2006
2007--
2008-- Name: routes_id_seq; Type: SEQUENCE; Schema: public; Owner: danq
2009--
2010
2011CREATE SEQUENCE public.routes_id_seq
2012 AS integer
2013 START WITH 1
2014 INCREMENT BY 1
2015 NO MINVALUE
2016 NO MAXVALUE
2017 CACHE 1;
2018
2019
2020ALTER TABLE public.routes_id_seq OWNER TO danq;
2021
2022--
2023-- Name: routes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq
2024--
2025
2026ALTER SEQUENCE public.routes_id_seq OWNED BY public.routes.id;
2027
2028
2029--
2030-- Name: sessions; Type: TABLE; Schema: public; Owner: danq
2031--
2032
2033CREATE TABLE public.sessions (
2034 id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
2035 data jsonb DEFAULT '{}'::jsonb,
2036 created_at timestamp with time zone DEFAULT now(),
2037 expires_at timestamp with time zone DEFAULT (now() + '24:00:00'::interval),
2038 last_access timestamp with time zone DEFAULT now()
2039);
2040
2041
2042ALTER TABLE public.sessions OWNER TO danq;
2043
2044--
2045-- Name: code_files id; Type: DEFAULT; Schema: public; Owner: danq
2046--
2047
2048ALTER TABLE ONLY public.code_files ALTER COLUMN id SET DEFAULT nextval('public.code_files_id_seq'::regclass);
2049
2050
2051--
2052-- Name: code_history id; Type: DEFAULT; Schema: public; Owner: danq
2053--
2054
2055ALTER TABLE ONLY public.code_history ALTER COLUMN id SET DEFAULT nextval('public.code_history_id_seq'::regclass);
2056
2057
2058--
2059-- Name: exceptions id; Type: DEFAULT; Schema: public; Owner: danq
2060--
2061
2062ALTER TABLE ONLY public.exceptions ALTER COLUMN id SET DEFAULT nextval('public.exceptions_id_seq'::regclass);
2063
2064
2065--
2066-- Name: jobs id; Type: DEFAULT; Schema: public; Owner: danq
2067--
2068
2069ALTER TABLE ONLY public.jobs ALTER COLUMN id SET DEFAULT nextval('public.jobs_id_seq'::regclass);
2070
2071
2072--
2073-- Name: routes id; Type: DEFAULT; Schema: public; Owner: danq
2074--
2075
2076ALTER TABLE ONLY public.routes ALTER COLUMN id SET DEFAULT nextval('public.routes_id_seq'::regclass);
2077
2078
2079--
2080-- Data for Name: blobs; Type: TABLE DATA; Schema: public; Owner: danq
2081--
2082
2083COPY public.blobs (id, filename, mimetype, data, size, metadata, created_at, updated_at) FROM stdin;
20847d92e779-4534-451c-9a6b-f4b2208fb60c welcome.txt text/plain \\x57656c636f6d6520746f20506f7374426c6f62202d20506f737467726553514c20706f776572732065766572797468696e6721 51 {"type": "system", "description": "Welcome message"} 2025-09-22 19:48:50.240894-05 2025-09-22 19:48:50.240894-05
208598d08b53-fe15-42db-95c5-02ccb877bf6e test.txt text/plain \\x48656c6c6f20576f726c6421 12 {} 2025-09-22 19:50:09.25042-05 2025-09-22 19:50:09.25042-05
2086\.
2087
2088
2089--
2090-- Data for Name: cache; Type: TABLE DATA; Schema: public; Owner: danq
2091--
2092
2093COPY public.cache (key, value, expires_at, created_at) FROM stdin;
2094\.
2095
2096
2097--
2098-- Data for Name: circuit_breakers; Type: TABLE DATA; Schema: public; Owner: danq
2099--
2100
2101COPY public.circuit_breakers (function_name, state, failure_count, last_failure, last_success) FROM stdin;
2102\.
2103
2104
2105--
2106-- Data for Name: code_files; Type: TABLE DATA; Schema: public; Owner: danq
2107--
2108
2109COPY public.code_files (id, path, content, language, version, is_active, created_at, updated_at, executed_at, metadata) FROM stdin;
21102 functions/ask.sql CREATE OR REPLACE FUNCTION public.ask(question text)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n sql_query text;\n result jsonb;\n word_count integer;\nBEGIN\n -- Normalize input\n question := lower(trim(question));\n\n -- Simple pattern matching for common queries\n sql_query := CASE\n -- "show blobs" or "list blobs"\n WHEN question ~ '^(show|list)\\s+blobs?$' THEN\n 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 10'\n\n -- "count blobs"\n WHEN question ~ '^count\\s+blobs?$' THEN\n 'SELECT COUNT(*) as count FROM blobs'\n\n -- "show jobs" or "list jobs"\n WHEN question ~ '^(show|list)\\s+jobs?$' THEN\n 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10'\n\n -- "pending jobs"\n WHEN question ~ '^pending\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''pending'''\n\n -- "failed jobs"\n WHEN question ~ '^failed\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''failed'''\n\n -- "completed jobs"\n WHEN question ~ '^completed\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''completed'''\n\n -- "show stats" or "statistics"\n WHEN question ~ '^(show\\s+)?stats|statistics$' THEN\n 'SELECT\n (SELECT COUNT(*) FROM blobs) as blob_count,\n (SELECT COUNT(*) FROM jobs) as job_count,\n (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) as active_sessions'\n\n -- "biggest blob" or "largest file"\n WHEN question ~ '^(biggest|largest)\\s+(blob|file)$' THEN\n 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1'\n\n -- "newest blob" or "latest file"\n WHEN question ~ '^(newest|latest)\\s+(blob|file)$' THEN\n 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 1'\n\n -- "show 5 blobs" (with number)\n WHEN question ~ '^show\\s+(\\d+)\\s+blobs?$' THEN\n format('SELECT * FROM blobs ORDER BY created_at DESC LIMIT %s',\n (regexp_match(question, 'show\\s+(\\d+)', 'i'))[1])\n\n ELSE\n NULL\n END;\n\n IF sql_query IS NULL THEN\n RETURN jsonb_build_object(\n 'error', 'Could not understand query',\n 'original', question,\n 'hint', 'Try: "show blobs", "count jobs", "failed jobs", "show stats"'\n );\n END IF;\n\n -- Execute the query\n BEGIN\n EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result;\n\n IF result IS NULL THEN\n result := '[]'::jsonb;\n END IF;\n\n RETURN jsonb_build_object(\n 'success', true,\n 'sql', sql_query,\n 'data', result\n );\n EXCEPTION WHEN OTHERS THEN\n RETURN jsonb_build_object(\n 'error', SQLERRM,\n 'sql', sql_query,\n 'original', question\n );\n END;\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true}
21113 functions/process_job.sql CREATE OR REPLACE FUNCTION public.process_job(p_job_id integer)\n RETURNS void\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n v_job record;\n v_result jsonb;\nBEGIN\n -- Get job details\n SELECT * INTO v_job FROM jobs WHERE id = p_job_id FOR UPDATE;\n\n IF NOT FOUND OR v_job.status != 'pending' THEN\n RETURN;\n END IF;\n\n -- Mark as processing\n UPDATE jobs\n SET status = 'processing', started_at = now()\n WHERE id = p_job_id;\n\n -- Process different job types\n BEGIN\n CASE v_job.type\n WHEN 'cleanup' THEN\n -- Clean old sessions and cache\n DELETE FROM sessions WHERE expires_at < now();\n DELETE FROM cache WHERE expires_at < now();\n v_result := jsonb_build_object(\n 'status', 'success',\n 'message', 'Cleanup completed'\n );\n\n WHEN 'compute' THEN\n -- Simple computation\n v_result := jsonb_build_object(\n 'result', (COALESCE((v_job.payload->>'value')::integer, 0) * 2)\n );\n\n WHEN 'analyze_blobs' THEN\n -- Analyze blob storage\n v_result := jsonb_build_object(\n 'total_blobs', (SELECT COUNT(*) FROM blobs),\n 'total_size', (SELECT COALESCE(SUM(size), 0) FROM blobs),\n 'avg_size', (SELECT COALESCE(AVG(size), 0) FROM blobs)\n );\n\n ELSE\n -- Unknown job type\n v_result := jsonb_build_object(\n 'error', format('Unknown job type: %s', v_job.type)\n );\n END CASE;\n\n -- Mark as completed\n UPDATE jobs\n SET status = 'completed',\n result = v_result,\n completed_at = now()\n WHERE id = p_job_id;\n\n EXCEPTION WHEN OTHERS THEN\n -- Mark as failed\n UPDATE jobs\n SET status = 'failed',\n result = jsonb_build_object(\n 'error', SQLERRM,\n 'detail', SQLSTATE\n ),\n attempts = attempts + 1\n WHERE id = p_job_id;\n\n -- Log exception\n INSERT INTO exceptions (error_type, message, context, function_name)\n VALUES (\n SQLSTATE,\n SQLERRM,\n jsonb_build_object('job_id', p_job_id, 'job_type', v_job.type),\n 'process_job'\n );\n END;\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true}
21124 functions/cache_get.sql CREATE OR REPLACE FUNCTION public.cache_get(p_key text)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nBEGIN\n -- Clean expired entries\n DELETE FROM cache WHERE expires_at < now();\n\n RETURN (SELECT value FROM cache WHERE key = p_key);\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true}
21135 functions/cache_set.sql CREATE OR REPLACE FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer DEFAULT 3600)\n RETURNS void\n LANGUAGE plpgsql\nAS $function$\nBEGIN\n INSERT INTO cache (key, value, expires_at)\n VALUES (p_key, p_value, now() + (p_ttl_seconds || ' seconds')::interval)\n ON CONFLICT (key) DO UPDATE\n SET value = EXCLUDED.value,\n expires_at = EXCLUDED.expires_at,\n created_at = now();\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true}
21141 functions/handle_request.sql CREATE OR REPLACE FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb DEFAULT '{}'::jsonb, p_body jsonb DEFAULT '{}'::jsonb, p_session_id uuid DEFAULT NULL::uuid)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n v_response jsonb;\n v_route record;\n v_blob_id uuid;\n v_job_id integer;\n v_stats jsonb;\n v_html text;\n v_blobs jsonb;\n v_jobs jsonb;\nBEGIN\n -- Default response\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Not found')\n );\n\n -- Route: GET / (HTML Dashboard)\n IF p_method = 'GET' AND p_path = '/' THEN\n -- Get stats\n SELECT jsonb_build_object(\n 'blob_count', (SELECT COUNT(*) FROM blobs),\n 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),\n 'jobs_pending', (SELECT COUNT(*) FROM jobs WHERE status = 'pending'),\n 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),\n 'jobs_failed', (SELECT COUNT(*) FROM jobs WHERE status = 'failed'),\n 'total_exceptions', (SELECT COUNT(*) FROM exceptions),\n 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())\n ) INTO v_stats;\n\n -- Get recent blobs\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n INTO v_blobs\n FROM (SELECT * FROM blobs ORDER BY created_at DESC LIMIT 5) b;\n\n -- Get recent jobs\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n INTO v_jobs\n FROM (SELECT * FROM jobs ORDER BY created_at DESC LIMIT 5) j;\n\n -- Build HTML\n v_html := format('<!DOCTYPE html>\n<html>\n<head>\n <title>PostBlob - PostgreSQL Does Everything!</title>\n <meta charset="utf-8">\n <meta name="viewport" content="width=device-width, initial-scale=1">\n <style>\n * { margin: 0; padding: 0; box-sizing: border-box; }\n body {\n font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;\n line-height: 1.6;\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n min-height: 100vh;\n padding: 20px;\n }\n .container {\n max-width: 1200px;\n margin: 0 auto;\n }\n header {\n background: white;\n border-radius: 12px;\n padding: 30px;\n margin-bottom: 30px;\n box-shadow: 0 10px 40px rgba(0,0,0,0.1);\n }\n h1 {\n color: #333;\n margin-bottom: 10px;\n font-size: 2.5em;\n }\n .subtitle {\n color: #666;\n font-size: 1.2em;\n }\n .grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));\n gap: 20px;\n margin-bottom: 30px;\n }\n .card {\n background: white;\n border-radius: 12px;\n padding: 25px;\n box-shadow: 0 10px 40px rgba(0,0,0,0.1);\n }\n .card h2 {\n color: #333;\n margin-bottom: 20px;\n font-size: 1.5em;\n border-bottom: 2px solid #667eea;\n padding-bottom: 10px;\n }\n .stat-grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(120px, 1fr));\n gap: 15px;\n margin-bottom: 20px;\n }\n .stat {\n text-align: center;\n padding: 15px;\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n border-radius: 8px;\n color: white;\n }\n .stat-value {\n font-size: 2em;\n font-weight: bold;\n }\n .stat-label {\n font-size: 0.9em;\n opacity: 0.9;\n }\n .list-item {\n padding: 12px;\n margin: 8px 0;\n background: #f8f9fa;\n border-radius: 6px;\n border-left: 4px solid #667eea;\n }\n .list-item strong {\n color: #333;\n }\n .status {\n display: inline-block;\n padding: 2px 8px;\n border-radius: 4px;\n font-size: 0.85em;\n font-weight: bold;\n }\n .status.pending { background: #ffc107; color: #333; }\n .status.completed { background: #28a745; color: white; }\n .status.failed { background: #dc3545; color: white; }\n .status.processing { background: #17a2b8; color: white; }\n .endpoint {\n padding: 15px;\n margin: 10px 0;\n background: #f8f9fa;\n border-radius: 6px;\n border-left: 4px solid #764ba2;\n }\n .method {\n display: inline-block;\n padding: 4px 8px;\n border-radius: 4px;\n font-weight: bold;\n font-size: 0.85em;\n margin-right: 10px;\n }\n .method.GET { background: #28a745; color: white; }\n .method.POST { background: #007bff; color: white; }\n .method.DELETE { background: #dc3545; color: white; }\n .path {\n font-family: "Monaco", "Menlo", monospace;\n color: #333;\n font-weight: bold;\n }\n .description {\n color: #666;\n margin-top: 5px;\n }\n .query-box {\n margin-top: 20px;\n }\n input[type="text"] {\n width: 100%%;\n padding: 12px;\n border: 2px solid #ddd;\n border-radius: 6px;\n font-size: 1em;\n margin-bottom: 10px;\n }\n input[type="text"]:focus {\n outline: none;\n border-color: #667eea;\n }\n button {\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n color: white;\n border: none;\n padding: 12px 24px;\n border-radius: 6px;\n font-size: 1em;\n cursor: pointer;\n transition: transform 0.2s;\n }\n button:hover {\n transform: translateY(-2px);\n }\n #queryResult {\n margin-top: 15px;\n padding: 15px;\n background: #f8f9fa;\n border-radius: 6px;\n font-family: monospace;\n white-space: pre-wrap;\n display: none;\n }\n .footer {\n text-align: center;\n color: white;\n margin-top: 40px;\n }\n .footer a {\n color: white;\n text-decoration: none;\n font-weight: bold;\n }\n </style>\n</head>\n<body>\n <div class="container">\n <header>\n <h1>🎯 PostBlob</h1>\n <p class="subtitle">PostgreSQL Does Everything - Web UI</p>\n </header>\n\n <div class="grid">\n <div class="card">\n <h2>📊 Statistics</h2>\n <div class="stat-grid">\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Blobs</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Pending Jobs</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Completed</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Sessions</div>\n </div>\n </div>\n <div class="list-item">\n <strong>Total Storage:</strong> %s bytes\n </div>\n <div class="list-item">\n <strong>Exceptions:</strong> %s\n </div>\n </div>\n\n <div class="card">\n <h2>💬 Natural Language Query</h2>\n <div class="query-box">\n <input type="text" id="queryInput" placeholder="Try: show blobs, failed jobs, show stats" />\n <button onclick="runQuery()">Ask Database</button>\n <div id="queryResult"></div>\n </div>\n <script>\n async function runQuery() {\n const input = document.getElementById("queryInput").value;\n const result = document.getElementById("queryResult");\n\n try {\n const response = await fetch("/ask?q=" + encodeURIComponent(input));\n const data = await response.json();\n result.style.display = "block";\n result.textContent = JSON.stringify(data, null, 2);\n } catch (e) {\n result.style.display = "block";\n result.textContent = "Error: " + e.message;\n }\n }\n\n document.getElementById("queryInput").addEventListener("keypress", (e) => {\n if (e.key === "Enter") runQuery();\n });\n </script>\n </div>\n </div>\n\n <div class="grid">\n <div class="card">\n <h2>📁 Recent Blobs</h2>\n %s\n <div style="margin-top: 20px;">\n <a href="/blobs" style="color: #667eea;">View All Blobs →</a>\n </div>\n </div>\n\n <div class="card">\n <h2>⚡ Recent Jobs</h2>\n %s\n <div style="margin-top: 20px;">\n <a href="/jobs" style="color: #667eea;">View All Jobs →</a>\n </div>\n </div>\n </div>\n\n <div class="card">\n <h2>🔥 Hot Deploy Code Editor</h2>\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/code</span>\n <div class="description">Live code editor - Edit functions while the server is running!</div>\n </div>\n <div style="margin-top: 20px;">\n <a href="/code" style="display: inline-block; background: linear-gradient(135deg, #ff6b6b 0%%, #feca57 100%%); color: white; padding: 12px 24px; border-radius: 6px; text-decoration: none; font-weight: bold; box-shadow: 0 4px 15px rgba(0,0,0,0.2); transition: transform 0.2s;">🔥 Open Code Editor</a>\n </div>\n </div>\n\n <div class="card">\n <h2>🚀 API Endpoints</h2>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/stats</span>\n <div class="description">Get database statistics</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/blobs</span>\n <div class="description">List all blobs</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/blob/{id}</span>\n <div class="description">Get specific blob by ID</div>\n </div>\n\n <div class="endpoint">\n <span class="method POST">POST</span>\n <span class="path">/blob</span>\n <div class="description">Upload new blob (JSON: filename, data, mimetype)</div>\n </div>\n\n <div class="endpoint">\n <span class="method DELETE">DELETE</span>\n <span class="path">/blob/{id}</span>\n <div class="description">Delete a blob</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/jobs</span>\n <div class="description">List all jobs</div>\n </div>\n\n <div class="endpoint">\n <span class="method POST">POST</span>\n <span class="path">/job</span>\n <div class="description">Create new job (JSON: type, payload)</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/ask?q={query}</span>\n <div class="description">Natural language database query</div>\n </div>\n </div>\n\n <div class="footer">\n <p>Powered by PostgreSQL • <a href="https://github.com/yourname/postblob">GitHub</a></p>\n </div>\n </div>\n</body>\n</html>',\n v_stats->>'blob_count',\n v_stats->>'jobs_pending',\n v_stats->>'jobs_completed',\n v_stats->>'active_sessions',\n v_stats->>'total_size',\n v_stats->>'total_exceptions',\n CASE\n WHEN jsonb_array_length(v_blobs) = 0 THEN '<p style="color: #999;">No blobs yet</p>'\n ELSE (\n SELECT string_agg(\n format('<div class="list-item"><strong>%s</strong><br>Size: %s bytes<br>%s</div>',\n b->>'filename',\n b->>'size',\n to_char((b->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')\n ), ''\n )\n FROM jsonb_array_elements(v_blobs) b\n )\n END,\n CASE\n WHEN jsonb_array_length(v_jobs) = 0 THEN '<p style="color: #999;">No jobs yet</p>'\n ELSE (\n SELECT string_agg(\n format('<div class="list-item"><strong>%s</strong><br><span class="status %s">%s</span><br>%s</div>',\n j->>'type',\n j->>'status',\n j->>'status',\n to_char((j->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')\n ), ''\n )\n FROM jsonb_array_elements(v_jobs) j\n )\n END\n );\n\n v_response := jsonb_build_object(\n 'status', 200,\n 'headers', jsonb_build_object('Content-Type', 'text/html'),\n 'body', v_html,\n 'html', true\n );\n\n -- Route: GET /ask (Natural Language Query)\n ELSIF p_method = 'GET' AND p_path LIKE '/ask%' THEN\n DECLARE\n v_query text;\n BEGIN\n -- Extract query parameter (simple parsing)\n v_query := substring(p_path from 'q=([^&]+)');\n v_query := replace(v_query, '%20', ' ');\n\n IF v_query IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', ask(v_query)\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 400,\n 'body', jsonb_build_object('error', 'Missing query parameter')\n );\n END IF;\n END;\n\n -- Route: GET /stats\n ELSIF p_method = 'GET' AND p_path = '/stats' THEN\n SELECT jsonb_build_object(\n 'blob_count', (SELECT COUNT(*) FROM blobs),\n 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),\n 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),\n 'total_exceptions', (SELECT COUNT(*) FROM exceptions),\n 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())\n ) INTO v_stats;\n\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_stats\n );\n\n -- Route: GET /blobs\n ELSIF p_method = 'GET' AND p_path = '/blobs' THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', (\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'mimetype', mimetype,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n FROM blobs\n LIMIT 100\n )\n );\n\n -- Route: GET /blob/{id}\n ELSIF p_method = 'GET' AND p_path LIKE '/blob/%' THEN\n v_blob_id := substring(p_path from '/blob/(.+)')::uuid;\n\n SELECT jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'mimetype', mimetype,\n 'metadata', metadata,\n 'created_at', created_at\n ) INTO v_response\n FROM blobs\n WHERE id = v_blob_id;\n\n IF v_response IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_response\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Blob not found')\n );\n END IF;\n\n -- Route: POST /blob\n ELSIF p_method = 'POST' AND p_path = '/blob' THEN\n INSERT INTO blobs (filename, mimetype, data, size, metadata)\n VALUES (\n COALESCE(p_body->>'filename', 'unnamed'),\n COALESCE(p_body->>'mimetype', 'application/octet-stream'),\n decode(p_body->>'data', 'base64'),\n length(decode(p_body->>'data', 'base64')),\n COALESCE(p_body->'metadata', '{}'::jsonb)\n )\n RETURNING id INTO v_blob_id;\n\n v_response := jsonb_build_object(\n 'status', 201,\n 'body', jsonb_build_object(\n 'id', v_blob_id,\n 'url', '/blob/' || v_blob_id\n )\n );\n\n -- Route: DELETE /blob/{id}\n ELSIF p_method = 'DELETE' AND p_path LIKE '/blob/%' THEN\n v_blob_id := substring(p_path from '/blob/(.+)')::uuid;\n\n DELETE FROM blobs WHERE id = v_blob_id;\n\n IF FOUND THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', jsonb_build_object('deleted', v_blob_id)\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Blob not found')\n );\n END IF;\n\n -- Route: GET /jobs\n ELSIF p_method = 'GET' AND p_path = '/jobs' THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', (\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'created_at', created_at,\n 'completed_at', completed_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n FROM jobs\n LIMIT 100\n )\n );\n\n -- Route: POST /job\n ELSIF p_method = 'POST' AND p_path = '/job' THEN\n INSERT INTO jobs (type, payload)\n VALUES (\n COALESCE(p_body->>'type', 'default'),\n COALESCE(p_body->'payload', '{}'::jsonb)\n )\n RETURNING id INTO v_job_id;\n\n -- Notify job worker\n PERFORM pg_notify('new_job', v_job_id::text);\n\n v_response := jsonb_build_object(\n 'status', 201,\n 'body', jsonb_build_object(\n 'job_id', v_job_id,\n 'status', 'pending'\n )\n );\n\n -- Route: GET /job/{id}\n ELSIF p_method = 'GET' AND p_path LIKE '/job/%' THEN\n v_job_id := substring(p_path from '/job/(\\d+)')::integer;\n\n SELECT jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'payload', payload,\n 'result', result,\n 'created_at', created_at,\n 'completed_at', completed_at\n ) INTO v_response\n FROM jobs\n WHERE id = v_job_id;\n\n IF v_response IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_response\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Job not found')\n );\n END IF;\n END IF;\n\n RETURN v_response;\nEND;\n$function$\n\n sql 5 t 2025-09-22 20:00:58.476063-05 2025-09-22 20:46:26.771355-05 \N {"type": "function", "hot_reload": true}
21156 ui/code_editor.html <!DOCTYPE html>\n<html>\n<head>\n <title>PostBlob Code Editor</title>\n <style>\n * { margin: 0; padding: 0; box-sizing: border-box; }\n body { font-family: monospace; background: #1e1e1e; color: #d4d4d4; }\n .container { display: flex; height: 100vh; }\n .sidebar { width: 250px; background: #252526; border-right: 1px solid #3e3e42; overflow-y: auto; }\n .editor-pane { flex: 1; display: flex; flex-direction: column; }\n .toolbar { background: #2d2d30; padding: 10px; border-bottom: 1px solid #3e3e42; }\n .editor { flex: 1; padding: 20px; overflow: auto; }\n textarea { width: 100%; height: 400px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42; padding: 10px; font-family: monospace; font-size: 14px; }\n .output { background: #1e1e1e; border-top: 1px solid #3e3e42; padding: 20px; height: 200px; overflow-y: auto; }\n .file-item { padding: 8px 15px; cursor: pointer; border-bottom: 1px solid #3e3e42; }\n .file-item:hover { background: #2d2d30; }\n .file-item.active { background: #094771; }\n button { background: #007acc; color: white; border: none; padding: 8px 16px; margin-right: 10px; cursor: pointer; }\n button:hover { background: #005a9e; }\n .status { padding: 5px 10px; background: #007acc; color: white; display: inline-block; }\n pre { white-space: pre-wrap; word-wrap: break-word; }\n </style>\n</head>\n<body>\n <div class="container">\n <div class="sidebar">\n <h3 style="padding: 15px; border-bottom: 1px solid #3e3e42;">Code Files</h3>\n <div id="fileList"></div>\n </div>\n <div class="editor-pane">\n <div class="toolbar">\n <button onclick="saveCode()">Save</button>\n <button onclick="executeCode()">Execute</button>\n <button onclick="hotReload()">Hot Reload</button>\n <button onclick="newFile()">New File</button>\n <span class="status" id="status">Ready</span>\n </div>\n <div class="editor">\n <input type="text" id="filePath" placeholder="File path..." style="width: 100%; padding: 5px; margin-bottom: 10px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42;">\n <textarea id="codeEditor" placeholder="-- Write your SQL or PL/pgSQL code here..."></textarea>\n </div>\n <div class="output">\n <h4 style="margin-bottom: 10px;">Output:</h4>\n <pre id="output"></pre>\n </div>\n </div>\n </div>\n\n <script>\n let currentFile = null;\n\n async function loadFiles() {\n try {\n const response = await fetch("/code/list");\n const files = await response.json();\n const fileList = document.getElementById("fileList");\n\n if (!files || files.error) {\n fileList.innerHTML = "<div>No files found</div>";\n return;\n }\n\n fileList.innerHTML = files.map(f => {\n return `<div class="file-item" data-path="${f.path}">${f.path}<br><small>${f.size} bytes | v${f.version}</small></div>`;\n }).join("");\n\n // Add click handlers\n document.querySelectorAll(".file-item").forEach(item => {\n item.addEventListener("click", function() {\n loadFile(this.getAttribute("data-path"), this);\n });\n });\n } catch (e) {\n console.error("Error loading files:", e);\n document.getElementById("output").textContent = "Error loading files: " + e.message;\n }\n }\n\n async function loadFile(path, element) {\n try {\n currentFile = path;\n document.getElementById("filePath").value = path;\n\n const response = await fetch(`/code/file?path=${encodeURIComponent(path)}`);\n const file = await response.json();\n\n if (file.error) {\n document.getElementById("output").textContent = "Error: " + file.error;\n return;\n }\n\n document.getElementById("codeEditor").value = file.content || "";\n setStatus(`Loaded ${path}`);\n\n // Update active state\n document.querySelectorAll(".file-item").forEach(el => el.classList.remove("active"));\n if (element) element.classList.add("active");\n\n } catch (e) {\n console.error("Error loading file:", e);\n document.getElementById("output").textContent = "Error loading file: " + e.message;\n }\n }\n\n async function saveCode() {\n try {\n const path = document.getElementById("filePath").value;\n const content = document.getElementById("codeEditor").value;\n\n if (!path) {\n alert("Please enter a file path");\n return;\n }\n\n const response = await fetch("/code/save", {\n method: "POST",\n headers: {"Content-Type": "application/json"},\n body: JSON.stringify({path, content})\n });\n\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus(result.hot_reloaded ? "Saved & Reloaded!" : "Saved!");\n loadFiles();\n } catch (e) {\n console.error("Error saving:", e);\n document.getElementById("output").textContent = "Error saving: " + e.message;\n }\n }\n\n async function executeCode() {\n try {\n const code = document.getElementById("codeEditor").value;\n\n if (!code) {\n alert("No code to execute");\n return;\n }\n\n const response = await fetch("/code/execute", {\n method: "POST",\n headers: {"Content-Type": "application/json"},\n body: JSON.stringify({code, language: "sql"})\n });\n\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus(result.success ? "Executed!" : "Error!");\n } catch (e) {\n console.error("Error executing:", e);\n document.getElementById("output").textContent = "Error executing: " + e.message;\n }\n }\n\n async function hotReload() {\n try {\n const response = await fetch("/code/reload", {method: "POST"});\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus("Hot Reloaded!");\n } catch (e) {\n console.error("Error reloading:", e);\n document.getElementById("output").textContent = "Error reloading: " + e.message;\n }\n }\n\n function newFile() {\n document.getElementById("filePath").value = "functions/new_function.sql";\n document.getElementById("codeEditor").value = `-- New function\nCREATE OR REPLACE FUNCTION new_function()\nRETURNS text AS $$\nBEGIN\n RETURN 'Hello from new function!';\nEND;\n$$ LANGUAGE plpgsql;`;\n currentFile = null;\n setStatus("New file");\n }\n\n function setStatus(text) {\n document.getElementById("status").textContent = text;\n }\n\n // Load files on startup\n window.addEventListener("load", loadFiles);\n </script>\n</body>\n</html> html 1 t 2025-09-22 20:00:58.493006-05 2025-09-22 20:00:58.493006-05 \N {"type": "ui", "component": "code_editor"}
21167 functions/self_modify.sql CREATE OR REPLACE FUNCTION get_version()\nRETURNS text AS $$\nBEGIN\n RETURN 'Version 2.0 - Hot Reloaded!';\nEND;\n$$ LANGUAGE plpgsql; sql 3 t 2025-09-22 20:00:58.496326-05 2025-09-22 20:02:58.560927-05 \N {"type": "function", "self_modifying": true}
21178 functions/html_encode.sql -- HTML encode function (like PHP's htmlspecialchars)\nCREATE OR REPLACE FUNCTION html_encode(input text)\nRETURNS text AS $func$\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n RETURN replace(\n replace(\n replace(\n replace(\n replace(input, '&', '&amp;'),\n '<', '&lt;'),\n '>', '&gt;'),\n '"', '&quot;'),\n '''', '&#39;');\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.206989-05 2025-09-22 20:41:10.206989-05 \N {}
21189 functions/url_encode.sql -- URL encode function (like PHP's urlencode)\nCREATE OR REPLACE FUNCTION url_encode(input text)\nRETURNS text AS $func$\nDECLARE\n result text := '';\n char_code int;\n i int;\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n FOR i IN 1..length(input) LOOP\n char_code := ascii(substring(input, i, 1));\n\n IF (char_code >= 48 AND char_code <= 57) OR -- 0-9\n (char_code >= 65 AND char_code <= 90) OR -- A-Z\n (char_code >= 97 AND char_code <= 122) OR -- a-z\n char_code IN (45, 46, 95, 126) THEN -- - . _ ~\n result := result || substring(input, i, 1);\n ELSIF char_code = 32 THEN\n result := result || '+';\n ELSE\n result := result || '%' || upper(to_hex(char_code));\n END IF;\n END LOOP;\n\n RETURN result;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.208676-05 2025-09-22 20:41:10.208676-05 \N {}
211910 functions/url_decode.sql -- URL decode function (like PHP's urldecode)\nCREATE OR REPLACE FUNCTION url_decode(input text)\nRETURNS text AS $func$\nDECLARE\n result text := '';\n i int := 1;\n len int;\n hex_str text;\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n len := length(input);\n\n WHILE i <= len LOOP\n IF substring(input, i, 1) = '%' AND i + 2 <= len THEN\n hex_str := substring(input, i + 1, 2);\n IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN\n result := result || chr(('x' || hex_str)::bit(8)::int);\n i := i + 3;\n ELSE\n result := result || '%';\n i := i + 1;\n END IF;\n ELSIF substring(input, i, 1) = '+' THEN\n result := result || ' ';\n i := i + 1;\n ELSE\n result := result || substring(input, i, 1);\n i := i + 1;\n END IF;\n END LOOP;\n\n RETURN result;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.20939-05 2025-09-22 20:41:10.20939-05 \N {}
212011 functions/parse_query_string.sql -- Query string parse function (like PHP's parse_str)\nCREATE OR REPLACE FUNCTION parse_query_string(query_string text)\nRETURNS jsonb AS $func$\nDECLARE\n params jsonb := '{}'::jsonb;\n pair text;\n key text;\n value text;\n pos int;\nBEGIN\n IF query_string IS NULL OR query_string = '' THEN\n RETURN params;\n END IF;\n\n IF left(query_string, 1) = '?' THEN\n query_string := substring(query_string, 2);\n END IF;\n\n FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP\n pos := position('=' IN pair);\n IF pos > 0 THEN\n key := url_decode(substring(pair, 1, pos - 1));\n value := url_decode(substring(pair, pos + 1));\n ELSE\n key := url_decode(pair);\n value := '';\n END IF;\n\n params := params || jsonb_build_object(key, value);\n END LOOP;\n\n RETURN params;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.209636-05 2025-09-22 20:41:10.209636-05 \N {}
212112 functions/slugify.sql -- Slugify function (convert text to URL-friendly slug)\nCREATE OR REPLACE FUNCTION slugify(input text)\nRETURNS text AS $func$\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n RETURN lower(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n input,\n '[^a-zA-Z0-9\\s-]', '', 'g'\n ),\n '\\s+', '-', 'g'\n ),\n '-+', '-', 'g'\n )\n );\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.209899-05 2025-09-22 20:41:10.209899-05 \N {}
212213 functions/format_bytes.sql -- Format bytes as human-readable size\nCREATE OR REPLACE FUNCTION format_bytes(bytes bigint)\nRETURNS text AS $func$\nDECLARE\n units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB'];\n unit_index int := 1;\n size numeric := bytes;\nBEGIN\n WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP\n size := size / 1024;\n unit_index := unit_index + 1;\n END LOOP;\n\n IF unit_index = 1 THEN\n RETURN bytes || ' B';\n ELSE\n RETURN round(size, 2) || ' ' || units[unit_index];\n END IF;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.210188-05 2025-09-22 20:41:10.210188-05 \N {}
2123\.
2124
2125
2126--
2127-- Data for Name: code_history; Type: TABLE DATA; Schema: public; Owner: danq
2128--
2129
2130COPY public.code_history (id, file_id, content, version, changed_at, changed_by) FROM stdin;
2131\.
2132
2133
2134--
2135-- Data for Name: exceptions; Type: TABLE DATA; Schema: public; Owner: danq
2136--
2137
2138COPY public.exceptions (id, error_type, message, traceback, context, function_name, occurred_at, resolved) FROM stdin;
2139\.
2140
2141
2142--
2143-- Data for Name: jobs; Type: TABLE DATA; Schema: public; Owner: danq
2144--
2145
2146COPY public.jobs (id, type, payload, status, result, attempts, created_at, started_at, completed_at) FROM stdin;
21471 cleanup {"description": "Initial cleanup"} completed {"status": "success", "message": "Cleanup completed"} 0 2025-09-22 19:48:50.241994-05 2025-09-22 19:49:45.260654-05 2025-09-22 19:49:45.260654-05
21482 analyze_blobs {"description": "Analyze storage"} completed {"avg_size": 51.0000000000000000, "total_size": 51, "total_blobs": 1} 0 2025-09-22 19:48:50.241994-05 2025-09-22 19:49:50.275851-05 2025-09-22 19:49:50.275851-05
21493 compute {"value": 42} completed {"result": 84} 0 2025-09-22 19:50:17.605383-05 2025-09-22 19:50:17.640306-05 2025-09-22 19:50:17.640306-05
2150\.
2151
2152
2153--
2154-- Data for Name: routes; Type: TABLE DATA; Schema: public; Owner: danq
2155--
2156
2157COPY public.routes (id, method, path_pattern, handler, description) FROM stdin;
21581 GET / home Welcome page
21592 GET /stats stats Database statistics
21603 GET /blobs list_blobs List all blobs
21614 GET /blob/:id get_blob Get specific blob
21625 POST /blob create_blob Upload new blob
21636 DELETE /blob/:id delete_blob Delete blob
21647 GET /jobs list_jobs List all jobs
21658 POST /job create_job Create new job
21669 GET /job/:id get_job Get job status
2167\.
2168
2169
2170--
2171-- Data for Name: sessions; Type: TABLE DATA; Schema: public; Owner: danq
2172--
2173
2174COPY public.sessions (id, data, created_at, expires_at, last_access) FROM stdin;
2175\.
2176
2177
2178--
2179-- Name: code_files_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq
2180--
2181
2182SELECT pg_catalog.setval('public.code_files_id_seq', 13, true);
2183
2184
2185--
2186-- Name: code_history_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq
2187--
2188
2189SELECT pg_catalog.setval('public.code_history_id_seq', 1, false);
2190
2191
2192--
2193-- Name: exceptions_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq
2194--
2195
2196SELECT pg_catalog.setval('public.exceptions_id_seq', 1, false);
2197
2198
2199--
2200-- Name: jobs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq
2201--
2202
2203SELECT pg_catalog.setval('public.jobs_id_seq', 3, true);
2204
2205
2206--
2207-- Name: routes_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq
2208--
2209
2210SELECT pg_catalog.setval('public.routes_id_seq', 9, true);
2211
2212
2213--
2214-- Name: blobs blobs_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2215--
2216
2217ALTER TABLE ONLY public.blobs
2218 ADD CONSTRAINT blobs_pkey PRIMARY KEY (id);
2219
2220
2221--
2222-- Name: cache cache_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2223--
2224
2225ALTER TABLE ONLY public.cache
2226 ADD CONSTRAINT cache_pkey PRIMARY KEY (key);
2227
2228
2229--
2230-- Name: circuit_breakers circuit_breakers_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2231--
2232
2233ALTER TABLE ONLY public.circuit_breakers
2234 ADD CONSTRAINT circuit_breakers_pkey PRIMARY KEY (function_name);
2235
2236
2237--
2238-- Name: code_files code_files_path_key; Type: CONSTRAINT; Schema: public; Owner: danq
2239--
2240
2241ALTER TABLE ONLY public.code_files
2242 ADD CONSTRAINT code_files_path_key UNIQUE (path);
2243
2244
2245--
2246-- Name: code_files code_files_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2247--
2248
2249ALTER TABLE ONLY public.code_files
2250 ADD CONSTRAINT code_files_pkey PRIMARY KEY (id);
2251
2252
2253--
2254-- Name: code_history code_history_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2255--
2256
2257ALTER TABLE ONLY public.code_history
2258 ADD CONSTRAINT code_history_pkey PRIMARY KEY (id);
2259
2260
2261--
2262-- Name: exceptions exceptions_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2263--
2264
2265ALTER TABLE ONLY public.exceptions
2266 ADD CONSTRAINT exceptions_pkey PRIMARY KEY (id);
2267
2268
2269--
2270-- Name: jobs jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2271--
2272
2273ALTER TABLE ONLY public.jobs
2274 ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);
2275
2276
2277--
2278-- Name: routes routes_method_path_pattern_key; Type: CONSTRAINT; Schema: public; Owner: danq
2279--
2280
2281ALTER TABLE ONLY public.routes
2282 ADD CONSTRAINT routes_method_path_pattern_key UNIQUE (method, path_pattern);
2283
2284
2285--
2286-- Name: routes routes_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2287--
2288
2289ALTER TABLE ONLY public.routes
2290 ADD CONSTRAINT routes_pkey PRIMARY KEY (id);
2291
2292
2293--
2294-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: danq
2295--
2296
2297ALTER TABLE ONLY public.sessions
2298 ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);
2299
2300
2301--
2302-- Name: idx_blobs_created; Type: INDEX; Schema: public; Owner: danq
2303--
2304
2305CREATE INDEX idx_blobs_created ON public.blobs USING btree (created_at DESC);
2306
2307
2308--
2309-- Name: idx_cache_expires; Type: INDEX; Schema: public; Owner: danq
2310--
2311
2312CREATE INDEX idx_cache_expires ON public.cache USING btree (expires_at);
2313
2314
2315--
2316-- Name: idx_exceptions_occurred; Type: INDEX; Schema: public; Owner: danq
2317--
2318
2319CREATE INDEX idx_exceptions_occurred ON public.exceptions USING btree (occurred_at DESC);
2320
2321
2322--
2323-- Name: idx_jobs_status; Type: INDEX; Schema: public; Owner: danq
2324--
2325
2326CREATE INDEX idx_jobs_status ON public.jobs USING btree (status, created_at);
2327
2328
2329--
2330-- Name: idx_sessions_expires; Type: INDEX; Schema: public; Owner: danq
2331--
2332
2333CREATE INDEX idx_sessions_expires ON public.sessions USING btree (expires_at);
2334
2335
2336--
2337-- Name: jobs job_created; Type: TRIGGER; Schema: public; Owner: danq
2338--
2339
2340CREATE TRIGGER job_created AFTER INSERT ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.notify_new_job();
2341
2342
2343--
2344-- Name: code_history code_history_file_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: danq
2345--
2346
2347ALTER TABLE ONLY public.code_history
2348 ADD CONSTRAINT code_history_file_id_fkey FOREIGN KEY (file_id) REFERENCES public.code_files(id);
2349
2350
2351--
2352-- PostgreSQL database dump complete
2353--
2354
2355\unrestrict ZHowxS4FzILvodLbuq1kLjCcpmcpSQ5reyQdJ8KCxiHbPVSQVdN99Uni14IBD26