Astro + Turso Live Comments

Experimental live collections · AI-adjacent data layer

Overview

I wanted a lightweight comment system for my blog posts without outsourcing to a 3rd‑party SaaS. The goals:

1. Keep ownership of data (SQLite/Turso)
2. Support a simple progressive enhancement path (works w/ plain form POST)
3. Add a client UX (modal form) that feels native to the site
4. Experiment with Astro’s experimental live collections to hydrate fresh comments
5. Allow future extension (notifications, edits, moderation)

This post documents the first slice: schema additions, loader, triggers, and UI pieces.

High Level Architecture

flowchart TD
  U[User] --> |Opens modal / submits form| F[Comment Modal Form]
  F --> |POST /api/comments| API[(Edge / Server Endpoint)]
  API --> DB[(Turso / libSQL)]
  DB --> |INSERT trigger writes notification| N[notifications table]
  LiveLoader[[Astro Live Collection Loader]] --> DB
  LiveLoader --> N
  LiveLoader --> |entries[]| Page[Blog Post Page]
  Page --> |Filter by slug| Visible[Rendered Comments]

Database Changes

Two main tables relevant to comments (simplified view):

-- comments table (already existed / added for feature)
CREATE TABLE IF NOT EXISTS Comments (
  id INTEGER PRIMARY KEY,
  commentText TEXT NOT NULL,
  postSlug TEXT NOT NULL,
  postType TEXT NOT NULL,       -- e.g. 'blog'
  userID INTEGER,               -- foreign key to User (optional / nullable)
  created_at TEXT DEFAULT (CURRENT_TIMESTAMP)
);

-- notifications table (used for polling-based 'realtime')
CREATE TABLE IF NOT EXISTS notifications (
  id INTEGER PRIMARY KEY,
  channel TEXT NOT NULL,
  event TEXT NOT NULL,
  created_at TEXT DEFAULT (CURRENT_TIMESTAMP)
);

And triggers (excerpt) to publish comment events:

CREATE TRIGGER new_comment_trigger
AFTER INSERT ON comments
BEGIN
  INSERT INTO notifications (channel, event)
  VALUES ('comments_' || NEW.postType, 'new-comment');
END;

Optional similar triggers exist for updates / deletions.

Update & Delete Triggers (Future Ready)

-- (Optional) update trigger
CREATE TRIGGER IF NOT EXISTS update_comment_trigger
AFTER UPDATE ON comments
BEGIN
  INSERT INTO notifications (channel, event)
  VALUES ('comments_' || NEW.postType, 'comment-updated');
END;

-- (Optional) delete trigger
CREATE TRIGGER IF NOT EXISTS delete_comment_trigger
AFTER DELETE ON comments
BEGIN
  INSERT INTO notifications (channel, event)
  VALUES ('comments_' || OLD.postType, 'comment-deleted');
END;

Type Layer (Excerpt)

// db/types.ts
export type Comments = {
  id: number;
  commentText: string;
  postSlug: string;
  postType: string;
  userID: number;
  createdAt: Date;
};

Live Collection Loader

Instead of per‑page SQL queries, I load all comments once into an Astro live collection, then filter in the rendering layer. This lets the collection grow over time (important for how current experimental live collections cache). Excerpt:

