Unlayer
Developer

Send a Weekly Account-Activity Digest from Postgres with React and Resend

October 1, 2026Comments
A week of activity, one useful email
Share

Turn a week of account activity into a useful email: a greeting, a total, the latest activity and a link back to the product. This tutorial connects PostgreSQL, a code-first Unlayer Elements template and Resend, then adds a weekly GitHub Actions trigger and a durable record for retries.

The implementation is deliberately small-cohort and single-worker. It sends an explicit empty-week message, limits the visible activity list, and keeps the original payload when a send needs another attempt. Provider acceptance is recorded separately from delivery; this is not an exactly-once delivery system.

Bring a defined week of account activity into one digest. Conceptual illustration.

What belongs to each layer

Postgres activity → closed UTC week → Elements HTML and plain-text companion → saved payload → Resend submission → recorded acceptance or review. The template formats content; your application owns memberships, consent, account authorization and notification settings. GitHub supplies the trigger, not a durable delivery queue. This uses the Elements library, not the commercial embedded visual editor.

1. Set up the worker

Use Node.js 24, npm, an empty PostgreSQL database with psql available, and a database connection that supports session-level locks. Sending also requires a Resend API key, a verified sender domain and an authorized recipient. Use a direct connection or session pooler, not transaction pooling. Start with fictional data; keep credentials out of source control.

The example assumes an existing authenticated SaaS app. Replace the activity and notification-settings paths in the worker with your own routes. Those routes must check account membership on every request; a link containing an account ID is not authorization. Your preference screen must save opted_in for the signed-in member, and your email-verification flow must own verified.

mkdir weekly-digest
cd weekly-digest
npm init -y
npm pkg set type=module
npm install --save-exact @unlayer/react-elements react@18 react-dom@18 pg resend
npm install --save-dev --save-exact typescript tsx @types/node@24 @types/react@18 @types/react-dom@18 @types/pg

Commit package.json and the resulting package-lock.json; the scheduled worker uses npm ci. Elements requires React and React DOM 18 or later. Add node_modules/, out/ and .env to .gitignore. Preview files contain account content, so keep them private. Create tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "jsx": "react-jsx",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "allowJs": true,
    "noEmit": true
  },
  "include": ["worker.ts", "email.tsx", "window.mjs"]
}

The automatic JSX runtime avoids a React-is-not-defined error. NodeNext uses the package module setting; the local .js import in worker.ts refers to the corresponding TypeScript source. allowJs includes the small window helper.

2. Store activity, preferences and send records

Create schema.sql:

CREATE TABLE digest_accounts (
  id text PRIMARY KEY CHECK (id ~ '^[a-z0-9-]{1,64}$'),
  name text CHECK (length(name) <= 120)
);
CREATE TABLE digest_members (
  id text PRIMARY KEY CHECK (id ~ '^[a-z0-9-]{1,64}$'),
  account_id text NOT NULL REFERENCES digest_accounts(id),
  email text NOT NULL, name text CHECK (length(name) <= 120),
  opted_in boolean NOT NULL DEFAULT false,
  verified boolean NOT NULL DEFAULT false
);
CREATE TABLE digest_activity (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id text NOT NULL REFERENCES digest_accounts(id),
  occurred_at timestamptz NOT NULL,
  label text NOT NULL CHECK (length(label) BETWEEN 1 AND 300)
);
CREATE INDEX ON digest_activity (account_id, occurred_at DESC, id);
CREATE TABLE digest_sends (
  id uuid PRIMARY KEY,
  account_id text NOT NULL, member_id text NOT NULL, week_end date NOT NULL,
  template_version text NOT NULL, payload jsonb NOT NULL,
  state text NOT NULL DEFAULT 'ready'
    CHECK (state IN ('ready','accepted','review','cancelled')),
  first_attempt_at timestamptz, attempts integer NOT NULL DEFAULT 0,
  provider_id text, last_error text,
  created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  UNIQUE (account_id, member_id, week_end)
);

The unique account/member/week key prevents creating another send record for the same window. The payload stores the exact sender, recipient, subject and bodies. Restrict access and apply your retention policy: it is a copy of customer content, not an anonymous job log. All senders must use the same advisory-lock key and database. Create seed.sql:

