pg

const BatchUsersUpsertQuery = `` /* 135-byte string literal not displayed */

BatchUsersUpsertQuery is a SQL query that batch upserts multiple user logins and returns the complete login -> user_id mapping.

This query:

• Accepts an array of logins via unnest($1::text[])
• Inserts new users with those logins (profile_url and avatar_url are NULL initially)
• On conflict (login already exists), performs a no-op update (preserves the existing row's data, only login is reassigned to itself)
• Returns all logins with their user_ids, both newly created and pre-existing

The ON CONFLICT clause deliberately uses "DO UPDATE SET login = excluded.login" rather than "DO NOTHING": Postgres does not produce RETURNING rows for conflicts resolved by DO NOTHING, only for rows actually written by INSERT or UPDATE. Without the no-op update, a conflicting (pre-existing) login would be silently dropped from the result set, and the caller would need a second SELECT round trip to fill in the gaps. The no-op update keeps the whole batch upsert to exactly one round trip.

The query is idempotent: re-running with the same login set returns consistent results.

Usage:

rows, err := db.Query(ctx, BatchUsersUpsertQuery, pq.Array([]string{"alice", "bob"}))
for rows.Next() {
  var login string
  var userID int64
  rows.Scan(&login, &userID)
  // login -> userID mapping
}

Example SQL test:

SELECT * FROM users;
-- Initial state: empty

SELECT * FROM unnest(ARRAY['alice', 'bob', 'charlie']) AS login;
-- Input: three logins

-- Execute the query:
INSERT INTO users (login)
SELECT unnest(ARRAY['alice', 'bob', 'charlie']::text[])
ON CONFLICT (login) DO UPDATE SET login = excluded.login
RETURNING login, user_id;
-- Returns: alice -> 1, bob -> 2, charlie -> 3

-- Re-run with same + new logins:
INSERT INTO users (login)
SELECT unnest(ARRAY['alice', 'bob', 'diana']::text[])
ON CONFLICT (login) DO UPDATE SET login = excluded.login
RETURNING login, user_id;
-- Returns: alice -> 1, bob -> 2, diana -> 4
-- (alice and bob reuse existing IDs, diana gets new ID)

const RepoUpsertQuery = `` /* 176-byte string literal not displayed */

RepoUpsertQuery is a single parameterized upsert that allocates or reuses the surrogate repo_id for a (provider, repo_full_name) pair. Per docs/plan/plan.md "Postgres schema": allocation is one upsert-returning-id per repo, not per commit — a single round trip per scan job, negligible against the clone itself.

ON CONFLICT ... DO UPDATE (not DO NOTHING) is required for two reasons:

1. Postgres does not emit a RETURNING row for a conflict resolved by DO NOTHING, only for rows actually written by INSERT or UPDATE — DO NOTHING would return no rows at all on the (common) second-and-later call for an existing repo.
2. repo_full_name must be refreshed in place on a GitHub rename (plan.md edge case 4: "Repo renamed on GitHub — the surrogate repo_id survives it; the repos.repo_full_name row is updated"). DO NOTHING would leave the stale name in place.

const UsersSelectByLoginsQuery = `
SELECT login, user_id
FROM users
WHERE login = ANY($1::text[])
`

UsersSelectByLoginsQuery is a fallback query that retrieves user_ids for existing logins. This is useful when you need to get the mapping without attempting insertion.

Use this when: - You know all logins already exist and want to avoid the overhead of INSERT attempts - You need to distinguish between "not found" and "newly inserted" cases

Returns only logins that already exist in the users table.