← back to Norma
feat(gmail): per-message Assign-to + per-user read receipts + inline-expand layout
38145dfa29d8bec3bf377dbbaa425741df18f8b9 · 2026-05-20 12:32:18 -0700 · Steve Abrams
DB (migration 025_email_assign_read.sql):
- gmail_messages.assigned_user_id / assigned_at / assigned_by (FK tier_credentials)
- gmail_message_reads (message_id, user_id, read_at) — one row per (msg, user)
API:
- GET /api/users — assignable users (admin/staff/intern) for the dropdown
- POST /api/gmail/messages/[id]/assign {userId} — admin-only assignment
- POST /api/gmail/messages/[id]/mark-read — current user records read (idempotent)
- GET /api/gmail/messages — now returns assigned_name + read_by[] for each msg
- GET /api/gmail/messages/[id] — auto-records read receipt for the CURRENT user
on view (per-user, replaces the legacy global is_read auto-flip)
UI (GmailCRMTab):
- Row footer chips: 'Assigned to <name>' (indigo) + 'Seen ×N' or 'Seen by X' (emerald)
- Detail panel: Owner dropdown (admin-only) + Seen-by pill list with read-at tooltips
- Layout: stacked column instead of 45/55 split — detail expands inline below the
list rather than as a sibling side-pane, per Steve's 'open inline with expanding
area' directive.
- Auto mark-as-read happens server-side on GET /messages/[id]; no extra client call.
Files touched
A app/api/gmail/messages/[id]/assign/route.tsA app/api/gmail/messages/[id]/mark-read/route.tsM app/api/gmail/messages/[id]/route.tsM app/api/gmail/messages/route.tsA app/api/users/route.tsM components/gmail/GmailCRMTab.tsxA db/025_email_assign_read.sql
Diff
commit 38145dfa29d8bec3bf377dbbaa425741df18f8b9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed May 20 12:32:18 2026 -0700
feat(gmail): per-message Assign-to + per-user read receipts + inline-expand layout
DB (migration 025_email_assign_read.sql):
- gmail_messages.assigned_user_id / assigned_at / assigned_by (FK tier_credentials)
- gmail_message_reads (message_id, user_id, read_at) — one row per (msg, user)
API:
- GET /api/users — assignable users (admin/staff/intern) for the dropdown
- POST /api/gmail/messages/[id]/assign {userId} — admin-only assignment
- POST /api/gmail/messages/[id]/mark-read — current user records read (idempotent)
- GET /api/gmail/messages — now returns assigned_name + read_by[] for each msg
- GET /api/gmail/messages/[id] — auto-records read receipt for the CURRENT user
on view (per-user, replaces the legacy global is_read auto-flip)
UI (GmailCRMTab):
- Row footer chips: 'Assigned to <name>' (indigo) + 'Seen ×N' or 'Seen by X' (emerald)
- Detail panel: Owner dropdown (admin-only) + Seen-by pill list with read-at tooltips
- Layout: stacked column instead of 45/55 split — detail expands inline below the
list rather than as a sibling side-pane, per Steve's 'open inline with expanding
area' directive.
- Auto mark-as-read happens server-side on GET /messages/[id]; no extra client call.
---
app/api/gmail/messages/[id]/assign/route.ts | 65 +++++++++
app/api/gmail/messages/[id]/mark-read/route.ts | 52 ++++++++
app/api/gmail/messages/[id]/route.ts | 60 ++++++++-
app/api/gmail/messages/route.ts | 49 +++++++
app/api/users/route.ts | 39 ++++++
components/gmail/GmailCRMTab.tsx | 175 ++++++++++++++++++++++++-
db/025_email_assign_read.sql | 32 +++++
7 files changed, 461 insertions(+), 11 deletions(-)
diff --git a/app/api/gmail/messages/[id]/assign/route.ts b/app/api/gmail/messages/[id]/assign/route.ts
new file mode 100644
index 0000000..a9be55f
--- /dev/null
+++ b/app/api/gmail/messages/[id]/assign/route.ts
@@ -0,0 +1,65 @@
+// POST /api/gmail/messages/[id]/assign — admin assigns a message to a staff/intern.
+// Body: { userId: string | null } (null = unassign)
+
+import { NextRequest, NextResponse } from 'next/server';
+import { query } from '@/lib/db';
+import { requireRole } from '@/lib/require-role';
+
+export const runtime = 'nodejs';
+
+export async function POST(request: NextRequest, { params }: { params: Promise<{ id: string }> }) {
+ const auth = requireRole(request, 'admin');
+ if (auth instanceof NextResponse) return auth;
+
+ const { id } = await params;
+ if (!id) return NextResponse.json({ error: 'missing id' }, { status: 400 });
+
+ let body: { userId?: string | null };
+ try { body = await request.json(); } catch { body = {}; }
+ const userId = body.userId ?? null;
+
+ // Look up the assigning admin's id from username
+ const adminRow = await query<{ id: string }>(
+ `SELECT id FROM tier_credentials WHERE username = $1 LIMIT 1`,
+ [auth.username],
+ );
+ const assignedBy = adminRow.rows[0]?.id || null;
+
+ // If assigning (not null), validate the target user exists + is active staff/intern
+ if (userId !== null) {
+ const userRow = await query<{ id: string; role: string }>(
+ `SELECT id, role FROM tier_credentials
+ WHERE id = $1 AND is_active = TRUE AND role IN ('admin','staff','intern')
+ LIMIT 1`,
+ [userId],
+ );
+ if (userRow.rows.length === 0) {
+ return NextResponse.json({ error: 'user not found or not assignable' }, { status: 404 });
+ }
+ }
+
+ const updateRes = await query<{
+ id: string;
+ assigned_user_id: string | null;
+ assigned_at: string | null;
+ }>(
+ `UPDATE gmail_messages
+ SET assigned_user_id = $1,
+ assigned_at = CASE WHEN $1 IS NULL THEN NULL ELSE NOW() END,
+ assigned_by = $2
+ WHERE id = $3
+ RETURNING id, assigned_user_id, assigned_at`,
+ [userId, assignedBy, id],
+ );
+
+ if (updateRes.rows.length === 0) {
+ return NextResponse.json({ error: 'message not found' }, { status: 404 });
+ }
+
+ return NextResponse.json({
+ ok: true,
+ message_id: id,
+ assigned_user_id: updateRes.rows[0].assigned_user_id,
+ assigned_at: updateRes.rows[0].assigned_at,
+ });
+}
diff --git a/app/api/gmail/messages/[id]/mark-read/route.ts b/app/api/gmail/messages/[id]/mark-read/route.ts
new file mode 100644
index 0000000..28969a4
--- /dev/null
+++ b/app/api/gmail/messages/[id]/mark-read/route.ts
@@ -0,0 +1,52 @@
+// POST /api/gmail/messages/[id]/mark-read — record that the CURRENT user has
+// read this message. Idempotent — re-calling does nothing (existing row stays).
+// Also flips the legacy global is_read flag so existing list-view logic works.
+
+import { NextRequest, NextResponse } from 'next/server';
+import { query } from '@/lib/db';
+import { requireRole } from '@/lib/require-role';
+
+export const runtime = 'nodejs';
+
+export async function POST(request: NextRequest, { params }: { params: Promise<{ id: string }> }) {
+ const auth = requireRole(request, 'admin', 'staff', 'intern');
+ if (auth instanceof NextResponse) return auth;
+
+ const { id } = await params;
+ if (!id) return NextResponse.json({ error: 'missing id' }, { status: 400 });
+
+ // Resolve current user id
+ const userRow = await query<{ id: string }>(
+ `SELECT id FROM tier_credentials WHERE username = $1 LIMIT 1`,
+ [auth.username],
+ );
+ if (userRow.rows.length === 0) {
+ return NextResponse.json({ error: 'session user not found in credentials' }, { status: 403 });
+ }
+ const userId = userRow.rows[0].id;
+
+ // Verify message exists
+ const msgRow = await query<{ id: string }>(
+ `SELECT id FROM gmail_messages WHERE id = $1 LIMIT 1`,
+ [id],
+ );
+ if (msgRow.rows.length === 0) {
+ return NextResponse.json({ error: 'message not found' }, { status: 404 });
+ }
+
+ // Insert read receipt (idempotent via PK conflict)
+ await query(
+ `INSERT INTO gmail_message_reads (message_id, user_id)
+ VALUES ($1, $2)
+ ON CONFLICT (message_id, user_id) DO NOTHING`,
+ [id, userId],
+ );
+
+ // Also flip the legacy is_read flag so existing UI markers still work.
+ await query(
+ `UPDATE gmail_messages SET is_read = TRUE WHERE id = $1 AND is_read = FALSE`,
+ [id],
+ );
+
+ return NextResponse.json({ ok: true, message_id: id, user_id: userId });
+}
diff --git a/app/api/gmail/messages/[id]/route.ts b/app/api/gmail/messages/[id]/route.ts
index 19605c0..c8e24b5 100644
--- a/app/api/gmail/messages/[id]/route.ts
+++ b/app/api/gmail/messages/[id]/route.ts
@@ -22,6 +22,7 @@ export async function GET(
to_addresses, cc_addresses, date_sent, snippet,
body_text, body_html, labels, headers, attachments,
is_read, created_at,
+ assigned_user_id, assigned_at,
CASE
WHEN labels @> ARRAY['IMPORTANT']
OR from_address ILIKE '%media%'
@@ -46,14 +47,65 @@ export async function GET(
if (!res.rows[0]) {
return NextResponse.json({ error: 'Message not found' }, { status: 404 });
}
+ const msg = res.rows[0];
+
+ // Hydrate assignee + read-by, mirror the list endpoint.
+ if (msg.assigned_user_id) {
+ const aRes = await query<{ username: string; name: string }>(
+ `SELECT username, COALESCE(full_name, username) AS name
+ FROM tier_credentials WHERE id = $1 LIMIT 1`,
+ [msg.assigned_user_id],
+ );
+ msg.assigned_username = aRes.rows[0]?.username || null;
+ msg.assigned_name = aRes.rows[0]?.name || null;
+ } else {
+ msg.assigned_username = null;
+ msg.assigned_name = null;
+ }
+ const rRes = await query<{
+ user_id: string; username: string; name: string; read_at: string;
+ }>(
+ `SELECT r.user_id, tc.username, COALESCE(tc.full_name, tc.username) AS name, r.read_at
+ FROM gmail_message_reads r
+ JOIN tier_credentials tc ON tc.id = r.user_id
+ WHERE r.message_id = $1
+ ORDER BY r.read_at ASC`,
+ [id],
+ );
+ msg.read_by = rRes.rows;
+
+ // Auto-mark as read for the CURRENT user (per-user receipt + global flag).
+ try {
+ const meRes = await query<{ id: string }>(
+ `SELECT id FROM tier_credentials WHERE username = $1 LIMIT 1`,
+ [auth.username],
+ );
+ const myId = meRes.rows[0]?.id;
+ if (myId) {
+ await query(
+ `INSERT INTO gmail_message_reads (message_id, user_id)
+ VALUES ($1, $2) ON CONFLICT DO NOTHING`,
+ [id, myId],
+ );
+ // Surface the new receipt to this response (without re-querying).
+ if (!msg.read_by.some((r: { user_id: string }) => r.user_id === myId)) {
+ msg.read_by.push({
+ user_id: myId,
+ username: auth.username,
+ name: auth.username,
+ read_at: new Date().toISOString(),
+ });
+ }
+ }
+ } catch { /* read-receipt is best-effort; don't fail the whole request */ }
- // Auto-mark as read
- if (!res.rows[0].is_read) {
+ // Keep legacy global is_read flipped on too.
+ if (!msg.is_read) {
await query(`UPDATE gmail_messages SET is_read = true WHERE id = $1`, [id]);
- res.rows[0].is_read = true;
+ msg.is_read = true;
}
- return NextResponse.json(res.rows[0]);
+ return NextResponse.json(msg);
}
/**
diff --git a/app/api/gmail/messages/route.ts b/app/api/gmail/messages/route.ts
index 15d6933..672ea84 100644
--- a/app/api/gmail/messages/route.ts
+++ b/app/api/gmail/messages/route.ts
@@ -137,6 +137,7 @@ export async function GET(request: NextRequest) {
to_addresses, cc_addresses, date_sent, snippet,
LEFT(body_text, 200) AS body_preview,
labels, is_read, attachments, created_at,
+ assigned_user_id, assigned_at,
(${priorityExpr}) AS priority
FROM gmail_messages
${where}
@@ -145,6 +146,54 @@ export async function GET(request: NextRequest) {
[...params, limit, offset],
);
+ // Hydrate assigned-user info + read-by list for the returned page of messages.
+ // Done in a second pass so the main query keeps its existing shape + index plan.
+ const messageIds: string[] = messagesRes.rows.map((r: { id: string }) => r.id);
+ let assigneesById: Record<string, { id: string; username: string; name: string } | undefined> = {};
+ let readsByMessageId: Record<string, Array<{ user_id: string; username: string; name: string; read_at: string }>> = {};
+ if (messageIds.length > 0) {
+ const assigneeIds = messagesRes.rows
+ .map((r: { assigned_user_id: string | null }) => r.assigned_user_id)
+ .filter((v: string | null): v is string => !!v);
+ if (assigneeIds.length > 0) {
+ const aRes = await query<{ id: string; username: string; name: string }>(
+ `SELECT id, username, COALESCE(full_name, username) AS name
+ FROM tier_credentials WHERE id = ANY($1::uuid[])`,
+ [assigneeIds],
+ );
+ assigneesById = Object.fromEntries(aRes.rows.map(r => [r.id, r]));
+ }
+ const rRes = await query<{
+ message_id: string;
+ user_id: string;
+ username: string;
+ name: string;
+ read_at: string;
+ }>(
+ `SELECT r.message_id, r.user_id, tc.username,
+ COALESCE(tc.full_name, tc.username) AS name, r.read_at
+ FROM gmail_message_reads r
+ JOIN tier_credentials tc ON tc.id = r.user_id
+ WHERE r.message_id = ANY($1::uuid[])
+ ORDER BY r.read_at ASC`,
+ [messageIds],
+ );
+ for (const row of rRes.rows) {
+ (readsByMessageId[row.message_id] ||= []).push({
+ user_id: row.user_id,
+ username: row.username,
+ name: row.name,
+ read_at: row.read_at,
+ });
+ }
+ }
+ for (const m of messagesRes.rows) {
+ const a = m.assigned_user_id ? assigneesById[m.assigned_user_id] : undefined;
+ m.assigned_username = a?.username || null;
+ m.assigned_name = a?.name || null;
+ m.read_by = readsByMessageId[m.id] || [];
+ }
+
// Get unique labels/topics for filter dropdown (natalia only)
const nmbId = nataliaMailbox.rows[0]?.id;
const topicsRes = await query(
diff --git a/app/api/users/route.ts b/app/api/users/route.ts
new file mode 100644
index 0000000..2ba52d1
--- /dev/null
+++ b/app/api/users/route.ts
@@ -0,0 +1,39 @@
+// GET /api/users — list of staff/admin/intern users for assignment dropdowns.
+// Admin-only. Returns id, full_name (fallback to username), email, role.
+
+import { NextRequest, NextResponse } from 'next/server';
+import { query } from '@/lib/db';
+import { requireRole } from '@/lib/require-role';
+
+export const runtime = 'nodejs';
+export const dynamic = 'force-dynamic';
+
+export async function GET(request: NextRequest) {
+ const auth = requireRole(request, 'admin', 'staff');
+ if (auth instanceof NextResponse) return auth;
+
+ const result = await query<{
+ id: string;
+ username: string;
+ full_name: string | null;
+ email: string | null;
+ role: string;
+ is_active: boolean;
+ }>(
+ `SELECT id, username, full_name, email, role, is_active
+ FROM tier_credentials
+ WHERE is_active = TRUE
+ AND role IN ('admin', 'staff', 'intern')
+ ORDER BY COALESCE(full_name, username) ASC`,
+ );
+
+ return NextResponse.json({
+ users: result.rows.map(r => ({
+ id: r.id,
+ username: r.username,
+ name: r.full_name || r.username,
+ email: r.email,
+ role: r.role,
+ })),
+ });
+}
diff --git a/components/gmail/GmailCRMTab.tsx b/components/gmail/GmailCRMTab.tsx
index c5ad73c..713b3d3 100644
--- a/components/gmail/GmailCRMTab.tsx
+++ b/components/gmail/GmailCRMTab.tsx
@@ -14,6 +14,21 @@ import GmailContactsPanel from './GmailContactsPanel';
/* ─── Types ──────────────────────────────────────────────────────────────── */
+interface AssignableUser {
+ id: string;
+ username: string;
+ name: string;
+ email: string | null;
+ role: string;
+}
+
+interface ReadReceipt {
+ user_id: string;
+ username: string;
+ name: string;
+ read_at: string;
+}
+
interface GmailMessage {
id: string;
gmail_id: string;
@@ -32,6 +47,12 @@ interface GmailMessage {
attachments: unknown[] | null;
priority: 'high' | 'medium' | 'low';
created_at: string;
+ // Per-message assignment + read receipts (from /api/gmail/messages backend).
+ assigned_user_id?: string | null;
+ assigned_username?: string | null;
+ assigned_name?: string | null;
+ assigned_at?: string | null;
+ read_by?: ReadReceipt[];
}
interface AIDraft {
@@ -217,6 +238,10 @@ export default function GmailCRMTab() {
const [offset, setOffset] = useState(0);
const limit = 50;
+ // Assign-to dropdown state: list of users that admin/staff can assign to.
+ const [assignableUsers, setAssignableUsers] = useState<AssignableUser[]>([]);
+ const [assigningId, setAssigningId] = useState<string | null>(null);
+
const [syncStatus, setSyncStatus] = useState<SyncStatus | null>(null);
const [showSyncDetails, setShowSyncDetails] = useState(false);
// Mail first: land with the full-width inbox visible. The Contacts sidebar
@@ -247,6 +272,57 @@ export default function GmailCRMTab() {
return () => clearInterval(interval);
}, [fetchSyncStatus]);
+ // Fetch assignable users once on mount (admin + staff + intern). Used by the
+ // per-message "Assign to ▾" dropdown. Silently empty for unauthorized roles.
+ useEffect(() => {
+ let alive = true;
+ fetch('/api/users', { credentials: 'same-origin' })
+ .then(r => r.ok ? r.json() : { users: [] })
+ .then(d => { if (alive) setAssignableUsers(d.users || []); })
+ .catch(() => { /* silently empty if not permitted */ });
+ return () => { alive = false; };
+ }, []);
+
+ // POST assignment. userId === null unassigns.
+ const assignMessage = useCallback(async (msgId: string, userId: string | null) => {
+ setAssigningId(msgId);
+ try {
+ const res = await fetch(`/api/gmail/messages/${msgId}/assign`, {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify({ userId }),
+ credentials: 'same-origin',
+ });
+ const data = await res.json();
+ if (!res.ok) throw new Error(data.error || 'Failed to assign');
+ const assignee = userId ? assignableUsers.find(u => u.id === userId) : null;
+ // Optimistic local update
+ setMessages(prev => prev.map(m =>
+ m.id === msgId ? {
+ ...m,
+ assigned_user_id: userId,
+ assigned_username: assignee?.username || null,
+ assigned_name: assignee?.name || null,
+ assigned_at: data.assigned_at,
+ } : m,
+ ));
+ setSelectedMsg(prev =>
+ prev && prev.id === msgId ? {
+ ...prev,
+ assigned_user_id: userId,
+ assigned_username: assignee?.username || null,
+ assigned_name: assignee?.name || null,
+ assigned_at: data.assigned_at,
+ } : prev,
+ );
+ toast.addToast(userId ? `Assigned to ${assignee?.name || 'user'}` : 'Unassigned', 'success');
+ } catch (err) {
+ toast.addToast((err as Error).message, 'error');
+ } finally {
+ setAssigningId(null);
+ }
+ }, [assignableUsers, toast]);
+
/* ── Fetch messages ─────────────────────────────────────────────────────── */
const fetchMessages = useCallback(async () => {
setLoading(true);
@@ -821,16 +897,20 @@ export default function GmailCRMTab() {
</div>
{/* ── Content area ───────────────────────────────────────────────────── */}
+ {/* 2026-05-20 — Steve directive: "onclick of message, open inline with
+ expanding area." Old layout had a 45/55 split with the detail panel
+ living to the RIGHT of the list. Now we stack vertically: list on top,
+ detail expands inline BELOW the list (the selected row stays visible
+ in scroll position). Contacts stays as a right sidebar when toggled. */}
<div style={{ display: 'flex', flex: 1, overflow: 'hidden' }}>
- {/* ── Message List ────────────────────────────────────────────────── */}
+ {/* ── Message List + Inline Detail (stacked) ──────────────────────── */}
<div
style={{
- width: selectedId ? '45%' : '100%',
- minWidth: selectedId ? 340 : undefined,
- borderRight: selectedId ? '1px solid var(--color-border)' : 'none',
+ flex: 1,
+ minWidth: 0,
display: 'flex',
flexDirection: 'column',
- transition: 'width 0.2s ease',
+ overflowY: 'auto',
}}
>
<div style={{ flex: 1, overflowY: 'auto' }}>
@@ -932,8 +1012,8 @@ export default function GmailCRMTab() {
{msg.body_preview || msg.snippet || ''}
</div>
- {/* Bottom: labels + attachments */}
- <div style={{ display: 'flex', alignItems: 'center', gap: 4, marginTop: 4 }}>
+ {/* Bottom: labels + attachments + assignment + read-by */}
+ <div style={{ display: 'flex', alignItems: 'center', gap: 4, marginTop: 4, flexWrap: 'wrap' }}>
{msg.attachments && (msg.attachments as unknown[]).length > 0 && (
<Paperclip size={11} style={{ color: 'var(--color-text-muted)' }} />
)}
@@ -947,6 +1027,36 @@ export default function GmailCRMTab() {
{l}
</span>
))}
+ {/* Assigned-to chip — visible to all staff/admin */}
+ {msg.assigned_name && (
+ <span style={{
+ fontSize: 9, padding: '1px 6px', borderRadius: 4,
+ backgroundColor: 'rgba(99,102,241,0.18)',
+ color: '#a5b4fc',
+ fontWeight: 700, letterSpacing: '0.04em',
+ display: 'inline-flex', alignItems: 'center', gap: 3,
+ }}
+ title={`Assigned to ${msg.assigned_name}${msg.assigned_at ? ' on ' + new Date(msg.assigned_at).toLocaleString() : ''}`}>
+ <Users size={9} />
+ {msg.assigned_name.split(/\s+/)[0]}
+ </span>
+ )}
+ {/* Read-by chip — only shown when at least one user has read */}
+ {msg.read_by && msg.read_by.length > 0 && (
+ <span style={{
+ fontSize: 9, padding: '1px 6px', borderRadius: 4,
+ backgroundColor: 'rgba(16,185,129,0.15)',
+ color: '#6ee7b7',
+ fontWeight: 700, letterSpacing: '0.04em',
+ display: 'inline-flex', alignItems: 'center', gap: 3,
+ }}
+ title={`Read by: ${msg.read_by.map(r => r.name).join(', ')}`}>
+ <Eye size={9} />
+ {msg.read_by.length === 1
+ ? `Seen by ${msg.read_by[0].name.split(/\s+/)[0]}`
+ : `Seen ×${msg.read_by.length}`}
+ </span>
+ )}
</div>
</div>
@@ -1086,6 +1196,57 @@ export default function GmailCRMTab() {
</span>
</div>
)}
+ {/* Assign-to row — admin only; staff/intern see the assignee read-only */}
+ <div style={{ display: 'flex', gap: 8, alignItems: 'center', marginTop: 6 }}>
+ <span style={{ color: 'var(--color-text-muted)', minWidth: 40 }}>Owner</span>
+ {assignableUsers.length > 0 ? (
+ <select
+ value={selectedMsg.assigned_user_id || ''}
+ disabled={assigningId === selectedMsg.id}
+ onChange={e => assignMessage(selectedMsg.id, e.target.value || null)}
+ className="input"
+ style={{ fontSize: 12, padding: '4px 8px', maxWidth: 260 }}
+ >
+ <option value="">— Unassigned —</option>
+ {assignableUsers.map(u => (
+ <option key={u.id} value={u.id}>
+ {u.name}{u.role !== 'admin' ? ` · ${u.role}` : ''}
+ </option>
+ ))}
+ </select>
+ ) : (
+ <span style={{ color: 'var(--color-text-secondary)' }}>
+ {selectedMsg.assigned_name || 'Unassigned'}
+ </span>
+ )}
+ {assigningId === selectedMsg.id && (
+ <Loader2 size={12} className="spinner" style={{ color: 'var(--color-primary)' }} />
+ )}
+ </div>
+ {/* Read-by row — surfaces per-user receipts so admin can see who has actually opened the email */}
+ <div style={{ display: 'flex', gap: 8, alignItems: 'flex-start', marginTop: 6 }}>
+ <span style={{ color: 'var(--color-text-muted)', minWidth: 40 }}>Seen by</span>
+ {selectedMsg.read_by && selectedMsg.read_by.length > 0 ? (
+ <div style={{ display: 'flex', flexWrap: 'wrap', gap: 4 }}>
+ {selectedMsg.read_by.map(r => (
+ <span key={r.user_id}
+ title={`Read at ${new Date(r.read_at).toLocaleString()}`}
+ style={{
+ fontSize: 11, padding: '2px 8px', borderRadius: 999,
+ backgroundColor: 'rgba(16,185,129,0.12)',
+ color: '#6ee7b7',
+ display: 'inline-flex', alignItems: 'center', gap: 4,
+ }}>
+ <CheckCircle2 size={10} /> {r.name}
+ </span>
+ ))}
+ </div>
+ ) : (
+ <span style={{ color: 'var(--color-text-muted)', fontStyle: 'italic' }}>
+ Not yet opened by anyone
+ </span>
+ )}
+ </div>
</div>
</div>
diff --git a/db/025_email_assign_read.sql b/db/025_email_assign_read.sql
new file mode 100644
index 0000000..b5acea5
--- /dev/null
+++ b/db/025_email_assign_read.sql
@@ -0,0 +1,32 @@
+-- ============================================================
+-- 025 — Per-email assign-to (admin → staff/intern) + per-user read receipts
+-- ============================================================
+-- Idempotent. Safe to re-run. Adds:
+-- 1. gmail_messages.assigned_user_id / assigned_at / assigned_by
+-- 2. gmail_message_reads — one row per (message, user) that has viewed it
+-- The legacy gmail_messages.is_read remains as a global "anyone has read"
+-- flag for backwards compatibility; new per-user state lives in the new table.
+
+-- 1. Assignment columns on gmail_messages
+ALTER TABLE gmail_messages
+ ADD COLUMN IF NOT EXISTS assigned_user_id UUID REFERENCES tier_credentials(id) ON DELETE SET NULL;
+ALTER TABLE gmail_messages
+ ADD COLUMN IF NOT EXISTS assigned_at TIMESTAMPTZ;
+ALTER TABLE gmail_messages
+ ADD COLUMN IF NOT EXISTS assigned_by UUID REFERENCES tier_credentials(id) ON DELETE SET NULL;
+
+CREATE INDEX IF NOT EXISTS idx_gmail_messages_assigned_user
+ ON gmail_messages(assigned_user_id) WHERE assigned_user_id IS NOT NULL;
+
+-- 2. Per-user read receipts
+CREATE TABLE IF NOT EXISTS gmail_message_reads (
+ message_id UUID NOT NULL REFERENCES gmail_messages(id) ON DELETE CASCADE,
+ user_id UUID NOT NULL REFERENCES tier_credentials(id) ON DELETE CASCADE,
+ read_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ PRIMARY KEY (message_id, user_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_gmail_message_reads_user
+ ON gmail_message_reads(user_id, read_at DESC);
+CREATE INDEX IF NOT EXISTS idx_gmail_message_reads_message
+ ON gmail_message_reads(message_id);
← f6b2dba feat(auth): intern role + user fields + admin-editable role
·
back to Norma
·
feat(modals): shared ResizableModal with full-screen toggle d433048 →