askrobots / postblob Public

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

0 downloads

postblob/add_web_ui.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
add_web_ui.sql · 20.0 KB · 613 lines
1-- Add Web UI to PostBlob
2-- psql postblob < add_web_ui.sql
3
4-- Update the home route to return HTML
5CREATE OR REPLACE FUNCTION handle_request(
6 p_method text,
7 p_path text,
8 p_headers jsonb DEFAULT '{}'::jsonb,
9 p_body jsonb DEFAULT '{}'::jsonb,
10 p_session_id uuid DEFAULT NULL
11) RETURNS jsonb AS $$
12DECLARE
13 v_response jsonb;
14 v_route record;
15 v_blob_id uuid;
16 v_job_id integer;
17 v_stats jsonb;
18 v_html text;
19 v_blobs jsonb;
20 v_jobs jsonb;
21BEGIN
22 -- Default response
23 v_response := jsonb_build_object(
24 'status', 404,
25 'body', jsonb_build_object('error', 'Not found')
26 );
27
28 -- Route: GET / (HTML Dashboard)
29 IF p_method = 'GET' AND p_path = '/' THEN
30 -- Get stats
31 SELECT jsonb_build_object(
32 'blob_count', (SELECT COUNT(*) FROM blobs),
33 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),
34 'jobs_pending', (SELECT COUNT(*) FROM jobs WHERE status = 'pending'),
35 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),
36 'jobs_failed', (SELECT COUNT(*) FROM jobs WHERE status = 'failed'),
37 'total_exceptions', (SELECT COUNT(*) FROM exceptions),
38 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())
39 ) INTO v_stats;
40
41 -- Get recent blobs
42 SELECT COALESCE(jsonb_agg(
43 jsonb_build_object(
44 'id', id,
45 'filename', filename,
46 'size', size,
47 'created_at', created_at
48 ) ORDER BY created_at DESC
49 ), '[]'::jsonb)
50 INTO v_blobs
51 FROM (SELECT * FROM blobs ORDER BY created_at DESC LIMIT 5) b;
52
53 -- Get recent jobs
54 SELECT COALESCE(jsonb_agg(
55 jsonb_build_object(
56 'id', id,
57 'type', type,
58 'status', status,
59 'created_at', created_at
60 ) ORDER BY created_at DESC
61 ), '[]'::jsonb)
62 INTO v_jobs
63 FROM (SELECT * FROM jobs ORDER BY created_at DESC LIMIT 5) j;
64
65 -- Build HTML
66 v_html := format('<!DOCTYPE html>
67<html>
68<head>
69 <title>PostBlob - PostgreSQL Does Everything!</title>
70 <meta charset="utf-8">
71 <meta name="viewport" content="width=device-width, initial-scale=1">
72 <style>
73 * { margin: 0; padding: 0; box-sizing: border-box; }
74 body {
75 font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
76 line-height: 1.6;
77 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
78 min-height: 100vh;
79 padding: 20px;
80 }
81 .container {
82 max-width: 1200px;
83 margin: 0 auto;
84 }
85 header {
86 background: white;
87 border-radius: 12px;
88 padding: 30px;
89 margin-bottom: 30px;
90 box-shadow: 0 10px 40px rgba(0,0,0,0.1);
91 }
92 h1 {
93 color: #333;
94 margin-bottom: 10px;
95 font-size: 2.5em;
96 }
97 .subtitle {
98 color: #666;
99 font-size: 1.2em;
100 }
101 .grid {
102 display: grid;
103 grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
104 gap: 20px;
105 margin-bottom: 30px;
106 }
107 .card {
108 background: white;
109 border-radius: 12px;
110 padding: 25px;
111 box-shadow: 0 10px 40px rgba(0,0,0,0.1);
112 }
113 .card h2 {
114 color: #333;
115 margin-bottom: 20px;
116 font-size: 1.5em;
117 border-bottom: 2px solid #667eea;
118 padding-bottom: 10px;
119 }
120 .stat-grid {
121 display: grid;
122 grid-template-columns: repeat(auto-fit, minmax(120px, 1fr));
123 gap: 15px;
124 margin-bottom: 20px;
125 }
126 .stat {
127 text-align: center;
128 padding: 15px;
129 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
130 border-radius: 8px;
131 color: white;
132 }
133 .stat-value {
134 font-size: 2em;
135 font-weight: bold;
136 }
137 .stat-label {
138 font-size: 0.9em;
139 opacity: 0.9;
140 }
141 .list-item {
142 padding: 12px;
143 margin: 8px 0;
144 background: #f8f9fa;
145 border-radius: 6px;
146 border-left: 4px solid #667eea;
147 }
148 .list-item strong {
149 color: #333;
150 }
151 .status {
152 display: inline-block;
153 padding: 2px 8px;
154 border-radius: 4px;
155 font-size: 0.85em;
156 font-weight: bold;
157 }
158 .status.pending { background: #ffc107; color: #333; }
159 .status.completed { background: #28a745; color: white; }
160 .status.failed { background: #dc3545; color: white; }
161 .status.processing { background: #17a2b8; color: white; }
162 .endpoint {
163 padding: 15px;
164 margin: 10px 0;
165 background: #f8f9fa;
166 border-radius: 6px;
167 border-left: 4px solid #764ba2;
168 }
169 .method {
170 display: inline-block;
171 padding: 4px 8px;
172 border-radius: 4px;
173 font-weight: bold;
174 font-size: 0.85em;
175 margin-right: 10px;
176 }
177 .method.GET { background: #28a745; color: white; }
178 .method.POST { background: #007bff; color: white; }
179 .method.DELETE { background: #dc3545; color: white; }
180 .path {
181 font-family: "Monaco", "Menlo", monospace;
182 color: #333;
183 font-weight: bold;
184 }
185 .description {
186 color: #666;
187 margin-top: 5px;
188 }
189 .query-box {
190 margin-top: 20px;
191 }
192 input[type="text"] {
193 width: 100%%;
194 padding: 12px;
195 border: 2px solid #ddd;
196 border-radius: 6px;
197 font-size: 1em;
198 margin-bottom: 10px;
199 }
200 input[type="text"]:focus {
201 outline: none;
202 border-color: #667eea;
203 }
204 button {
205 background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);
206 color: white;
207 border: none;
208 padding: 12px 24px;
209 border-radius: 6px;
210 font-size: 1em;
211 cursor: pointer;
212 transition: transform 0.2s;
213 }
214 button:hover {
215 transform: translateY(-2px);
216 }
217 #queryResult {
218 margin-top: 15px;
219 padding: 15px;
220 background: #f8f9fa;
221 border-radius: 6px;
222 font-family: monospace;
223 white-space: pre-wrap;
224 display: none;
225 }
226 .footer {
227 text-align: center;
228 color: white;
229 margin-top: 40px;
230 }
231 .footer a {
232 color: white;
233 text-decoration: none;
234 font-weight: bold;
235 }
236 </style>
237</head>
238<body>
239 <div class="container">
240 <header>
241 <h1>🎯 PostBlob</h1>
242 <p class="subtitle">PostgreSQL Does Everything - Web UI</p>
243 </header>
244
245 <div class="grid">
246 <div class="card">
247 <h2>📊 Statistics</h2>
248 <div class="stat-grid">
249 <div class="stat">
250 <div class="stat-value">%s</div>
251 <div class="stat-label">Blobs</div>
252 </div>
253 <div class="stat">
254 <div class="stat-value">%s</div>
255 <div class="stat-label">Pending Jobs</div>
256 </div>
257 <div class="stat">
258 <div class="stat-value">%s</div>
259 <div class="stat-label">Completed</div>
260 </div>
261 <div class="stat">
262 <div class="stat-value">%s</div>
263 <div class="stat-label">Sessions</div>
264 </div>
265 </div>
266 <div class="list-item">
267 <strong>Total Storage:</strong> %s bytes
268 </div>
269 <div class="list-item">
270 <strong>Exceptions:</strong> %s
271 </div>
272 </div>
273
274 <div class="card">
275 <h2>💬 Natural Language Query</h2>
276 <div class="query-box">
277 <input type="text" id="queryInput" placeholder="Try: show blobs, failed jobs, show stats" />
278 <button onclick="runQuery()">Ask Database</button>
279 <div id="queryResult"></div>
280 </div>
281 <script>
282 async function runQuery() {
283 const input = document.getElementById("queryInput").value;
284 const result = document.getElementById("queryResult");
285
286 try {
287 const response = await fetch("/ask?q=" + encodeURIComponent(input));
288 const data = await response.json();
289 result.style.display = "block";
290 result.textContent = JSON.stringify(data, null, 2);
291 } catch (e) {
292 result.style.display = "block";
293 result.textContent = "Error: " + e.message;
294 }
295 }
296
297 document.getElementById("queryInput").addEventListener("keypress", (e) => {
298 if (e.key === "Enter") runQuery();
299 });
300 </script>
301 </div>
302 </div>
303
304 <div class="grid">
305 <div class="card">
306 <h2>📁 Recent Blobs</h2>
307 %s
308 <div style="margin-top: 20px;">
309 <a href="/blobs" style="color: #667eea;">View All Blobs →</a>
310 </div>
311 </div>
312
313 <div class="card">
314 <h2>⚡ Recent Jobs</h2>
315 %s
316 <div style="margin-top: 20px;">
317 <a href="/jobs" style="color: #667eea;">View All Jobs →</a>
318 </div>
319 </div>
320 </div>
321
322 <div class="card">
323 <h2>🚀 API Endpoints</h2>
324
325 <div class="endpoint">
326 <span class="method GET">GET</span>
327 <span class="path">/stats</span>
328 <div class="description">Get database statistics</div>
329 </div>
330
331 <div class="endpoint">
332 <span class="method GET">GET</span>
333 <span class="path">/blobs</span>
334 <div class="description">List all blobs</div>
335 </div>
336
337 <div class="endpoint">
338 <span class="method GET">GET</span>
339 <span class="path">/blob/{id}</span>
340 <div class="description">Get specific blob by ID</div>
341 </div>
342
343 <div class="endpoint">
344 <span class="method POST">POST</span>
345 <span class="path">/blob</span>
346 <div class="description">Upload new blob (JSON: filename, data, mimetype)</div>
347 </div>
348
349 <div class="endpoint">
350 <span class="method DELETE">DELETE</span>
351 <span class="path">/blob/{id}</span>
352 <div class="description">Delete a blob</div>
353 </div>
354
355 <div class="endpoint">
356 <span class="method GET">GET</span>
357 <span class="path">/jobs</span>
358 <div class="description">List all jobs</div>
359 </div>
360
361 <div class="endpoint">
362 <span class="method POST">POST</span>
363 <span class="path">/job</span>
364 <div class="description">Create new job (JSON: type, payload)</div>
365 </div>
366
367 <div class="endpoint">
368 <span class="method GET">GET</span>
369 <span class="path">/ask?q={query}</span>
370 <div class="description">Natural language database query</div>
371 </div>
372 </div>
373
374 <div class="footer">
375 <p>Powered by PostgreSQL • <a href="https://github.com/yourname/postblob">GitHub</a></p>
376 </div>
377 </div>
378</body>
379</html>',
380 v_stats->>'blob_count',
381 v_stats->>'jobs_pending',
382 v_stats->>'jobs_completed',
383 v_stats->>'active_sessions',
384 v_stats->>'total_size',
385 v_stats->>'total_exceptions',
386 CASE
387 WHEN jsonb_array_length(v_blobs) = 0 THEN '<p style="color: #999;">No blobs yet</p>'
388 ELSE (
389 SELECT string_agg(
390 format('<div class="list-item"><strong>%s</strong><br>Size: %s bytes<br>%s</div>',
391 b->>'filename',
392 b->>'size',
393 to_char((b->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')
394 ), ''
395 )
396 FROM jsonb_array_elements(v_blobs) b
397 )
398 END,
399 CASE
400 WHEN jsonb_array_length(v_jobs) = 0 THEN '<p style="color: #999;">No jobs yet</p>'
401 ELSE (
402 SELECT string_agg(
403 format('<div class="list-item"><strong>%s</strong><br><span class="status %s">%s</span><br>%s</div>',
404 j->>'type',
405 j->>'status',
406 j->>'status',
407 to_char((j->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')
408 ), ''
409 )
410 FROM jsonb_array_elements(v_jobs) j
411 )
412 END
413 );
414
415 v_response := jsonb_build_object(
416 'status', 200,
417 'headers', jsonb_build_object('Content-Type', 'text/html'),
418 'body', v_html,
419 'html', true
420 );
421
422 -- Route: GET /ask (Natural Language Query)
423 ELSIF p_method = 'GET' AND p_path LIKE '/ask%' THEN
424 DECLARE
425 v_query text;
426 BEGIN
427 -- Extract query parameter (simple parsing)
428 v_query := substring(p_path from 'q=([^&]+)');
429 v_query := replace(v_query, '%20', ' ');
430
431 IF v_query IS NOT NULL THEN
432 v_response := jsonb_build_object(
433 'status', 200,
434 'body', ask(v_query)
435 );
436 ELSE
437 v_response := jsonb_build_object(
438 'status', 400,
439 'body', jsonb_build_object('error', 'Missing query parameter')
440 );
441 END IF;
442 END;
443
444 -- Route: GET /stats
445 ELSIF p_method = 'GET' AND p_path = '/stats' THEN
446 SELECT jsonb_build_object(
447 'blob_count', (SELECT COUNT(*) FROM blobs),
448 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),
449 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),
450 'total_exceptions', (SELECT COUNT(*) FROM exceptions),
451 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())
452 ) INTO v_stats;
453
454 v_response := jsonb_build_object(
455 'status', 200,
456 'body', v_stats
457 );
458
459 -- Route: GET /blobs
460 ELSIF p_method = 'GET' AND p_path = '/blobs' THEN
461 v_response := jsonb_build_object(
462 'status', 200,
463 'body', (
464 SELECT COALESCE(jsonb_agg(
465 jsonb_build_object(
466 'id', id,
467 'filename', filename,
468 'size', size,
469 'mimetype', mimetype,
470 'created_at', created_at
471 ) ORDER BY created_at DESC
472 ), '[]'::jsonb)
473 FROM blobs
474 LIMIT 100
475 )
476 );
477
478 -- Route: GET /blob/{id}
479 ELSIF p_method = 'GET' AND p_path LIKE '/blob/%' THEN
480 v_blob_id := substring(p_path from '/blob/(.+)')::uuid;
481
482 SELECT jsonb_build_object(
483 'id', id,
484 'filename', filename,
485 'size', size,
486 'mimetype', mimetype,
487 'metadata', metadata,
488 'created_at', created_at
489 ) INTO v_response
490 FROM blobs
491 WHERE id = v_blob_id;
492
493 IF v_response IS NOT NULL THEN
494 v_response := jsonb_build_object(
495 'status', 200,
496 'body', v_response
497 );
498 ELSE
499 v_response := jsonb_build_object(
500 'status', 404,
501 'body', jsonb_build_object('error', 'Blob not found')
502 );
503 END IF;
504
505 -- Route: POST /blob
506 ELSIF p_method = 'POST' AND p_path = '/blob' THEN
507 INSERT INTO blobs (filename, mimetype, data, size, metadata)
508 VALUES (
509 COALESCE(p_body->>'filename', 'unnamed'),
510 COALESCE(p_body->>'mimetype', 'application/octet-stream'),
511 decode(p_body->>'data', 'base64'),
512 length(decode(p_body->>'data', 'base64')),
513 COALESCE(p_body->'metadata', '{}'::jsonb)
514 )
515 RETURNING id INTO v_blob_id;
516
517 v_response := jsonb_build_object(
518 'status', 201,
519 'body', jsonb_build_object(
520 'id', v_blob_id,
521 'url', '/blob/' || v_blob_id
522 )
523 );
524
525 -- Route: DELETE /blob/{id}
526 ELSIF p_method = 'DELETE' AND p_path LIKE '/blob/%' THEN
527 v_blob_id := substring(p_path from '/blob/(.+)')::uuid;
528
529 DELETE FROM blobs WHERE id = v_blob_id;
530
531 IF FOUND THEN
532 v_response := jsonb_build_object(
533 'status', 200,
534 'body', jsonb_build_object('deleted', v_blob_id)
535 );
536 ELSE
537 v_response := jsonb_build_object(
538 'status', 404,
539 'body', jsonb_build_object('error', 'Blob not found')
540 );
541 END IF;
542
543 -- Route: GET /jobs
544 ELSIF p_method = 'GET' AND p_path = '/jobs' THEN
545 v_response := jsonb_build_object(
546 'status', 200,
547 'body', (
548 SELECT COALESCE(jsonb_agg(
549 jsonb_build_object(
550 'id', id,
551 'type', type,
552 'status', status,
553 'created_at', created_at,
554 'completed_at', completed_at
555 ) ORDER BY created_at DESC
556 ), '[]'::jsonb)
557 FROM jobs
558 LIMIT 100
559 )
560 );
561
562 -- Route: POST /job
563 ELSIF p_method = 'POST' AND p_path = '/job' THEN
564 INSERT INTO jobs (type, payload)
565 VALUES (
566 COALESCE(p_body->>'type', 'default'),
567 COALESCE(p_body->'payload', '{}'::jsonb)
568 )
569 RETURNING id INTO v_job_id;
570
571 -- Notify job worker
572 PERFORM pg_notify('new_job', v_job_id::text);
573
574 v_response := jsonb_build_object(
575 'status', 201,
576 'body', jsonb_build_object(
577 'job_id', v_job_id,
578 'status', 'pending'
579 )
580 );
581
582 -- Route: GET /job/{id}
583 ELSIF p_method = 'GET' AND p_path LIKE '/job/%' THEN
584 v_job_id := substring(p_path from '/job/(\d+)')::integer;
585
586 SELECT jsonb_build_object(
587 'id', id,
588 'type', type,
589 'status', status,
590 'payload', payload,
591 'result', result,
592 'created_at', created_at,
593 'completed_at', completed_at
594 ) INTO v_response
595 FROM jobs
596 WHERE id = v_job_id;
597
598 IF v_response IS NOT NULL THEN
599 v_response := jsonb_build_object(
600 'status', 200,
601 'body', v_response
602 );
603 ELSE
604 v_response := jsonb_build_object(
605 'status', 404,
606 'body', jsonb_build_object('error', 'Job not found')
607 );
608 END IF;
609 END IF;
610
611 RETURN v_response;
612END;
613$$ LANGUAGE plpgsql;