-- Fictional data only; run once in an empty isolated database.
INSERT INTO digest_accounts VALUES ('busy', 'Example & Co'), ('quiet', '');
INSERT INTO digest_members VALUES
  ('demo-busy', 'busy', 'busy@example.net', 'Alex', true, false),
  ('demo-quiet', 'quiet', 'quiet@example.net', NULL, true, false);
INSERT INTO digest_activity (account_id, occurred_at, label)
SELECT 'busy', '2026-09-21T00:00:00Z'::timestamptz + n * interval '1 hour',
       'Task ' || n || ' completed'
FROM generate_series(0, 11) AS n;
INSERT INTO digest_activity (account_id, occurred_at, label) VALUES
 ('busy', '2026-09-20T23:59:59Z', 'Before the reporting window'),
 ('busy', '2026-09-28T00:00:00Z', 'Belongs to the following week');

The seed contains an active account, a quiet account, missing display names and events immediately outside the window. Both recipients are deliberately unverified, so send mode skips them. Apply these files once to an empty development database:

# Set DATABASE_URL securely in your shell first.
psql "$DATABASE_URL" -v ON_ERROR_STOP=on -f schema.sql -f seed.sql

3. Define the reporting week

Create window.mjs:

const WEEK = 7 * 24 * 60 * 60 * 1000;
export function utcWeek(weekEnd) {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(weekEnd)) throw Error('Use YYYY-MM-DD');
  const end = new Date(`${weekEnd}T00:00:00.000Z`);
  if (!Number.isFinite(+end) || end.toISOString().slice(0, 10) !== weekEnd
      || end.getUTCDay() !== 1) throw Error('Expected a valid Monday');
  return { start: new Date(+end - WEEK).toISOString(), end: end.toISOString() };
}
export function latestMonday(now = new Date()) {
  const day = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
  day.setUTCDate(day.getUTCDate() - (day.getUTCDay() + 6) % 7);
  return day.toISOString().slice(0, 10);
}

Each digest covers Monday 00:00 UTC through the following Monday, excluding the end instant. Supply week_end explicitly for recovery. An ordinary scheduled run chooses the most recent Monday; it does not automatically find missed historical weeks. This is a UTC business rule, not each recipient’s local timezone.

4. Build the React email

Create email.tsx:

import assert from 'node:assert/strict';
import { Email, Row, Column, ColumnLayouts, Heading, Paragraph,
  Button, renderToHtmlParts } from '@unlayer/react-elements';

