Experiment 02 — Studio Null

The
Guestbook

A SQL-rendered record of everyone who passed through. Entries are stored in PostgreSQL and assembled into this page on every request. There are 0 signatures.

0Total Entries
0Signed Today
—First Entry
—Most Recent
No entries yet. Be the first.
— Leave your mark

Sign the
guestbook

guestbook.sql
-- ═══════════════════════════════════════════════════════════
--  THE GUESTBOOK — A SQL-rendered visitor record
-- ═══════════════════════════════════════════════════════════

WITH

-- ─── STYLES ──────────────────────────────────────────────────
styles AS (
  SELECT $css$
    @import url('https://fonts.googleapis.com/css2?family=EB+Garamond:ital,wght@0,400;0,500;1,400;1,500&family=Space+Mono:wght@400;700&display=swap');
    :root {
      --bg: #0d0d10; --surface: #13131a; --fg: #f0ede6;
      --muted: #7a7870; --accent: #c8a96e; --border: rgba(200,169,110,0.18);
    }
    *, *::before, *::after { margin:0; padding:0; box-sizing:border-box; }
    html { scroll-behavior:smooth; }
    body { background:var(--bg); color:var(--fg);
           font-family:'EB Garamond',Georgia,serif; font-size:18px; line-height:1.6; }
    a { color:inherit; text-decoration:none; }
    nav { display:flex; justify-content:space-between; align-items:center;
          padding:1.4rem 4rem; border-bottom:1px solid var(--border);
          position:sticky; top:0; background:rgba(13,13,16,0.94);
          backdrop-filter:blur(12px); z-index:100; }
    .logo { font-family:'Space Mono',monospace; font-size:.8rem;
            letter-spacing:.22em; color:var(--accent); }
    .nav-links a { font-family:'Space Mono',monospace; font-size:.65rem;
                   letter-spacing:.14em; margin-left:2rem; color:var(--muted); transition:color .2s; }
    .nav-links a:hover { color:var(--fg); }
    .gb-header { padding:6rem 4rem 4rem; border-bottom:1px solid var(--border); }
    .gb-eye { display:block; font-family:'Space Mono',monospace; font-size:.62rem;
              letter-spacing:.3em; color:var(--accent); text-transform:uppercase; margin-bottom:2rem; }
    .gb-title { font-size:clamp(3rem,7vw,6.5rem); font-weight:400;
                line-height:1.0; letter-spacing:-.03em; margin-bottom:1.5rem; }
    .gb-title em { font-style:italic; color:var(--accent); }
    .gb-sub { font-size:1.2rem; color:var(--muted); max-width:520px; line-height:1.8; }
    .stats-bar { display:grid; grid-template-columns:repeat(4,1fr);
                 border-bottom:1px solid var(--border); }
    .stat { padding:2.2rem 2rem; border-right:1px solid var(--border);
            display:flex; flex-direction:column; gap:.4rem; }
    .stat:last-child { border-right:none; }
    .stat-val { font-size:2.2rem; line-height:1; letter-spacing:-.03em; }
    .stat-lbl { font-family:'Space Mono',monospace; font-size:.58rem;
                letter-spacing:.16em; color:var(--muted); text-transform:uppercase; }
    .stat-mono { font-family:'Space Mono',monospace !important; font-size:1rem !important; }
    .entries { padding:0; }
    .entry { padding:2.2rem 4rem; border-bottom:1px solid var(--border);
             display:grid; grid-template-columns:auto 1fr; gap:3rem; align-items:baseline; }
    .entry:hover { background:var(--surface); }
    .entry-meta { text-align:right; min-width:160px; }
    .entry-num { display:block; font-family:'Space Mono',monospace;
                 font-size:.58rem; letter-spacing:.2em; color:var(--accent); margin-bottom:.4rem; }
    .entry-ts { display:block; font-family:'Space Mono',monospace;
                font-size:.6rem; letter-spacing:.08em; color:var(--muted); line-height:1.6; }
    .entry-name { font-size:1.35rem; letter-spacing:-.01em; display:block; margin-bottom:.35rem; }
    .entry-note { font-size:1rem; color:var(--muted); line-height:1.75; font-style:italic; max-width:600px; }
    .no-entries { padding:6rem 4rem; color:var(--muted); font-size:1.1rem;
                  font-style:italic; border-bottom:1px solid var(--border); }
    .sign-section { padding:5rem 4rem 7rem; max-width:640px; }
    .sign-eye { font-family:'Space Mono',monospace; font-size:.62rem; letter-spacing:.28em;
                color:var(--accent); text-transform:uppercase; display:block; margin-bottom:2rem; }
    .sign-title { font-size:clamp(2rem,4vw,3.2rem); font-weight:400;
                  letter-spacing:-.02em; margin-bottom:2.5rem; }
    .sign-title em { font-style:italic; }
    .field { display:flex; flex-direction:column; gap:.6rem; margin-bottom:1.6rem; }
    .field label { font-family:'Space Mono',monospace; font-size:.6rem;
                   letter-spacing:.18em; color:var(--muted); text-transform:uppercase; }
    .field input, .field textarea { background:var(--surface); border:1px solid var(--border);
      color:var(--fg); font-family:'EB Garamond',serif; font-size:1.1rem;
      padding:.75rem 1rem; outline:none; width:100%; resize:vertical; transition:border-color .2s; }
    .field input::placeholder, .field textarea::placeholder { color:var(--muted); font-style:italic; }
    .field input:focus, .field textarea:focus { border-color:rgba(200,169,110,.45); }
    .field textarea { min-height:100px; }
    .sign-btn { font-family:'Space Mono',monospace; font-size:.72rem; letter-spacing:.14em;
                padding:.85rem 2.2rem; border:1px solid var(--accent); color:var(--accent);
                background:transparent; cursor:pointer; transition:all .25s; }
    .sign-btn:hover { background:var(--accent); color:var(--bg); }
    footer { display:flex; justify-content:space-between; align-items:center;
             padding:1.8rem 4rem; border-top:1px solid var(--border); }
    footer span { font-family:'Space Mono',monospace; font-size:.62rem;
                  letter-spacing:.12em; color:var(--muted); }
    .pg-badge { color:var(--accent) !important; }
    #sql-toggle-btn { font-family:'Space Mono',monospace; font-size:.65rem;
      letter-spacing:.14em; margin-left:1.8rem; color:var(--accent);
      background:none; border:none; cursor:pointer; padding:0; transition:opacity .2s; }
    #sql-toggle-btn:hover { opacity:.7; }
    #sql-panel { position:fixed; inset:0; z-index:999; background:var(--bg);
      display:flex; flex-direction:column;
      transform:translateY(100%); transition:transform .38s cubic-bezier(.4,0,.2,1); overflow:hidden; }
    #sql-panel.open { transform:translateY(0); }
    #sql-panel-header { display:flex; justify-content:space-between; align-items:center;
      padding:1.2rem 4rem; border-bottom:1px solid var(--border);
      background:rgba(13,13,16,.96); backdrop-filter:blur(12px); flex-shrink:0; }
    #sql-panel-filename { font-family:'Space Mono',monospace; font-size:.65rem;
      letter-spacing:.22em; color:var(--accent); text-transform:uppercase; }
    #sql-panel-close { font-family:'Space Mono',monospace; font-size:.65rem;
      letter-spacing:.14em; color:var(--muted); background:none; border:none;
      cursor:pointer; transition:color .2s; }
    #sql-panel-close:hover { color:var(--fg); }
    #sql-panel-body { flex:1; overflow-y:auto; padding:2.5rem 4rem 4rem; }
    #sql-panel-pre { font-family:'Space Mono',monospace; font-size:.72rem; line-height:1.85;
      white-space:pre-wrap; word-break:break-word; color:#bbb; background:none; border:none; padding:0; margin:0; }
    .sk { color:#c8a96e; font-weight:700; }
    .ss { color:#a8c97f; }
    .sc { color:#4a5060; font-style:italic; }
    .sn { color:#79b8e0; }

    @media (max-width:768px) {
      nav { padding:1.2rem 1.5rem; }
      .nav-links a { margin-left:1rem; font-size:.58rem; }
      #sql-toggle-btn { margin-left:1rem; font-size:.58rem; }
      #sql-panel-header { padding:1rem 1.5rem; }
      #sql-panel-body { padding:1.5rem; }
      #sql-panel-pre { font-size:.6rem; }
      .gb-header { padding:3.5rem 1.5rem 2.5rem; }
      .stats-bar { grid-template-columns:1fr 1fr; }
      .stat { padding:1.8rem 1.2rem; }
      .stat:nth-child(2) { border-right:none; }
      .stat:nth-child(3) { border-right:1px solid var(--border); border-top:1px solid var(--border); }
      .stat:nth-child(4) { border-right:none; border-top:1px solid var(--border); }
      .entry { grid-template-columns:1fr; gap:0; padding:2rem 1.5rem; }
      .entry-meta { text-align:left; display:flex; gap:1.5rem; align-items:baseline; margin-bottom:.8rem; }
      .entry-ts { display:inline; }
      .sign-section { padding:3rem 1.5rem 5rem; }
      footer { padding:1.5rem; flex-direction:column; gap:.6rem; text-align:center; }
    }
  $css$ AS css
),

-- ─── STATS ───────────────────────────────────────────────────
gb_stats AS (
  SELECT
    count(*)::int                                        AS total,
    count(*) FILTER (
      WHERE created_at > now() - interval '24 hours'
    )::int                                               AS today,
    coalesce(to_char(min(created_at) AT TIME ZONE 'UTC', 'DD Mon YYYY'), '—') AS first_date,
    coalesce(to_char(max(created_at) AT TIME ZONE 'UTC', 'DD Mon YYYY'), '—') AS last_date
  FROM guestbook
),

-- ─── ENTRIES ─────────────────────────────────────────────────
entry_rows AS (
  SELECT id, name, note, created_at,
    row_number() OVER (ORDER BY created_at ASC) AS entry_num
  FROM guestbook
  ORDER BY created_at DESC
),
entries_html AS (
  SELECT coalesce(
    string_agg(
      '<div class="entry">'
      || '<div class="entry-meta">'
      || '<span class="entry-num">#' || lpad(entry_num::text, 4, '0') || '</span>'
      || '<span class="entry-ts">'
      || to_char(created_at AT TIME ZONE 'UTC', 'DD Mon YYYY')
      || '<br>'
      || to_char(created_at AT TIME ZONE 'UTC', 'HH24:MI')
      || ' UTC</span></div>'
      || '<div class="entry-body">'
      || '<span class="entry-name">'
      || replace(replace(replace(replace(name,'&','&amp;'),'<','&lt;'),'>','&gt;'),'"','&quot;')
      || '</span>'
      || CASE
           WHEN note IS NOT NULL AND trim(note) != '' THEN
             '<p class="entry-note">'
             || replace(replace(replace(replace(trim(note),'&','&amp;'),'<','&lt;'),'>','&gt;'),'"','&quot;')
             || '</p>'
           ELSE ''
         END
      || '</div></div>',
      '' ORDER BY created_at DESC
    ),
    '<div class="no-entries">No entries yet. Be the first.</div>'
  ) AS html
  FROM entry_rows
),

-- ─── NAV ─────────────────────────────────────────────────────
nav AS (
  SELECT
    '<nav>'
    || '<span class="logo">THE GUESTBOOK</span>'
    || '<div class="nav-links">'
    || '<a href="/">&larr; Studio Null</a>'
    || '<a href="/chronolith">Chronolith</a>'
    || '<button id="sql-toggle-btn" onclick="toggleSQL()">[ sql ]</button>'
    || '</div>'
    || '</nav>'
  AS html
),

-- ─── HEADER ──────────────────────────────────────────────────
header AS (
  SELECT
    '<div class="gb-header">'
    || '<span class="gb-eye">Experiment 02 &mdash; Studio Null</span>'
    || '<h1 class="gb-title">The<br><em>Guestbook</em></h1>'
    || '<p class="gb-sub">A SQL-rendered record of everyone who passed through. '
    || 'Entries are stored in PostgreSQL and assembled into this page on every request. '
    || 'There are ' || total || ' signatures.</p>'
    || '</div>'
  AS html
  FROM gb_stats
),

-- ─── STATS BAR ───────────────────────────────────────────────
stats_bar AS (
  SELECT
    '<div class="stats-bar">'
    || '<div class="stat"><span class="stat-val">' || total
    || '</span><span class="stat-lbl">Total Entries</span></div>'
    || '<div class="stat"><span class="stat-val">' || today
    || '</span><span class="stat-lbl">Signed Today</span></div>'
    || '<div class="stat"><span class="stat-val stat-mono">' || first_date
    || '</span><span class="stat-lbl">First Entry</span></div>'
    || '<div class="stat"><span class="stat-val stat-mono">' || last_date
    || '</span><span class="stat-lbl">Most Recent</span></div>'
    || '</div>'
  AS html
  FROM gb_stats
),

-- ─── SIGN FORM ───────────────────────────────────────────────
sign_form AS (
  SELECT
    '<div class="sign-section" id="sign">'
    || '<span class="sign-eye">&mdash; Leave your mark</span>'
    || '<h2 class="sign-title">Sign the<br><em>guestbook</em></h2>'
    || '<form method="POST" action="/guestbook/sign">'
    || '<div class="field"><label for="gb-name">Your name</label>'
    || '<input id="gb-name" name="name" type="text" placeholder="What do they call you?" maxlength="80" required autocomplete="off"></div>'
    || '<div class="field"><label for="gb-note">A note (optional)</label>'
    || '<textarea id="gb-note" name="note" placeholder="Something brief. Or not." maxlength="500"></textarea></div>'
    || '<button type="submit" class="sign-btn">Sign the guestbook &rarr;</button>'
    || '</form></div>'
  AS html
),

-- ─── FOOTER ──────────────────────────────────────────────────
footer AS (
  SELECT
    '<footer>'
    || '<span>The Guestbook &nbsp;&middot;&nbsp; Studio Null</span>'
    || '<span class="pg-badge">&laquo; Generated live by PostgreSQL '
    || current_setting('server_version')
    || ' in __QUERY_MS__ms &raquo;</span>'
    || '</footer>'
  AS html
),

-- ─── ASSEMBLE ────────────────────────────────────────────────
page AS (
  SELECT
    '<!DOCTYPE html><html lang="en"><head>'
    || '<meta charset="UTF-8">'
    || '<meta name="viewport" content="width=device-width,initial-scale=1.0">'
    || '<title>The Guestbook &mdash; Studio Null</title>'
    || '<link rel="icon" type="image/svg+xml" href="/favicon.svg">'
    || '<style>' || styles.css || '</style>'
    || '</head><body>'
    || nav.html
    || header.html
    || stats_bar.html
    || '<div class="entries">' || entries_html.html || '</div>'
    || sign_form.html
    || footer.html
    || '__SQL_PANEL__'
    || '</body></html>'
  AS html
  FROM styles, nav, header, stats_bar, entries_html, sign_form, footer
)

SELECT html FROM page;