← back to Norma
Fix tier_credentials schema drift in users + gmail routes
b184c1077ed2d3f8accdc52586259a5307120e31 · 2026-07-11 08:55:30 -0700 · Steve Abrams
users: drop full_name/email/is_active; name from display_name, email from
email-shaped username, no is_active filter.
gmail messages/[id]/assign/oauth: swap full_name->display_name in COALESCE,
resolve email from username (no tier_credentials.email), drop is_active
filter on the assign target lookup.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M app/api/gmail/messages/[id]/assign/route.tsM app/api/gmail/messages/[id]/route.tsM app/api/gmail/messages/route.tsM app/api/gmail/oauth/route.tsM app/api/users/route.ts
Diff
commit b184c1077ed2d3f8accdc52586259a5307120e31
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Jul 11 08:55:30 2026 -0700
Fix tier_credentials schema drift in users + gmail routes
users: drop full_name/email/is_active; name from display_name, email from
email-shaped username, no is_active filter.
gmail messages/[id]/assign/oauth: swap full_name->display_name in COALESCE,
resolve email from username (no tier_credentials.email), drop is_active
filter on the assign target lookup.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
app/api/gmail/messages/[id]/assign/route.ts | 2 +-
app/api/gmail/messages/[id]/route.ts | 4 ++--
app/api/gmail/messages/route.ts | 16 +++++-----------
app/api/gmail/oauth/route.ts | 24 +++++-------------------
app/api/users/route.ts | 17 +++++++----------
5 files changed, 20 insertions(+), 43 deletions(-)
diff --git a/app/api/gmail/messages/[id]/assign/route.ts b/app/api/gmail/messages/[id]/assign/route.ts
index a9be55f..cb173c2 100644
--- a/app/api/gmail/messages/[id]/assign/route.ts
+++ b/app/api/gmail/messages/[id]/assign/route.ts
@@ -29,7 +29,7 @@ export async function POST(request: NextRequest, { params }: { params: Promise<{
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')
+ WHERE id = $1 AND role IN ('admin','staff','intern')
LIMIT 1`,
[userId],
);
diff --git a/app/api/gmail/messages/[id]/route.ts b/app/api/gmail/messages/[id]/route.ts
index c8e24b5..206d064 100644
--- a/app/api/gmail/messages/[id]/route.ts
+++ b/app/api/gmail/messages/[id]/route.ts
@@ -52,7 +52,7 @@ export async function GET(
// 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
+ `SELECT username, COALESCE(display_name, username) AS name
FROM tier_credentials WHERE id = $1 LIMIT 1`,
[msg.assigned_user_id],
);
@@ -65,7 +65,7 @@ export async function GET(
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
+ `SELECT r.user_id, tc.username, COALESCE(tc.display_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
diff --git a/app/api/gmail/messages/route.ts b/app/api/gmail/messages/route.ts
index ef8e896..4778532 100644
--- a/app/api/gmail/messages/route.ts
+++ b/app/api/gmail/messages/route.ts
@@ -90,16 +90,10 @@ export async function GET(request: NextRequest) {
conditions.push(`mailbox_id = $${idx++}`);
params.push(explicitMailbox);
} else {
- // Resolve the user's email and look up their mailbox
+ // Resolve the user's email — tier_credentials has no email column, so the
+ // username IS the email when it's email-shaped.
let userEmail: string | null = null;
- try {
- const u = await query<{ email: string | null }>(
- 'SELECT email FROM tier_credentials WHERE username = $1',
- [auth.username],
- );
- userEmail = u.rows[0]?.email || null;
- } catch { /* non-fatal */ }
- if (!userEmail && auth.username.includes('@')) {
+ if (auth.username.includes('@')) {
userEmail = auth.username;
}
if (userEmail) {
@@ -238,7 +232,7 @@ export async function GET(request: NextRequest) {
.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
+ `SELECT id, username, COALESCE(display_name, username) AS name
FROM tier_credentials WHERE id = ANY($1::uuid[])`,
[assigneeIds],
);
@@ -252,7 +246,7 @@ export async function GET(request: NextRequest) {
read_at: string;
}>(
`SELECT r.message_id, r.user_id, tc.username,
- COALESCE(tc.full_name, tc.username) AS name, r.read_at
+ COALESCE(tc.display_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[])
diff --git a/app/api/gmail/oauth/route.ts b/app/api/gmail/oauth/route.ts
index f6adc0d..6d7a5e0 100644
--- a/app/api/gmail/oauth/route.ts
+++ b/app/api/gmail/oauth/route.ts
@@ -1,7 +1,6 @@
import { NextRequest, NextResponse } from 'next/server';
import { requireRole } from '@/lib/require-role';
import { getAuthUrl, getConnectionStatus, getOAuth2Client, storeTokens } from '@/lib/gmail';
-import { query } from '@/lib/db';
import { verifyAuth } from '@/lib/auth';
/**
@@ -21,25 +20,16 @@ export async function GET(request: NextRequest) {
// 1. explicit ?mailbox=… (admin can connect any)
// 2. session email (the logged-in user's own mailbox)
let mailboxEmail = searchParams.get('mailbox') || '';
- if (!mailboxEmail) {
- try {
- const r = await query<{ email: string | null }>(
- 'SELECT email FROM tier_credentials WHERE username = $1',
- [auth.username],
- );
- mailboxEmail = r.rows[0]?.email || (auth.username.includes('@') ? auth.username : '');
- } catch { /* non-fatal */ }
+ if (!mailboxEmail && auth.username.includes('@')) {
+ // tier_credentials has no email column — the username IS the email.
+ mailboxEmail = auth.username;
}
if (!mailboxEmail) {
return NextResponse.json({ error: 'No mailbox email resolvable for this user' }, { status: 400 });
}
// Non-admins may only connect their own mailbox
if (auth.role !== 'admin') {
- const userMatch = await query<{ email: string | null }>(
- 'SELECT email FROM tier_credentials WHERE username = $1',
- [auth.username],
- );
- const ownEmail = (userMatch.rows[0]?.email || (auth.username.includes('@') ? auth.username : '')).toLowerCase();
+ const ownEmail = (auth.username.includes('@') ? auth.username : '').toLowerCase();
if (mailboxEmail.toLowerCase() !== ownEmail) {
return NextResponse.json({ error: 'You can only connect your own Gmail' }, { status: 403 });
}
@@ -138,11 +128,7 @@ export async function POST(request: NextRequest) {
// Non-admins still restricted to their own mailbox
if (auth.role !== 'admin') {
- const own = await query<{ email: string | null }>(
- 'SELECT email FROM tier_credentials WHERE username = $1',
- [auth.username],
- );
- const ownEmail = (own.rows[0]?.email || (auth.username.includes('@') ? auth.username : '')).toLowerCase();
+ const ownEmail = (auth.username.includes('@') ? auth.username : '').toLowerCase();
if (mailboxEmail.toLowerCase() !== ownEmail) {
return NextResponse.json({ error: 'You can only connect your own Gmail' }, { status: 403 });
}
diff --git a/app/api/users/route.ts b/app/api/users/route.ts
index 2ba52d1..74de29a 100644
--- a/app/api/users/route.ts
+++ b/app/api/users/route.ts
@@ -1,5 +1,5 @@
// GET /api/users — list of staff/admin/intern users for assignment dropdowns.
-// Admin-only. Returns id, full_name (fallback to username), email, role.
+// Admin-only. Returns id, name (display_name fallback to username), role.
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
@@ -15,24 +15,21 @@ export async function GET(request: NextRequest) {
const result = await query<{
id: string;
username: string;
- full_name: string | null;
- email: string | null;
+ display_name: string | null;
role: string;
- is_active: boolean;
}>(
- `SELECT id, username, full_name, email, role, is_active
+ `SELECT id, username, display_name, role
FROM tier_credentials
- WHERE is_active = TRUE
- AND role IN ('admin', 'staff', 'intern')
- ORDER BY COALESCE(full_name, username) ASC`,
+ WHERE role IN ('admin', 'staff', 'intern')
+ ORDER BY COALESCE(display_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,
+ name: r.display_name || r.username,
+ email: r.username.includes('@') ? r.username : null,
role: r.role,
})),
});
← 86dc7d4 Fix tier_credentials schema drift in auth + impersonate rout
·
back to Norma
·
chore: version bump (session close — Norma platform + agent dab8014 →