// src/utils/fetchFromTurso.ts (excerpt)
export const tursoCommentLoader = (config: { postType?: string }) => ({
  name: 'turso-comments-with-users',
  async loadCollection() {
    const sql = `SELECT c.id, c.commentText, c.postSlug, c.postType, c.userID, c.created_at, u.name as userName
                 FROM Comments c
                 LEFT JOIN User u ON c.userID = u.id
                 ORDER BY c.created_at DESC`;
    const result = await turso.execute({ sql, args: [] });
    return result.rows.map(row => ({
      id: String(row.id),
      data: {
        id: String(row.id),
        content: String(row.commentText).replace(/^['"]|['"]$/g, ''),
        created_at: new Date(String(row.created_at)),
        author: String(row.userName || `User ${row.userID}`),
        postSlug: String(row.postSlug),
        postType: String(row.postType),
        userID: Number(row.userID || 0)
      }
    }));
  },
  // loadEntry + watch() (polling w/ notifications) also implemented
});

Polling & Notifications

The watch generator polls every 10 minutes and also prunes stale notification rows. True push (web sockets / HTTP streaming) could come later; this is a pragmatic, low‑infrastructure starting point.

Frontend Components

UI Snapshots

1. Modal Form (CommentModal.astro)

Client‑side progressive enhancement: a hidden overlay toggled by a button. On submit it posts to /api/comments (simple form POST). Name is optional.

<form method="POST" action="/api/comments" class="comment-form">
  <input type="hidden" name="postSlug" value={postSlug} />
  <input type="hidden" name="postType" value={postType} />
  <input type="text" name="userName" placeholder="Your name (optional)" />
  <textarea name="content" required />
  <button type="submit">Post Comment</button>
</form>

2. Display (PostComments.astro / LiveComments.astro)

These pull the live collection, filter by postSlug, map to render blocks, and show counts. Fallback logic exists if the experimental API returns an error (direct loader invocation).

---
import PostComments from "@components/PostComments.astro";
const slug = Astro.props.slug;
---
<PostComments postSlug={slug} maxComments={12} />

PostComments.astro (excerpt):

// Fetch ALL then filter
const result = await getLiveCollection("comments");
if (!result || Array.from(result).length === 0) {
  const { tursoCommentLoader } = await import('../utils/fetchFromTurso.js');
  const loader = tursoCommentLoader({postType: 'blog'});
  allComments = await loader.loadCollection();
} else {
  allComments = Array.from(result);
}
filteredComments = filterCommentsByPost(allComments, postSlug);

3. Filtering Helper

export function filterCommentsByPost(all, slug) {
  return all.filter(entry => entry.data.postSlug === slug);
}

4. Watch Generator (Excerpt)

async *watch() {
  let lastNotificationId = 0;
  // establish starting point
  const maxResult = await turso.execute({
    sql: 'SELECT MAX(id) as max_id FROM notifications', args: []
  });
  lastNotificationId = Number(maxResult.rows[0]?.max_id || 0);
  while (true) {
    const result = await turso.execute({
      sql: 'SELECT id, channel, event, created_at FROM notifications WHERE id > ? ORDER BY id ASC',
      args: [lastNotificationId]
    });
    if (result.rows.length) {
      for (const row of result.rows) {
        lastNotificationId = Number(row.id);
        yield { type: 'reload', event: String(row.event || 'comment-update') };
      }
      await turso.execute({
        sql: "DELETE FROM notifications WHERE created_at < datetime('now','-1 hour')",
        args: []
      });
    }
    await new Promise(r => setTimeout(r, 600000)); // 10m
  }
}

Data Flow Summary

1. User opens modal → submits form
2. API endpoint validates & inserts row into Comments
3. DB trigger writes a notifications row
4. Live collection (on next poll) reloads / yields change → page updates
5. UI filters comments for the current post slug

Why Not Real Push Yet?

Keeping surface area small while validating schema + UX. Once stable I can explore:

• Websocket fan‑out service
• Turso change data capture (if/when exposed)
• Server‑Sent Events (SSE) endpoint wrapping notifications polling

Lessons / Gotchas

Next Steps

• Add moderation / spam protection (honeypot + rate limit)
• Edit / delete support (already scaffolded in triggers)
• Pagination or virtual list if volume grows
• Surface comment counts in blog index cards

Closing Thoughts

The combination of Turso + Astro experimental live collections gives me a good enough pseudo‑realtime pipeline with very little infra. The abstraction boundary (one loader + small helpers) keeps future iteration easy.

End of initial implementation log. Updates will append here as the system evolves.