Function resolvePersonId

server/src/db/relationship-db.ts:74–309

(identifiers: {
  slack_user_id?: string;
  workos_user_id?: string;
  email?: string;
  prospect_org_id?: string;
  display_name?: string;
})

 * Returns the person ID (UUID).
 */
export async function resolvePersonId(identifiers: {
  slack_user_id?: string;
  workos_user_id?: string;
  email?: string;
  prospect_org_id?: string;
  display_name?: string;
}): Promise<string> {
  const client = await getClient();
  try {
    await client.query('BEGIN');

    const conditions: string[] = [];
    const params: unknown[] = [];

    if (identifiers.slack_user_id) {
      params.push(identifiers.slack_user_id);
      conditions.push(`slack_user_id = $${params.length}`);
    }
    if (identifiers.workos_user_id) {
      params.push(identifiers.workos_user_id);
      conditions.push(`workos_user_id = $${params.length}`);
    }
    if (identifiers.email) {
      params.push(identifiers.email);
      conditions.push(`email = $${params.length}`);
    }

    let existing: Record<string, unknown> | undefined;
    let mergedPersonIds: string[] = [];
    let identityLinkedData: Record<string, string> | null = null;

    if (conditions.length > 0) {
      const result = await client.query(
        `SELECT * FROM person_relationships WHERE ${conditions.join(' OR ')} FOR UPDATE`,
        params
      );

      if (result.rows.length > 1) {
        // Multiple rows matched different identifiers — need to merge.
        // Pick the oldest record as the winner (most history).
        const sorted = result.rows.sort(
          (a, b) => new Date(a.created_at as string).getTime() - new Date(b.created_at as string).getTime()
        );
        const winner = sorted[0];
        const losers = sorted.slice(1);

        // Absorb loser fields the winner doesn't have
        for (const loser of losers) {
          const fieldUpdates: string[] = [];
          const fieldParams: unknown[] = [];
          let fi = 1;

          // Identity fields
          if (!winner.slack_user_id && loser.slack_user_id) {
            fieldUpdates.push(`slack_user_id = $${fi++}`);
            fieldParams.push(loser.slack_user_id);
            winner.slack_user_id = loser.slack_user_id;
          }