Check out my Python script to sync JumpCloud users to an on-prem PostgreSQL db.

Alright, so I've been putting JumpCloud's Directory-as-a-Service through its paces for a few months now, mainly for centralizing user auth across some legacy on-prem apps and cloud tools. One thing I kept wanting was a local, queryable snapshot of our user directory for internal dashboards and audit reports without hitting API rate limits. The API is decent, but I needed something that could join user data with local application tables in our good old PostgreSQL database.

I decided to write a Python sync script that pulls user data from JumpCloud and dumps it into a local `users` table. It's not a complex real-time sync, more of a daily cron job to keep things reasonably fresh. I focused on keeping it idempotent and handling the soft deletes (suspended users) that JumpCloud uses.

Here's the core of it. I used `requests` and `psycopg2`, with a simple config for the API key and DB connection. The script does a few key things:

* Fetches users from the JumpCloud `/users` endpoint with pagination.
* Maps relevant fields (like `id`, `email`, `firstname`, `lastname`, `suspended` status) to our local schema.
* Uses the JumpCloud user `id` as the primary key for upserts (`ON CONFLICT...DO UPDATE`).
* Importantly, it also checks for users in our DB that are no longer in the JumpCloud response and marks them as `inactive` (we don't delete, just flag).

```python
import requests
import psycopg2
from psycopg2.extras import execute_batch
from config import JC_API_KEY, DB_PARAMS

JC_HEADERS = {'Accept': 'application/json', 'Authorization': f'Bearer {JC_API_KEY}'}
JC_BASE_URL = 'https://console.jumpcloud.com/api/v2'

def fetch_jc_users():
"""Fetch all users from JumpCloud with pagination."""
users = []
url = f'{JC_BASE_URL}/users'
while url:
resp = requests.get(url, headers=JC_HEADERS, params={'limit': 100})
resp.raise_for_status()
data = resp.json()
users.extend(data.get('results', []))
url = data.get('nextUrl') # JumpCloud paginates with 'nextUrl'
return users

def sync_users():
jc_users = fetch_jc_users()
current_jc_ids = {user['id'] for user in jc_users}

conn = psycopg2.connect(**DB_PARAMS)
cur = conn.cursor()

# Upsert fetched users
upsert_sql = """
INSERT INTO jumpcloud_users (jc_id, email, first_name, last_name, active, department, raw_json)
VALUES (%s, %s, %s, %s, %s, %s, %s::jsonb)
ON CONFLICT (jc_id) DO UPDATE SET
email = EXCLUDED.email,
first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
active = EXCLUDED.active,
department = EXCLUDED.department,
raw_json = EXCLUDED.raw_json,
last_synced = CURRENT_TIMESTAMP;
"""
batch_data = []
for user in jc_users:
batch_data.append((
user['id'],
user.get('email'),
user.get('firstname', ''),
user.get('lastname', ''),
not user.get('suspended', False), # Map suspended to our 'active' flag
user.get('department', ''),
json.dumps(user)
))
execute_batch(cur, upsert_sql, batch_data)

# Deactivate users no longer in JumpCloud
deactivate_sql = "UPDATE jumpcloud_users SET active = FALSE WHERE jc_id NOT IN %s;"
cur.execute(deactivate_sql, (tuple(current_jc_ids) if current_jc_ids else ('NULL',),))

conn.commit()
cur.close()
conn.close()
print(f"Synced {len(jc_users)} users. Marked missing users as inactive.")

if __name__ == '__main__':
sync_users()
```

Some observations and questions for the community:

* The JumpCloud API's use of `nextUrl` for pagination is straightforward, but I wish the user objects had a bit more consistency—some fields are just missing vs. being `null` sometimes.
* I'm storing the entire raw JSON for each user in a `jsonb` column. This has been a lifesaver for ad-hoc queries when we needed a new field I hadn't mapped initially. Highly recommend this pattern.
* I'm still debating whether to use the system user `id` or the `username` field as the immutable key. The `id` feels right, but some of our old systems key off email.
* Has anyone else built a similar sync? Curious about:
* How you handle group membership syncing (that's my next step, and the `/usergroups` and `/groupmembership` endpoints look a bit more nested).
* If you've added any retry logic or exponential backoff for the API calls.
* Whether you sync system bindings (like which users are on which servers) and how you model that relationally.

The script is simple, but it's been running reliably and gives us a lot more flexibility. It's also a great concrete way to compare how JumpCloud's API design and data model stack up against other directory services we've tested.