export type Digest = {
  account: string; name: string; start: string; end: string; total: number;
  items: { id: string; label: string; occurred_at: string }[];
};
const entities: Record<string, string> = {
  '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;', "'": '&#39;',
};
const escape = (s: string) => s.replace(/[&<>"']/g, c => entities[c]!);

export function renderDigest(d: Digest, activityUrl: string, prefsUrl: string) {
  assert.ok(Number.isSafeInteger(d.total) && d.total >= d.items.length);
  const summary = d.total ? `${d.total} activities recorded.` : 'No activity this week.';
  const range = `${d.start.slice(0, 10)} to ${d.end.slice(0, 10)} (end exclusive, UTC)`;
  const lines = d.items.map(x => `${new Date(x.occurred_at).toISOString()}: ${x.label}`);
  const remaining = `${d.total - d.items.length} more activities in your workspace.`;
  const tree = <Email contentWidth="600px" backgroundColor="#f4f4f4">
    <Row layout={ColumnLayouts.OneColumn} backgroundColor="#ffffff" padding="24px">
      <Column>
        <Heading headingType="h1" fontSize="24px" color="#041e39">Your weekly activity</Heading>
        <Paragraph html={`Hi ${escape(d.name)}, here's the week for ${escape(d.account)}.`} />
        <Paragraph html={escape(range)} />
        <Paragraph html={escape(summary)} />
        {lines.map((line, i) => <Paragraph key={d.items[i].id} html={escape(line)} />)}
        {d.total > d.items.length && <Paragraph html={escape(remaining)} />}
        <Button href={activityUrl} backgroundColor="#041e39" color="#ffffff">View account activity</Button>
        <Paragraph html={`Manage weekly digest preferences: <a href="${escape(prefsUrl)}">notification settings</a>`} />
      </Column>
    </Row>
  </Email>;
  const { head, body } = renderToHtmlParts(tree);
  const html = `<!DOCTYPE html><html><head>${head}</head>${body}</html>`;
  const text = [`Hi ${d.name}, here's the week for ${d.account}.`, range, summary,
    ...lines, ...(d.total > d.items.length ? [remaining] : []),
    `View account activity: ${activityUrl}`, `Notification settings: ${prefsUrl}`].join('\n\n');
  return { subject: `Your weekly activity: ${d.end.slice(0, 10)}`, html, text };
}

Keep Email → Row → Column → content. Pass the direct Email tree to renderToHtmlParts and compose head/body as the rendering API documents. The plain-text companion uses the same data and retains both destination URLs. Values entering Paragraph.html are escaped as text; this helper is not a rich-HTML sanitizer. The application, not user input, supplies the HTTPS origin.

A busy week shows the total, a bounded recent list and a remaining-activity line. A quiet week keeps the greeting, date range and links but substitutes an explicit no-activity message. Missing account and member names receive defaults in the worker. No external images or fonts are needed in this template.

5. Query, preview and submit the saved payload

Create worker.ts:

import assert from 'node:assert/strict';
import { randomUUID } from 'node:crypto';
import { mkdir, writeFile } from 'node:fs/promises';
import { setTimeout as wait } from 'node:timers/promises';
import pg from 'pg';
import { Resend } from 'resend';
import { utcWeek, latestMonday } from './window.mjs';
import { renderDigest } from './email.js';
import type { Digest } from './email.js';

type Member = { id: string; account_id: string; email: string; name: string | null; account: string | null };
type Payload = { from: string; to: string[]; subject: string; html: string; text: string };
const env = (key: string) => { const v = process.env[key]; if (!v) throw Error(`Set ${key}`); return v; };
const mode = process.argv[2];
assert.ok(mode === 'preview' || mode === 'send', 'Use preview or send');
const weekEnd = process.argv[3] || latestMonday();
const window = utcWeek(weekEnd);
assert.ok(+new Date(window.end) <= Date.now(), 'The reporting week must be closed');
const origin = new URL(env('APP_ORIGIN'));
assert.ok(origin.protocol === 'https:' && !origin.username && !origin.password
  && origin.pathname === '/' && !origin.search && !origin.hash, 'APP_ORIGIN must be an HTTPS origin');
const from = mode === 'send' ? env('MAIL_FROM') : 'Preview <preview@example.com>';
const resend = mode === 'send' ? new Resend(env('RESEND_API_KEY')) : null;
const db = new pg.Client({ connectionString: env('DATABASE_URL'),
  connectionTimeoutMillis: 10000, statement_timeout: 30000 });
// Never keep sending after losing the session that owns the lock.
db.on('error', () => { console.error('Database session lost; reconcile outstanding attempts'); process.exit(1); });
await db.connect();
let locked = false;
let failed = false;
try {
  if (mode === 'send') {
    locked = (await db.query('SELECT pg_try_advisory_lock(71421, 1) AS ok')).rows[0].ok;
    if (!locked) throw Error('Another digest sender is running');
  }
  const members = (await db.query<Member>(`
    SELECT m.id, m.account_id, m.email, m.name, a.name AS account
    FROM digest_members m JOIN digest_accounts a ON a.id=m.account_id
    WHERE m.opted_in AND ($1::boolean OR m.verified) ORDER BY m.id`, [mode === 'preview'])).rows;
  await mkdir('out', { recursive: true });
  for (const m of members) {
    const identity = [m.account_id, m.id, weekEnd];
    let record = mode === 'send' ? (await db.query(`SELECT * FROM digest_sends
      WHERE account_id=$1 AND member_id=$2 AND week_end=$3::date`, identity)).rows[0] : null;
    if (record && record.state !== 'ready') {
      if (record.state === 'review') failed = true;
      console.log(`${m.id}: ${record.state}`); continue;
    }
    if (!record) {
      // Count and bounded list share one PostgreSQL statement snapshot.
      const r = (await db.query(`
        SELECT count(*)::text AS total,
          COALESCE((SELECT jsonb_agg(t ORDER BY t.occurred_at DESC, t.id::bigint)
            FROM (SELECT id::text AS id, label, occurred_at FROM digest_activity
              WHERE account_id=$1 AND occurred_at >= $2::timestamptz
                AND occurred_at < $3::timestamptz
              ORDER BY occurred_at DESC, digest_activity.id LIMIT 10) t), '[]'::jsonb) AS items
        FROM digest_activity WHERE account_id=$1
          AND occurred_at >= $2::timestamptz AND occurred_at < $3::timestamptz`,
        [m.account_id, window.start, window.end])).rows[0];
      const d: Digest = { account: m.account?.trim() || 'Your workspace',
        name: m.name?.trim() || 'there', ...window, total: Number(r.total), items: r.items };
      const base = `${origin.origin}/accounts/${encodeURIComponent(m.account_id)}`;
      const payload: Payload = { from, to: [m.email],
        ...renderDigest(d, `${base}/activity`, `${base}/settings/notifications`) };
      if (mode === 'preview') {
        const path = `out/${m.id}-${weekEnd}`;
        await writeFile(`${path}.html`, payload.html);
        await writeFile(`${path}.txt`, payload.text);
        console.log(`${m.id}: total=${d.total}; shown=${d.items.length}`);
        continue;
      }
      await db.query(`INSERT INTO digest_sends
        (id, account_id, member_id, week_end, template_version, payload)
        VALUES ($1,$2,$3,$4::date,'digest-v1',$5::jsonb)
        ON CONFLICT (account_id,member_id,week_end) DO NOTHING`,
        [randomUUID(), ...identity, JSON.stringify(payload)]);
      record = (await db.query(`SELECT * FROM digest_sends
        WHERE account_id=$1 AND member_id=$2 AND week_end=$3::date`, identity)).rows[0];
    }
    // Recheck membership, opt-in and exact recipient immediately before each attempt.
    await wait(1100);
    const payload = record.payload as Payload;
    const allowed = await db.query(`SELECT 1 FROM digest_members
      WHERE id=$1 AND account_id=$2 AND opted_in AND verified AND email=$3`,
      [m.id, m.account_id, payload.to[0]]);
    if (!allowed.rowCount) {
      await db.query("UPDATE digest_sends SET state='cancelled' WHERE id=$1", [record.id]);
      continue;
    }
    const attempt = await db.query(`UPDATE digest_sends
      SET first_attempt_at=COALESCE(first_attempt_at,clock_timestamp()), attempts=attempts+1
      WHERE id=$1 AND state='ready' AND (first_attempt_at IS NULL
        OR first_attempt_at > clock_timestamp() - interval '23 hours') RETURNING id`, [record.id]);
    if (!attempt.rowCount) {
      await db.query("UPDATE digest_sends SET state='review' WHERE id=$1 AND state='ready'", [record.id]);
      failed = true; console.error(`${m.id}: retry cutoff reached; reconcile manually`); continue;
    }
    try {
      const { data, error } = await resend!.emails.send(payload, { idempotencyKey: `digest/${record.id}` });
      if (error || !data?.id) throw Error(error?.name || 'No provider acknowledgement');
      await db.query("UPDATE digest_sends SET state='accepted', provider_id=$2, last_error=NULL WHERE id=$1",
        [record.id, data.id]);
      console.log(`${m.id}: provider accepted`);
    } catch (error) {
      const message = error instanceof Error ? error.message : 'Unknown send error';
      // Do not log the body, recipient address or credentials.
      await db.query('UPDATE digest_sends SET last_error=$2 WHERE id=$1', [record.id, message.slice(0, 160)]);
      console.error(`${m.id}: attempt unresolved; inspect before retrying`); failed = true;
    }
  }
  if (failed) process.exitCode = 1;
} finally {
  try { if (locked) await db.query('SELECT pg_advisory_unlock(71421, 1)'); }
  finally { await db.end(); }
}

The activity count and limited list share one SELECT snapshot, scoped by account_id and parameterized timestamps. The count includes the whole week, not just the displayed items. Activity is frozen per recipient when that recipient’s send record is first created; different recipients are not promised an identical account-wide snapshot. Late-arriving events do not rewrite an existing payload. If that distinction matters, introduce a separate immutable account/week snapshot before fan-out.

The advisory lock serializes cooperating workers. A unique record and the provider key cover different failure modes: the database stops new records; the same Resend key and unchanged payload make short-window retries possible. The worker stops on database-session loss rather than continuing without its lock.

Resend retains idempotency keys for 24 hours. This example stops automatic eligibility after 23 hours measured from the first attempted request, leaving a conservative margin. A crash between provider acceptance and saving provider_id remains ambiguous. Inspect the provider record before any later manual decision; never reset the timestamp, delete the row or generate a fresh key merely to clear an error.

Membership, verified address and opt-in are checked again immediately before submission. There is still a race between that check and the external request. An email already accepted cannot be recalled by changing a preference. This is an application tradeoff, not an Elements security guarantee.

6. Preview first, then enable an authorized send

# DATABASE_URL is already set. This demo origin does not provide app routes.
export APP_ORIGIN=https://app.example.com
npx tsx worker.ts preview 2026-09-28

Open the HTML and text files under out/ privately. Compare the busy and quiet accounts, the exclusive end boundary, fallback names and the full activity/settings destinations. Preview mode neither creates send records nor calls Resend. Replace APP_ORIGIN with your real HTTPS application origin and make sure both routes work for an authenticated account member.

For a controlled send, replace only the intended development member’s email with an address you own or are authorized to use; mark it verified through your trusted setup. Leave other fixture members unverified. Set MAIL_FROM to an address on your verified sending domain and RESEND_API_KEY through your shell’s secret mechanism, then run:

npx tsx worker.ts send 2026-09-28

An accepted record means Resend acknowledged the request, not that the recipient received or opened it. Inspect the provider dashboard for subsequent delivery or bounce events. Retry an unresolved attempt by running the same week again only after examining the error and while inside the cutoff. Accepted and cancelled records are skipped. A rejected address or payload needs investigation, not a rapid retry loop.

7. Add the weekly trigger

Place the files at the repository root. Commit the lockfile, and create .github/workflows/digest.yml with the following contents. Store DATABASE_URL, RESEND_API_KEY and MAIL_FROM as repository secrets; store APP_ORIGIN as a repository variable. Use a persistent database reachable securely from the runner. Do not seed or recreate it on each run.

name: Weekly account digest
on:
  schedule:
    - cron: '17 9 * * 1'
  workflow_dispatch:
    inputs:
      week_end:
        description: 'Exclusive UTC Monday (YYYY-MM-DD)'
        required: true
        type: string
permissions:
  contents: read
concurrency:
  group: account-digest
  cancel-in-progress: false
jobs:
  send:
    runs-on: ubuntu-latest
    timeout-minutes: 20
    steps:
      - uses: actions/checkout@v7
      - uses: actions/setup-node@v7
        with:
          node-version: '24'
          package-manager-cache: false
      - run: npm ci
      - run: npx tsc --noEmit
      - run: npx tsx worker.ts send "$WEEK_END"
        env:
          WEEK_END: ${{ inputs.week_end }}
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          RESEND_API_KEY: ${{ secrets.RESEND_API_KEY }}
          MAIL_FROM: ${{ secrets.MAIL_FROM }}
          APP_ORIGIN: ${{ vars.APP_ORIGIN }}

The cron requests Monday 09:17 UTC. GitHub warns that scheduled jobs can be delayed or dropped and run only from the default branch; inactive public repositories can lose their schedule. Concurrency reduces overlap but is not a guaranteed queue. Monitor missed and failed runs. Use Run workflow with the explicit ending Monday to recover a missed window, and remember the send-record retry cutoff. Limit the cohort so the job fits its timeout; for larger audiences, replace the all-members scan with pagination and a durable job queue.

Troubleshooting and next steps

No message is submitted: check opted_in, verified and existing record state. Preview intentionally permits unverified fixture members. Wrong activity: inspect the UTC range and account mapping; do not remove tenant predicates. Another sender is running: let it finish or confirm its session ended; do not switch lock keys. Idempotency conflict: preserve the original payload and investigate changes. Retry cutoff reached: reconcile with Resend before deciding what happened. Database connection fails: follow your host’s TLS/network instructions rather than disabling certificate checks.

Keep the flow’s boundaries explicit when integrating it: trusted application data in, a saved message out, and a provider acknowledgement recorded separately. Start with the Elements installation guide and rendering API, then adapt the template to your own activity model. If customers need to edit layouts themselves, that is a different workflow covered by our embeddable React email builder guide.