An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
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 |
| 5 | CREATE 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 $$ |
| 12 | DECLARE |
| 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; |
| 21 | BEGIN |
| 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; |
| 612 | END; |
| 613 | $$ LANGUAGE plpgsql; |