add matrix page

author: grothedev <grothedev@gmail.com> 2026-04-19 22:51:19 -0500
committer: grothedev <grothedev@gmail.com> 2026-04-19 22:51:19 -0500
commit: 2f57affde6641c8e5912e483c234f84608be8eb2 (patch)
tree: c9534f15a1ff96713421c8a106a7ff996a9edb17
parent: df0f054366a81d02b28a5e2ae0d571cf5b153256 (diff)
4 files changed, 908 insertions, 3 deletions
diff --git a/resources/css/home.css b/resources/css/home.css
index ebbbb29..3223a44 100644
--- a/resources/css/home.css
+++ b/resources/css/home.css
@@ -42,9 +42,12 @@ section h3 {
     text-decoration: none;
 }
 a {
-    color: #3f6d87;
+    font-weight: bold;
+    color: #548226;
     text-decoration: none;
-    padding: .5rem;
+    padding-bottom: .1rem;
+    border-bottom: 1px dashed gray;
+    margin: .5rem;
 }
 li {
     list-style-type: none;
diff --git a/resources/views/matrix.blade.php b/resources/views/matrix.blade.php
new file mode 100755
index 0000000..b5deb92
--- /dev/null
+++ b/resources/views/matrix.blade.php
@@ -0,0 +1,41 @@
+@extends('template')
+@section('content')
+<main>
+    <section style="background-color: hsl(0, 0%, 72%); padding: 1rem; ">
+        <center></center>
+            <br><h3><a href = "https://matrix.org/">Matrix Communications System</a></h3>
+            <h2>An open network for secure, decentralised communication</h2>
+        </center>
+    </section>
+    <section>
+        <p>I run a Matrix server on this domain. Feel free to use it.</p>
+        <p>My Matrix Account: <a href = "https://matrix.to/@goob:belthelziquor.com">@goob:belthelziquor.com</a></p>
+    </section>
+    <section>
+        <p>Don't know what to do with that URL? Don't know what Matrix is? Read on. It's quite simple.</p>
+    </section>
+
+    <section>
+        <h3>What is Matrix?</h3>
+        <p>
+            Matrix is an open standard and communication protocol for real-time messaging, voice, and video.
+            Think of it like email — but for instant messaging. Just like you can send an email from Gmail to
+            a Yahoo address without either party needing to use the same service, Matrix lets you chat with
+            anyone on any Matrix server, from your own account which exists on some server.
+        </p>
+        <p>
+            You don't need to trust a corporation with your conversations. There's no single company running
+            Matrix — it's a <strong><a href = "https://www.geeksforgeeks.org/system-design/federated-architecture-system-design">federated</a>, <a href = "https://en.wikipedia.org/wiki/Decentralization"></a>decentralized network</a></strong>. Thousands of servers run
+            independently and talk to each other. My server (<code>belthelziquor.com</code>) is one of them.
+        </p>
+    </section>
+    <section>
+        <h3>Further Reading</h3>
+        <ul>
+            <li><a href="https://matrix.org/docs/chat_basics/matrix-for-im/">Matrix for Instant Messaging</a> — official intro</li>
+            <li><a href="https://element.io/">Element</a> — the most popular Matrix client</li>
+            <li><a href="https://servers.joinmatrix.org/">Public server list</a> — find a homeserver or browse what's out there</li>
+        </ul>
+    </section>
+</main>
+@endsection
+\ No newline at end of file
diff --git a/resources/views/psql.blade.php b/resources/views/psql.blade.php
new file mode 100644
index 0000000..226567a
--- /dev/null
+++ b/resources/views/psql.blade.php
@@ -0,0 +1,840 @@
+@extends('template')
+@section('content')
+<!DOCTYPE html>
+<html lang="en">
+<head>
+<meta charset="UTF-8">
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<title>psql quick reference</title>
+<link href="https://fonts.googleapis.com/css2?family=Berkeley+Mono&family=Inter:wght@400;500&display=swap" rel="stylesheet">
+<style>
+
+* { box-sizing: border-box; margin: 0; padding: 0; }
+
+body {
+  background: #1e2128;
+  color: #cdd3de;
+  font-family: 'Inter', sans-serif;
+  font-size: 14px;
+  line-height: 1.6;
+}
+
+.page {
+  max-width: 1400px;
+  margin: 0 auto;
+}
+
+/* ── HEADER ── */
+h1 {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 28px;
+  font-weight: 400;
+  color: #7dd3db;
+  letter-spacing: -0.5px;
+  margin-bottom: 4px;
+}
+
+.subtitle {
+  font-size: 13px;
+  color: #5a6270;
+  margin-bottom: 32px;
+}
+
+/* ── TOC ── */
+.toc {
+  background: #252830;
+  border: 1px solid #2e3340;
+  border-radius: 6px;
+  padding: 16px 20px;
+  margin-bottom: 40px;
+}
+
+.toc-label {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  color: #5a6270;
+  text-transform: uppercase;
+  letter-spacing: 0.08em;
+  margin-bottom: 10px;
+}
+
+.toc-links {
+  display: flex;
+  flex-direction: column;
+  gap: 4px;
+  width: fit-content;
+}
+
+.toc-links a {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 12px;
+  color: #7dd3db;
+  text-decoration: none;
+  background: #1e2128;
+  border: 1px solid #2e3340;
+  padding: 3px 10px;
+  border-radius: 4px;
+}
+
+.toc-links a:hover { border-color: #7dd3db; }
+
+/* ── TWO-COLUMN AREA ── */
+.two-col {
+  display: grid;
+  grid-template-columns: 1fr 1fr;
+  gap: 16px;
+  margin-bottom: 16px;
+}
+
+/* ── SECTION (full width) ── */
+.section {
+  width: 100%;
+  background: #252830;
+  border: 1px solid #2e3340;
+  border-radius: 6px;
+  overflow: hidden;
+  margin-bottom: 16px;
+  scroll-margin-top: 24px;
+}
+
+/* cards inside two-col don't need bottom margin */
+.two-col .section { margin-bottom: 0; }
+
+.section-head {
+  display: flex;
+  align-items: center;
+  gap: 8px;
+  padding: 10px 16px;
+  background: #2a2e38;
+  border-bottom: 1px solid #2e3340;
+}
+
+.section-head h2 {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  font-weight: 400;
+  color: #5a6270;
+  letter-spacing: 0.1em;
+  text-transform: uppercase;
+  flex: 1;
+}
+
+.dot {
+  width: 8px;
+  height: 8px;
+  border-radius: 50%;
+  flex-shrink: 0;
+}
+
+.back {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  color: #5a6270;
+  text-decoration: none;
+  padding: 2px 6px;
+  border: 1px solid #2e3340;
+  border-radius: 3px;
+}
+.back:hover { color: #7dd3db; border-color: #7dd3db; }
+
+/* ── REFERENCE TABLE ── */
+.ref-table {
+  width: 100%;
+  border-collapse: collapse;
+}
+
+.ref-table td {
+  padding: 7px 16px;
+  vertical-align: top;
+  border-bottom: 1px solid #2e3340;
+  font-size: 13px;
+}
+
+.ref-table tr:last-child td { border-bottom: none; }
+.ref-table tr:hover td { background: #2a2e38; }
+
+.ref-table td:first-child {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 12px;
+  white-space: nowrap;
+  width: 40%;
+}
+
+.ref-table td:last-child { color: #5a6270; }
+
+.ref-table .section-row td {
+  padding: 8px 16px 4px;
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 10px;
+  color: #5a6270;
+  text-transform: uppercase;
+  letter-spacing: 0.08em;
+  background: #2a2e38;
+  border-bottom: 1px solid #2e3340;
+}
+
+/* ── CODE BLOCKS ── */
+.code-grid {
+  display: grid;
+  grid-template-columns: 1fr 1fr;
+}
+
+.code-block {
+  padding: 14px 16px;
+  border-right: 1px solid #2e3340;
+  border-bottom: 1px solid #2e3340;
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 12px;
+  line-height: 1.85;
+  background: #1e2128;
+}
+
+.code-block:nth-child(even) { border-right: none; }
+
+/* last row: remove bottom borders */
+.code-block:nth-last-child(1),
+.code-block:nth-last-child(2) { border-bottom: none; }
+
+/* if odd number, last item spans or just loses bottom */
+.code-block:only-child { grid-column: 1 / -1; border-right: none; border-bottom: none; }
+
+.lbl {
+  display: block;
+  font-size: 10px;
+  color: #fffc41fa;
+  text-transform: uppercase;
+  letter-spacing: 0.07em;
+  margin-bottom: 6px;
+}
+
+/* ── SYNTAX COLORS ── */
+.kw   { color: #6fa8e8; }
+.fn   { color: #c792ea; }
+.str  { color: #88cc88; }
+.num  { color: #f0a070; }
+.cmt  { color: #4a5262; font-style: italic; }
+.op   { color: #7dd3db; }
+.typ  { color: #e5c07b; }
+.cmd  { color: #7dd3db; }
+.tbl  { color: #f0a070; }
+
+/* ── JOIN VISUAL ── */
+.join-grid {
+  display: grid;
+  grid-template-columns: repeat(6, 1fr);
+  gap: 1px;
+  background: #2e3340;
+  border-top: 1px solid #2e3340;
+}
+
+.join-cell {
+  background: #1e2128;
+  padding: 12px 14px;
+}
+
+.join-name {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  color: #7dd3db;
+  margin-bottom: 4px;
+  margin-top: 4px;
+}
+
+.join-desc { font-size: 11px; color: #5a6270; line-height: 1.5; }
+
+.venn { display: flex; align-items: center; margin-bottom: 6px; }
+.c { width: 20px; height: 20px; border-radius: 50%; border: 1.5px solid; flex-shrink: 0; }
+.cL { border-color: #6fa8e8; margin-right: -6px; }
+.cR { border-color: #f0a070; }
+.fL  { background: rgba(111,168,232,0.3); }
+.fR  { background: rgba(240,160,112,0.3); }
+.fB  { background: rgba(160,140,200,0.3); }
+
+/* ── PILLS ── */
+.pill {
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  padding: 1px 8px;
+  border-radius: 3px;
+}
+.p-blue   { background: #1a2a3a; color: #6fa8e8; }
+.p-yellow { background: #2e2412; color: #e5c07b; }
+.p-red    { background: #2e1818; color: #e06c75; }
+
+/* ── JSONB split ── */
+.split {
+  display: grid;
+  grid-template-columns: 1fr 1fr;
+  border-top: 1px solid #2e3340;
+}
+.split > *:first-child { border-right: 1px solid #2e3340; }
+
+footer {
+  margin-top: 48px;
+  font-family: 'Berkeley Mono', monospace;
+  font-size: 11px;
+  color: #3a4050;
+  text-align: center;
+}
+</style>
+</head>
+<body>
+<div class="page">
+
+<h1>psql</h1>
+<div class="subtitle">PostgreSQL 16 quick reference &nbsp;·&nbsp; <a href="https://postgresql.org/docs" style="color:#5a6270">postgresql.org/docs</a></div>
+
+<!-- TOC -->
+<div class="toc" id="top">
+  <div class="toc-label">sections</div>
+  <div class="toc-links">
+    <a href="#meta">meta-commands</a>
+    <a href="#types">data types</a>
+    <a href="#ddl">DDL</a>
+    <a href="#dml">DML</a>
+    <a href="#select">SELECT</a>
+    <a href="#functions">functions</a>
+    <a href="#joins">joins</a>
+    <a href="#users">users &amp; perms</a>
+    <a href="#transactions">transactions</a>
+    <a href="#explain">explain &amp; analyze</a>
+    <a href="#admin">admin queries</a>
+    <a href="#jsonb">jsonb</a>
+    <a href="#conditional">conditional &amp; casting</a>
+  </div>
+</div>
+
+<!-- two-col: meta + types -->
+<div class="two-col">
+
+  <div class="section" id="meta">
+    <div class="section-head">
+      <span class="dot" style="background:#7dd3db"></span>
+      <h2>meta-commands ( \ )</h2>
+      <a class="back" href="#top">↑</a>
+    </div>
+    <table class="ref-table">
+      <tr><td><span class="cmd">\l</span></td><td>list databases</td></tr>
+      <tr><td><span class="cmd">\c dbname</span></td><td>connect to database</td></tr>
+      <tr><td><span class="cmd">\dt</span></td><td>list tables in schema</td></tr>
+      <tr><td><span class="cmd">\d table</span></td><td>describe table</td></tr>
+      <tr><td><span class="cmd">\di</span></td><td>list indexes</td></tr>
+      <tr><td><span class="cmd">\dv</span></td><td>list views</td></tr>
+      <tr><td><span class="cmd">\df</span></td><td>list functions</td></tr>
+      <tr><td><span class="cmd">\dn</span></td><td>list schemas</td></tr>
+      <tr><td><span class="cmd">\du</span></td><td>list roles / users</td></tr>
+      <tr><td><span class="cmd">\dp table</span></td><td>show privileges</td></tr>
+      <tr><td><span class="cmd">\timing</span></td><td>toggle execution time</td></tr>
+      <tr><td><span class="cmd">\x</span></td><td>toggle expanded output</td></tr>
+      <tr><td><span class="cmd">\e</span></td><td>open query in $EDITOR</td></tr>
+      <tr><td><span class="cmd">\i file.sql</span></td><td>execute sql file</td></tr>
+      <tr><td><span class="cmd">\o file</span></td><td>pipe output to file</td></tr>
+      <tr><td><span class="cmd">\q</span></td><td>quit</td></tr>
+    </table>
+  </div>
+
+  <div class="section" id="types">
+    <div class="section-head">
+      <span class="dot" style="background:#e5c07b"></span>
+      <h2>data types</h2>
+      <a class="back" href="#top">↑</a>
+    </div>
+    <table class="ref-table">
+      <tr><td><span class="typ">integer</span> / <span class="typ">int</span></td><td>4-byte signed int</td></tr>
+      <tr><td><span class="typ">bigint</span></td><td>8-byte signed int</td></tr>
+      <tr><td><span class="typ">smallint</span></td><td>2-byte signed int</td></tr>
+      <tr><td><span class="typ">serial</span> / <span class="typ">bigserial</span></td><td>auto-incrementing int</td></tr>
+      <tr><td><span class="typ">numeric(p,s)</span></td><td>exact decimal</td></tr>
+      <tr><td><span class="typ">real</span> / <span class="typ">float8</span></td><td>4/8-byte float</td></tr>
+      <tr><td><span class="typ">boolean</span></td><td>true / false / null</td></tr>
+      <tr><td><span class="typ">varchar(n)</span></td><td>variable string, limit n</td></tr>
+      <tr><td><span class="typ">text</span></td><td>unlimited string</td></tr>
+      <tr><td><span class="typ">bytea</span></td><td>binary data</td></tr>
+      <tr><td><span class="typ">date</span></td><td>calendar date</td></tr>
+      <tr><td><span class="typ">time</span></td><td>time of day</td></tr>
+      <tr><td><span class="typ">timestamp</span></td><td>date + time (no tz)</td></tr>
+      <tr><td><span class="typ">timestamptz</span></td><td>date + time with tz</td></tr>
+      <tr><td><span class="typ">interval</span></td><td>time span</td></tr>
+      <tr><td><span class="typ">uuid</span></td><td>universally unique id</td></tr>
+      <tr><td><span class="typ">json</span> / <span class="typ">jsonb</span></td><td>json (jsonb = binary)</td></tr>
+      <tr><td><span class="typ">int[]</span> / <span class="typ">text[]</span></td><td>arrays of any type</td></tr>
+    </table>
+  </div>
+
+</div><!-- end two-col -->
+
+<!-- DDL — full width -->
+<div class="section" id="ddl">
+  <div class="section-head">
+    <span class="dot" style="background:#6fa8e8"></span>
+    <h2>DDL — define</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">create table</span>
+      <span class="kw">CREATE TABLE</span> <span class="tbl">users</span> (<br>
+      &nbsp;&nbsp;id         <span class="typ">bigserial</span>    <span class="kw">PRIMARY KEY</span>,<br>
+      &nbsp;&nbsp;username   <span class="typ">text</span>         <span class="kw">NOT NULL UNIQUE</span>,<br>
+      &nbsp;&nbsp;email      <span class="typ">text</span>         <span class="kw">NOT NULL</span>,<br>
+      &nbsp;&nbsp;created_at <span class="typ">timestamptz</span>  <span class="kw">DEFAULT</span> <span class="fn">now</span>()<br>
+      );
+    </div>
+    <div class="code-block">
+      <span class="lbl">alter table</span>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">users</span> <span class="kw">ADD COLUMN</span> bio <span class="typ">text</span>;<br>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">users</span> <span class="kw">DROP COLUMN</span> bio;<br>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">users</span> <span class="kw">RENAME COLUMN</span> email <span class="kw">TO</span> mail;<br>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">users</span> <span class="kw">ALTER COLUMN</span> bio <span class="kw">SET NOT NULL</span>;<br>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">users</span> <span class="kw">RENAME TO</span> <span class="tbl">accounts</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">indexes</span>
+      <span class="kw">CREATE INDEX</span> idx_email <span class="kw">ON</span> <span class="tbl">users</span>(email);<br>
+      <span class="kw">CREATE UNIQUE INDEX ON</span> <span class="tbl">users</span>(username);<br>
+      <span class="kw">CREATE INDEX ON</span> <span class="tbl">users</span> <span class="kw">USING</span> gin(data);<br>
+      <span class="kw">CREATE INDEX CONCURRENTLY ON</span> <span class="tbl">users</span>(email);<br>
+      <span class="kw">DROP INDEX</span> idx_email;
+    </div>
+    <div class="code-block">
+      <span class="lbl">foreign key</span>
+      <span class="kw">ALTER TABLE</span> <span class="tbl">posts</span> <span class="kw">ADD CONSTRAINT</span> fk_user<br>
+      &nbsp;&nbsp;<span class="kw">FOREIGN KEY</span>(user_id)<br>
+      &nbsp;&nbsp;<span class="kw">REFERENCES</span> <span class="tbl">users</span>(id)<br>
+      &nbsp;&nbsp;<span class="kw">ON DELETE CASCADE</span>;
+    </div>
+  </div>
+</div>
+
+<!-- DML — full width -->
+<div class="section" id="dml">
+  <div class="section-head">
+    <span class="dot" style="background:#88cc88"></span>
+    <h2>DML — manipulate</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">insert</span>
+      <span class="kw">INSERT INTO</span> <span class="tbl">users</span> (username, email)<br>
+      <span class="kw">VALUES</span> (<span class="str">'alice'</span>, <span class="str">'alice@example.com'</span>)<br>
+      <span class="kw">RETURNING</span> id, created_at;
+    </div>
+    <div class="code-block">
+      <span class="lbl">upsert</span>
+      <span class="kw">INSERT INTO</span> <span class="tbl">users</span> (username, email)<br>
+      <span class="kw">VALUES</span> (<span class="str">'alice'</span>, <span class="str">'alice@example.com'</span>)<br>
+      <span class="kw">ON CONFLICT</span>(username) <span class="kw">DO UPDATE SET</span><br>
+      &nbsp;&nbsp;email <span class="op">=</span> <span class="kw">EXCLUDED</span>.email;
+    </div>
+    <div class="code-block">
+      <span class="lbl">update</span>
+      <span class="kw">UPDATE</span> <span class="tbl">users</span> <span class="kw">SET</span> email <span class="op">=</span> <span class="str">'x@y.com'</span><br>
+      <span class="kw">WHERE</span> id <span class="op">=</span> <span class="num">42</span><br>
+      <span class="kw">RETURNING</span> *;
+    </div>
+    <div class="code-block">
+      <span class="lbl">update from join</span>
+      <span class="kw">UPDATE</span> <span class="tbl">users</span> u<br>
+      <span class="kw">SET</span> score <span class="op">=</span> s.score<br>
+      <span class="kw">FROM</span> <span class="tbl">scores</span> s<br>
+      <span class="kw">WHERE</span> s.user_id <span class="op">=</span> u.id;
+    </div>
+    <div class="code-block">
+      <span class="lbl">delete / truncate</span>
+      <span class="kw">DELETE FROM</span> <span class="tbl">users</span> <span class="kw">WHERE</span> id <span class="op">=</span> <span class="num">42</span>;<br>
+      <span class="kw">TRUNCATE TABLE</span> <span class="tbl">users</span><br>
+      &nbsp;&nbsp;<span class="kw">RESTART IDENTITY CASCADE</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">copy (bulk import / export)</span>
+      <span class="kw">COPY</span> <span class="tbl">users</span> <span class="kw">FROM</span> <span class="str">'/tmp/in.csv'</span> CSV HEADER;<br>
+      <span class="kw">COPY</span> <span class="tbl">users</span> <span class="kw">TO</span> <span class="str">'/tmp/out.csv'</span> CSV HEADER;
+    </div>
+  </div>
+</div>
+
+<!-- SELECT — full width -->
+<div class="section" id="select">
+  <div class="section-head">
+    <span class="dot" style="background:#c792ea"></span>
+    <h2>SELECT anatomy</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">group / order / limit</span>
+      <span class="kw">SELECT</span> u.id, <span class="fn">count</span>(p.id) <span class="kw">AS</span> posts<br>
+      <span class="kw">FROM</span> <span class="tbl">users</span> u<br>
+      <span class="kw">LEFT JOIN</span> <span class="tbl">posts</span> p <span class="kw">ON</span> p.user_id <span class="op">=</span> u.id<br>
+      <span class="kw">WHERE</span> u.created_at <span class="op">></span> <span class="str">'2024-01-01'</span><br>
+      <span class="kw">GROUP BY</span> u.id<br>
+      <span class="kw">HAVING</span> <span class="fn">count</span>(p.id) <span class="op">></span> <span class="num">5</span><br>
+      <span class="kw">ORDER BY</span> posts <span class="kw">DESC</span><br>
+      <span class="kw">LIMIT</span> <span class="num">10</span> <span class="kw">OFFSET</span> <span class="num">20</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">window functions</span>
+      <span class="kw">SELECT</span> username,<br>
+      &nbsp;&nbsp;<span class="fn">row_number</span>() <span class="kw">OVER</span> (<span class="kw">ORDER BY</span> created_at),<br>
+      &nbsp;&nbsp;<span class="fn">rank</span>() <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> role <span class="kw">ORDER BY</span> score <span class="kw">DESC</span>),<br>
+      &nbsp;&nbsp;<span class="fn">lag</span>(score) <span class="kw">OVER</span> (<span class="kw">ORDER BY</span> created_at),<br>
+      &nbsp;&nbsp;<span class="fn">sum</span>(score) <span class="kw">OVER</span> (<span class="kw">PARTITION BY</span> role)<br>
+      <span class="kw">FROM</span> <span class="tbl">users</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">CTE</span>
+      <span class="kw">WITH</span> active <span class="kw">AS</span> (<br>
+      &nbsp;&nbsp;<span class="kw">SELECT</span> * <span class="kw">FROM</span> <span class="tbl">users</span> <span class="kw">WHERE</span> active <span class="op">=</span> <span class="kw">true</span><br>
+      ),<br>
+      ranked <span class="kw">AS</span> (<br>
+      &nbsp;&nbsp;<span class="kw">SELECT</span> *, <span class="fn">row_number</span>() <span class="kw">OVER</span> (<span class="kw">ORDER BY</span> score) rn<br>
+      &nbsp;&nbsp;<span class="kw">FROM</span> active<br>
+      )<br>
+      <span class="kw">SELECT</span> * <span class="kw">FROM</span> ranked <span class="kw">WHERE</span> rn <span class="op"><=</span> <span class="num">10</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">subquery / exists</span>
+      <span class="kw">SELECT</span> * <span class="kw">FROM</span> <span class="tbl">users</span> <span class="kw">WHERE</span> id <span class="kw">IN</span> (<br>
+      &nbsp;&nbsp;<span class="kw">SELECT</span> user_id <span class="kw">FROM</span> <span class="tbl">posts</span><br>
+      &nbsp;&nbsp;<span class="kw">WHERE</span> published <span class="op">=</span> <span class="kw">true</span><br>
+      );<br><br>
+      <span class="kw">SELECT</span> * <span class="kw">FROM</span> <span class="tbl">users</span> u <span class="kw">WHERE EXISTS</span> (<br>
+      &nbsp;&nbsp;<span class="kw">SELECT</span> <span class="num">1</span> <span class="kw">FROM</span> <span class="tbl">posts</span> p <span class="kw">WHERE</span> p.user_id <span class="op">=</span> u.id<br>
+      );
+    </div>
+  </div>
+</div>
+
+<!-- functions — full width -->
+<div class="section" id="functions">
+  <div class="section-head">
+    <span class="dot" style="background:#f0a070"></span>
+    <h2>functions</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <table class="ref-table">
+    <tr class="section-row"><td colspan="2">aggregate</td></tr>
+    <tr><td><span class="fn">count</span>(*)</td><td>row count</td></tr>
+    <tr><td><span class="fn">sum</span>(col) / <span class="fn">avg</span>(col)</td><td>sum / average</td></tr>
+    <tr><td><span class="fn">min</span>(col) / <span class="fn">max</span>(col)</td><td>min / max value</td></tr>
+    <tr><td><span class="fn">string_agg</span>(col, sep)</td><td>concatenate to string</td></tr>
+    <tr><td><span class="fn">array_agg</span>(col)</td><td>collect into array</td></tr>
+    <tr><td><span class="fn">json_agg</span>(col)</td><td>collect into json array</td></tr>
+    <tr class="section-row"><td colspan="2">string</td></tr>
+    <tr><td><span class="fn">length</span>(str)</td><td>character count</td></tr>
+    <tr><td><span class="fn">lower</span> / <span class="fn">upper</span>(str)</td><td>case conversion</td></tr>
+    <tr><td><span class="fn">trim</span>(str)</td><td>strip whitespace</td></tr>
+    <tr><td><span class="fn">substring</span>(str, pos, len)</td><td>extract substring</td></tr>
+    <tr><td><span class="fn">replace</span>(str, from, to)</td><td>replace occurrence</td></tr>
+    <tr><td><span class="fn">split_part</span>(str, delim, n)</td><td>split and pick nth part</td></tr>
+    <tr><td><span class="fn">regexp_replace</span>(str, pat, rep)</td><td>regex substitution</td></tr>
+    <tr><td><span class="fn">concat</span>(a, b, ...)</td><td>concatenate (nulls skipped)</td></tr>
+    <tr class="section-row"><td colspan="2">date / time</td></tr>
+    <tr><td><span class="fn">now</span>()</td><td>current timestamp with tz</td></tr>
+    <tr><td><span class="fn">current_date</span></td><td>today's date</td></tr>
+    <tr><td><span class="fn">date_trunc</span>(<span class="str">'month'</span>, ts)</td><td>truncate to unit</td></tr>
+    <tr><td><span class="fn">extract</span>(year <span class="kw">FROM</span> ts)</td><td>extract part</td></tr>
+    <tr><td><span class="fn">age</span>(ts1, ts2)</td><td>interval between timestamps</td></tr>
+    <tr class="section-row"><td colspan="2">math &amp; misc</td></tr>
+    <tr><td><span class="fn">abs</span> / <span class="fn">ceil</span> / <span class="fn">floor</span>(n)</td><td>absolute / ceiling / floor</td></tr>
+    <tr><td><span class="fn">round</span>(n, d)</td><td>round to d decimal places</td></tr>
+    <tr><td><span class="fn">random</span>()</td><td>random float 0.0–1.0</td></tr>
+    <tr><td><span class="fn">gen_random_uuid</span>()</td><td>generate v4 uuid</td></tr>
+  </table>
+</div>
+
+<!-- joins — full width -->
+<div class="section" id="joins">
+  <div class="section-head">
+    <span class="dot" style="background:#e06c75"></span>
+    <h2>joins</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="join-grid">
+    <div class="join-cell">
+      <div class="venn"><div class="c cL fB"></div><div class="c cR fB"></div></div>
+      <div class="join-name">INNER JOIN</div>
+      <div class="join-desc">matching rows in both tables</div>
+    </div>
+    <div class="join-cell">
+      <div class="venn"><div class="c cL fL"></div><div class="c cR"></div></div>
+      <div class="join-name">LEFT JOIN</div>
+      <div class="join-desc">all left rows, null if no right match</div>
+    </div>
+    <div class="join-cell">
+      <div class="venn"><div class="c cL"></div><div class="c cR fR"></div></div>
+      <div class="join-name">RIGHT JOIN</div>
+      <div class="join-desc">all right rows, null if no left match</div>
+    </div>
+    <div class="join-cell">
+      <div class="venn"><div class="c cL fL"></div><div class="c cR fR"></div></div>
+      <div class="join-name">FULL OUTER JOIN</div>
+      <div class="join-desc">all rows, nulls where no match</div>
+    </div>
+    <div class="join-cell">
+      <div class="venn" style="gap:8px"><div class="c cL" style="margin:0"></div><div class="c cR"></div></div>
+      <div class="join-name">CROSS JOIN</div>
+      <div class="join-desc">every left row × every right row</div>
+    </div>
+    <div class="join-cell">
+      <div class="venn"><div class="c cL fL" style="margin:0"></div><div class="c cR" style="margin-left:-6px"></div></div>
+      <div class="join-name">ANTI-JOIN</div>
+      <div class="join-desc">LEFT JOIN … WHERE right IS NULL</div>
+    </div>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">standard syntax</span>
+      <span class="kw">SELECT</span> u.username, p.title<br>
+      <span class="kw">FROM</span> <span class="tbl">users</span> u<br>
+      <span class="kw">INNER JOIN</span> <span class="tbl">posts</span> p <span class="kw">ON</span> p.user_id <span class="op">=</span> u.id;
+    </div>
+    <div class="code-block">
+      <span class="lbl">anti-join pattern</span>
+      <span class="kw">SELECT</span> * <span class="kw">FROM</span> <span class="tbl">users</span> u<br>
+      <span class="kw">LEFT JOIN</span> <span class="tbl">posts</span> p <span class="kw">ON</span> p.user_id <span class="op">=</span> u.id<br>
+      <span class="kw">WHERE</span> p.id <span class="kw">IS NULL</span>;
+    </div>
+  </div>
+</div>
+
+<!-- users — full width -->
+<div class="section" id="users">
+  <div class="section-head">
+    <span class="dot" style="background:#e5c07b"></span>
+    <h2>users &amp; permissions</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">create user / role</span>
+      <span class="kw">CREATE USER</span> alice <span class="kw">WITH PASSWORD</span> <span class="str">'secret'</span>;<br>
+      <span class="kw">CREATE ROLE</span> readonly;<br>
+      <span class="kw">GRANT</span> readonly <span class="kw">TO</span> alice;
+    </div>
+    <div class="code-block">
+      <span class="lbl">grant / revoke</span>
+      <span class="kw">GRANT ALL PRIVILEGES ON DATABASE</span> mydb <span class="kw">TO</span> alice;<br>
+      <span class="kw">GRANT SELECT ON ALL TABLES IN SCHEMA</span> public <span class="kw">TO</span> alice;<br>
+      <span class="kw">GRANT USAGE ON SCHEMA</span> public <span class="kw">TO</span> alice;<br>
+      <span class="kw">REVOKE ALL ON</span> <span class="tbl">users</span> <span class="kw">FROM</span> alice;
+    </div>
+    <div class="code-block">
+      <span class="lbl">alter user</span>
+      <span class="kw">ALTER USER</span> alice <span class="kw">WITH PASSWORD</span> <span class="str">'newpass'</span>;<br>
+      <span class="kw">ALTER USER</span> alice <span class="kw">WITH SUPERUSER</span>;<br>
+      <span class="kw">ALTER USER</span> alice <span class="kw">WITH NOSUPERUSER</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">default privileges (future tables)</span>
+      <span class="kw">ALTER DEFAULT PRIVILEGES IN SCHEMA</span> public<br>
+      <span class="kw">GRANT SELECT ON TABLES TO</span> readonly;
+    </div>
+  </div>
+</div>
+
+<!-- transactions — full width -->
+<div class="section" id="transactions">
+  <div class="section-head">
+    <span class="dot" style="background:#7dd3db"></span>
+    <h2>transactions</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">basic transaction</span>
+      <span class="kw">BEGIN</span>;<br>
+      <span class="kw">UPDATE</span> <span class="tbl">accounts</span> <span class="kw">SET</span> balance <span class="op">=</span> balance <span class="op">-</span> <span class="num">100</span> <span class="kw">WHERE</span> id <span class="op">=</span> <span class="num">1</span>;<br>
+      <span class="kw">UPDATE</span> <span class="tbl">accounts</span> <span class="kw">SET</span> balance <span class="op">=</span> balance <span class="op">+</span> <span class="num">100</span> <span class="kw">WHERE</span> id <span class="op">=</span> <span class="num">2</span>;<br>
+      <span class="kw">COMMIT</span>; <span class="cmt">-- or ROLLBACK;</span>
+    </div>
+    <div class="code-block">
+      <span class="lbl">savepoints</span>
+      <span class="kw">SAVEPOINT</span> sp1;<br>
+      <span class="cmt">-- ... do work ...</span><br>
+      <span class="kw">ROLLBACK TO</span> sp1; <span class="cmt">-- partial undo</span><br>
+      <span class="kw">RELEASE SAVEPOINT</span> sp1;
+    </div>
+  </div>
+  <table class="ref-table">
+    <tr><td><span class="pill p-blue">READ COMMITTED</span></td><td>default — sees committed data at query start</td></tr>
+    <tr><td><span class="pill p-yellow">REPEATABLE READ</span></td><td>consistent snapshot for whole transaction</td></tr>
+    <tr><td><span class="pill p-red">SERIALIZABLE</span></td><td>strictest — transactions appear sequential</td></tr>
+  </table>
+</div>
+
+<!-- explain — full width -->
+<div class="section" id="explain">
+  <div class="section-head">
+    <span class="dot" style="background:#c792ea"></span>
+    <h2>explain &amp; analyze</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">syntax</span>
+      <span class="kw">EXPLAIN</span> <span class="kw">SELECT</span> * <span class="kw">FROM</span> <span class="tbl">users</span> <span class="kw">WHERE</span> email <span class="op">=</span> <span class="str">'x'</span>;<br>
+      <span class="kw">EXPLAIN ANALYZE</span> <span class="kw">SELECT</span> ...; <span class="cmt">-- actually executes</span><br>
+      <span class="kw">EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)</span> <span class="kw">SELECT</span> ...;
+    </div>
+    <div class="code-block">
+      <span class="lbl">force / disable scan types</span>
+      <span class="kw">SET</span> enable_seqscan <span class="op">=</span> off;  <span class="cmt">-- prefer index scans</span><br>
+      <span class="kw">SET</span> enable_indexscan <span class="op">=</span> off; <span class="cmt">-- prefer seq scans</span><br>
+      <span class="kw">RESET</span> enable_seqscan;         <span class="cmt">-- restore default</span>
+    </div>
+  </div>
+  <table class="ref-table">
+    <tr><td>Seq Scan</td><td>full table scan — consider adding an index</td></tr>
+    <tr><td>Index Scan</td><td>uses index, fetches heap rows</td></tr>
+    <tr><td>Index Only Scan</td><td>covering index — no heap access needed</td></tr>
+    <tr><td>Bitmap Heap Scan</td><td>batched index + heap access</td></tr>
+    <tr><td>Hash Join</td><td>builds hash table — good for large sets</td></tr>
+    <tr><td>Nested Loop</td><td>good when inner set is small</td></tr>
+    <tr><td>cost=X..Y</td><td>startup..total estimated cost units</td></tr>
+    <tr><td>actual time</td><td>real milliseconds — only with ANALYZE</td></tr>
+  </table>
+</div>
+
+<!-- admin queries — full width -->
+<div class="section" id="admin">
+  <div class="section-head">
+    <span class="dot" style="background:#88cc88"></span>
+    <h2>handy admin queries</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">table sizes</span>
+      <span class="kw">SELECT</span> relname,<br>
+      &nbsp;&nbsp;<span class="fn">pg_size_pretty</span>(<span class="fn">pg_total_relation_size</span>(oid))<br>
+      <span class="kw">FROM</span> pg_class <span class="kw">WHERE</span> relkind <span class="op">=</span> <span class="str">'r'</span><br>
+      <span class="kw">ORDER BY</span> <span class="fn">pg_total_relation_size</span>(oid) <span class="kw">DESC</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">active connections</span>
+      <span class="kw">SELECT</span> pid, usename, state, query<br>
+      <span class="kw">FROM</span> pg_stat_activity<br>
+      <span class="kw">WHERE</span> state <span class="op">!=</span> <span class="str">'idle'</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">kill a query</span>
+      <span class="kw">SELECT</span> <span class="fn">pg_cancel_backend</span>(pid);    <span class="cmt">-- graceful</span><br>
+      <span class="kw">SELECT</span> <span class="fn">pg_terminate_backend</span>(pid); <span class="cmt">-- force kill</span>
+    </div>
+    <div class="code-block">
+      <span class="lbl">long running queries</span>
+      <span class="kw">SELECT</span> pid, <span class="fn">now</span>() <span class="op">-</span> query_start <span class="kw">AS</span> dur, query<br>
+      <span class="kw">FROM</span> pg_stat_activity<br>
+      <span class="kw">WHERE</span> state <span class="op">=</span> <span class="str">'active'</span><br>
+      <span class="kw">ORDER BY</span> dur <span class="kw">DESC</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">missing indexes (high seq scans)</span>
+      <span class="kw">SELECT</span> relname, seq_scan, idx_scan<br>
+      <span class="kw">FROM</span> pg_stat_user_tables<br>
+      <span class="kw">WHERE</span> seq_scan <span class="op">></span> idx_scan<br>
+      <span class="kw">ORDER BY</span> seq_scan <span class="kw">DESC</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">unused indexes</span>
+      <span class="kw">SELECT</span> indexrelname, idx_scan<br>
+      <span class="kw">FROM</span> pg_stat_user_indexes<br>
+      <span class="kw">WHERE</span> idx_scan <span class="op">=</span> <span class="num">0</span><br>
+      <span class="kw">ORDER BY</span> schemaname, relname;
+    </div>
+    <div class="code-block">
+      <span class="lbl">table bloat / vacuum</span>
+      <span class="kw">VACUUM ANALYZE</span> <span class="tbl">users</span>;<br>
+      <span class="kw">VACUUM FULL</span> <span class="tbl">users</span>; <span class="cmt">-- exclusive lock, rewrites</span><br>
+      <span class="kw">SELECT</span> relname, n_dead_tup, last_autovacuum<br>
+      <span class="kw">FROM</span> pg_stat_user_tables <span class="kw">ORDER BY</span> n_dead_tup <span class="kw">DESC</span>;
+    </div>
+    <div class="code-block">
+      <span class="lbl">duplicate rows</span>
+      <span class="kw">SELECT</span> email, <span class="fn">count</span>(*)<br>
+      <span class="kw">FROM</span> <span class="tbl">users</span><br>
+      <span class="kw">GROUP BY</span> email<br>
+      <span class="kw">HAVING</span> <span class="fn">count</span>(*) <span class="op">></span> <span class="num">1</span>;
+    </div>
+  </div>
+</div>
+
+<!-- jsonb — full width -->
+<div class="section" id="jsonb">
+  <div class="section-head">
+    <span class="dot" style="background:#f0a070"></span>
+    <h2>jsonb operators</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="split">
+    <table class="ref-table">
+      <tr><td><span class="op">-></span> <span class="str">'key'</span></td><td>get value as json</td></tr>
+      <tr><td><span class="op">->></span> <span class="str">'key'</span></td><td>get value as text</td></tr>
+      <tr><td><span class="op">#></span> <span class="str">'{a,b}'</span></td><td>nested value as json</td></tr>
+      <tr><td><span class="op">#>></span> <span class="str">'{a,b}'</span></td><td>nested value as text</td></tr>
+      <tr><td><span class="op">@></span></td><td>left contains right</td></tr>
+      <tr><td><span class="op">&lt;@</span></td><td>left contained by right</td></tr>
+      <tr><td><span class="op">?</span> <span class="str">'key'</span></td><td>key exists?</td></tr>
+      <tr><td><span class="op">||</span></td><td>concatenate objects</td></tr>
+      <tr><td><span class="op">-</span> <span class="str">'key'</span></td><td>delete key</td></tr>
+    </table>
+    <div class="code-block" style="border-bottom:none">
+      <span class="lbl">examples</span>
+      <span class="kw">SELECT</span> data<span class="op">->></span><span class="str">'name'</span> <span class="kw">FROM</span> <span class="tbl">events</span><br>
+      <span class="kw">WHERE</span> data <span class="op">@></span> <span class="str">'{"type":"login"}'</span>::<span class="typ">jsonb</span>;<br><br>
+      <span class="kw">SELECT</span> data<span class="op">#>></span><span class="str">'{user,email}'</span> <span class="kw">FROM</span> <span class="tbl">events</span>;<br><br>
+      <span class="cmt">-- gin index for @> containment:</span><br>
+      <span class="kw">CREATE INDEX ON</span> <span class="tbl">events</span> <span class="kw">USING</span> gin(data);
+    </div>
+  </div>
+</div>
+
+<!-- conditional — full width -->
+<div class="section" id="conditional">
+  <div class="section-head">
+    <span class="dot" style="background:#6fa8e8"></span>
+    <h2>conditional &amp; casting</h2>
+    <a class="back" href="#top">↑</a>
+  </div>
+  <div class="code-grid">
+    <div class="code-block">
+      <span class="lbl">case expression</span>
+      <span class="kw">CASE</span><br>
+      &nbsp;&nbsp;<span class="kw">WHEN</span> score <span class="op">>=</span> <span class="num">90</span> <span class="kw">THEN</span> <span class="str">'A'</span><br>
+      &nbsp;&nbsp;<span class="kw">WHEN</span> score <span class="op">>=</span> <span class="num">70</span> <span class="kw">THEN</span> <span class="str">'B'</span><br>
+      &nbsp;&nbsp;<span class="kw">ELSE</span> <span class="str">'C'</span><br>
+      <span class="kw">END</span>
+    </div>
+    <div class="code-block">
+      <span class="lbl">null handling</span>
+      <span class="fn">coalesce</span>(val, fallback)  <span class="cmt">-- first non-null</span><br>
+      <span class="fn">nullif</span>(a, b)            <span class="cmt">-- null if a = b</span><br>
+      <span class="fn">greatest</span>(a, b, ...)    <span class="cmt">-- largest non-null</span><br>
+      <span class="fn">least</span>(a, b, ...)       <span class="cmt">-- smallest non-null</span>
+    </div>
+    <div class="code-block">
+      <span class="lbl">casting</span>
+      val<span class="op">::</span><span class="typ">integer</span>           <span class="cmt">-- pg shorthand</span><br>
+      <span class="fn">CAST</span>(val <span class="kw">AS</span> <span class="typ">integer</span>) <span class="cmt">-- sql standard</span><br>
+      <span class="str">'2024-01-01'</span><span class="op">::</span><span class="typ">date</span><br>
+      <span class="str">'{"a":1}'</span><span class="op">::</span><span class="typ">jsonb</span>
+    </div>
+    <div class="code-block">
+      <span class="lbl">pattern matching</span>
+      <span class="kw">WHERE</span> name <span class="kw">LIKE</span>  <span class="str">'alice%'</span>   <span class="cmt">-- case sensitive</span><br>
+      <span class="kw">WHERE</span> name <span class="kw">ILIKE</span> <span class="str">'alice%'</span>   <span class="cmt">-- case insensitive</span><br>
+      <span class="kw">WHERE</span> name <span class="op">~</span>     <span class="str">'^alice'</span>   <span class="cmt">-- regex</span><br>
+      <span class="kw">WHERE</span> name <span class="op">~*</span>    <span class="str">'^alice'</span>   <span class="cmt">-- regex, no case</span>
+    </div>
+  </div>
+</div>
+
+<footer>postgresql.org/docs &nbsp;·&nbsp; explain.dalibo.com &nbsp;·&nbsp; pgdocs.github.io</footer>
+
+</div>
+</body>
+</html>
+
+@endsection
+\ No newline at end of file
diff --git a/routes/web.php b/routes/web.php
index df3f526..d98ed5d 100755
--- a/routes/web.php
+++ b/routes/web.php
@@ -144,9 +144,30 @@ Route::get('/newtab', function() {
     return view('newtab');
 });
 
+//Route::post('/do', function(Request $req) {
+    $file = storage_path('app/todo.json');
+    $items = file_exists($file) ? json_decode(file_get_contents($file), true) ?? [] : [];
+    $op = $req->input('op', 'append');
+
+    if ($op === 'overwrite') {
+        $lines = array_filter(array_map('trim', explode("\n", $req->input('content', ''))));
+        $items = array_values($lines);
+    } elseif ($op === 'delete') {
+        $index = (int) $req->input('index');
+        array_splice($items, $index, 1);
+    } else {
+        // append (default)
+        $lines = array_filter(array_map('trim', explode("\n", $req->input('content', ''))));
+        $items = array_merge($items, $lines);
+    }
+
+    file_put_contents($file, json_encode(array_values($items), JSON_PRETTY_PRINT));
+    return redirect('/do')->with('status', 'List updated.');
+})->middleware('auth');
+
 // Catch-all: only allow specific whitelisted views
 Route::get('/{v}', function($v){
-    $allowed = ['notes', 'kyanite', 'marked', 'v', '4'];
+    $allowed = ['notes', 'kyanite', 'marked', 'v', '4', 'matrix', 'psql', 'do'];
     if (!in_array($v, $allowed)) {
         abort(404);
     }
author	grothedev <grothedev@gmail.com>	2026-04-19 22:51:19 -0500
committer	grothedev <grothedev@gmail.com>	2026-04-19 22:51:19 -0500
commit	2f57affde6641c8e5912e483c234f84608be8eb2 (patch)
tree	c9534f15a1ff96713421c8a106a7ff996a9edb17
parent	df0f054366a81d02b28a5e2ae0d571cf5b153256 (diff)