How to Enrich a Postgres Table With LeadOcean

Q: Can I enrich by name and company domain?

Not directly. Enrich takes exactly one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by person_id.

Q: Will the job re-spend records on rows it already enriched?

No. It only selects rows where enriched_at is null, and it sets that column on a match and on a 404.

Q: How do I size a company list before I spend anything?

Use GET /v1/companies/count or the leadocean_count_companies MCP tool. Both are free and return an exact total.

You enrich a Postgres table with a short Python script on a schedule. It claims rows with no enrichment yet, calls LeadOcean once per row, and updates each row with the email, phone and profile it gets back.

The script uses FOR UPDATE SKIP LOCKED, which Postgres documents in the SELECT reference, September 2026. Two overlapping runs never take the same row.

TL;DR

It takes about 30 minutes: 10 for the table, 10 for the script, 10 to test on a small batch.
It spends 1 record per matched row. A batch of 50 costs up to 50 of your 1,000 free records. A row we hold nothing for is a 404 and costs 0.
Every row needs a linkedin_url or an email. There is no name plus domain lookup, so keep one of those on each row.

Prerequisites

Postgres 10 or later (identity columns), and a role that can create a table and update it.
Python 3.10 or later with psycopg and requests (pip install "psycopg[binary]" requests). The psycopg 3 docs, September 2026, cover connections and transactions.
A LeadOcean API key from app.leadocean.io. The free plan has no card and 1,000 records, one-off.
The key and your connection string as environment variables. Neither goes in a table or in Git.

bash

export LEADOCEAN_API_KEY="paste-your-key-here"
export DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"

Step 1: Add the columns the job writes to

Keep the raw answer in jsonb and pull out the fields you query. Rows start with enriched_at empty, which is how the job finds its work.

sql

create table if not exists leads (
  id            bigint generated always as identity primary key,
  linkedin_url  text,
  email         text,
  enriched_at   timestamptz,
  enrich_status text,          -- matched | not_found
  work_email    text,
  email_status  text,
  phone         text,
  profile       jsonb
);

create table if not exists companies (
  id          bigint generated always as identity primary key,
  domain      text not null,
  enriched_at timestamptz,
  company     jsonb
);

create index if not exists leads_pending on leads (id) where enriched_at is null;
create index if not exists companies_pending on companies (id) where enriched_at is null;

insert into leads (linkedin_url) values ('https://www.linkedin.com/in/janedoe-example');
insert into companies (domain) values ('acme.com');

Both rows are placeholders. Swap in your own data. The partial indexes keep the pending lookup fast once the table is large.

Step 2: Write the enrichment script

The script claims up to 50 pending rows per table, calls POST /v1/people/enrich with reveal_email true, and writes the result back. It sends one request at a time, which stays under the 5 requests a second on Free.

python

import os, sys, time  # save as enrich.py
import psycopg
import requests
from psycopg.types.json import Jsonb

API = "https://api.leadocean.io"
HEADERS = {"x-api-key": os.environ["LEADOCEAN_API_KEY"],
           "Content-Type": "application/json"}
BATCH = 50

class Stop(Exception):
    pass

def call(path, body):
    for _ in range(5):
        r = requests.post(API + path, headers=HEADERS, json=body, timeout=30)
        if r.status_code == 429:
            time.sleep(int(r.headers.get("Retry-After", "1")))
            continue
        if r.status_code == 404:
            return None                  # nobody matched, costs 0
        if r.status_code in (401, 402):
            raise Stop(f"HTTP {r.status_code}")
        r.raise_for_status()
        return r.json()
    raise Stop("still 429 after 5 tries")

stopped = None
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    with conn.cursor() as cur:
        try:
            cur.execute("""
                select id, linkedin_url, email from leads
                where enriched_at is null
                order by id limit %s
                for update skip locked""", (BATCH,))
            for id_, linkedin_url, email in cur.fetchall():
                key = {"linkedin_url": linkedin_url} if linkedin_url else {"email": email}
                res = call("/v1/people/enrich", {**key, "reveal_email": True})
                if res is None:
                    cur.execute("update leads set enriched_at = now(), "
                                "enrich_status = 'not_found' where id = %s", (id_,))
                    continue
                data = res.get("data", {})
                contact = data.get("contact_data", {})
                emails = contact.get("contact_emails") or [{}]
                phones = contact.get("contact_phones") or [{}]
                cur.execute("""
                    update leads set enriched_at = now(), enrich_status = 'matched',
                      work_email = %s, email_status = %s, phone = %s, profile = %s
                    where id = %s""",
                    (emails[0].get("email"), contact.get("email_status"),
                     phones[0].get("phone"), Jsonb(data.get("profile_data")), id_))

            cur.execute("""
                select id, domain from companies
                where enriched_at is null
                order by id limit %s
                for update skip locked""", (BATCH,))
            for id_, domain in cur.fetchall():
                res = call("/v1/companies/enrich", {"domain": domain})
                payload = Jsonb(res.get("data")) if res else None
                cur.execute("update companies set enriched_at = now(), company = %s "
                            "where id = %s", (payload, id_))
        except Stop as e:
            stopped = e

if stopped:
    sys.exit(f"stopped early: {stopped}")

The with block commits when it ends. On a 401 or 402 the script stops calling, still commits the rows it already paid for, then exits with an error. A bad key or spent records never loops, and no matched row is bought twice.

Step 3: Run it by hand first

Insert two or three test rows, then run the script once.

bash

python3 enrich.py
psql "$DATABASE_URL" -c "select id, enrich_status, work_email, email_status from leads;"

A matched row shows matched and a work email. A row we hold nothing for shows not_found.

Step 4: Schedule it

Plain Postgres has no scheduler of its own, so run the script from cron. This line runs it at the top of every hour and appends the output to a log.

bash

0 * * * * cd /opt/enrich && /usr/bin/python3 enrich.py >> enrich.log 2>&1

Load the two environment variables in the crontab or a wrapper script. Fifty rows an hour is 1,200 rows a day. Raise BATCH for a larger backlog, and keep the one-at-a-time loop under your plan's rate limit.

Step 5: Read the results

Query the table. Only verified and catch_all_valid are safe to send to.

sql

select id, work_email, email_status, phone
from leads
where enrich_status = 'matched'
  and email_status in ('verified', 'catch_all_valid');

Add catch_all only if you accept that the domain takes every address and the mailbox is unconfirmed. Drop invalid, spam_trap, abuse and disposable. LeadOcean gives no refund for bounced emails, so filter before you send.

What you get

The email_status list the query relies on comes from leadocean_list_enum_values, a free call, run on 2026-10-21.

json

{"name":"email_status","total":13,"matched":13,"values":["verified","catch_all_valid","catch_all","risky","unknown","untested","invalid","role","disposable","spam_trap","abuse","derived","none"],"truncated":false}

Each matched person returns this shape. It is the response shape from the API reference, with placeholder values.

json

{
  "success": true,
  "data": {
    "profile_data": {
      "profile_full_name": "Jane Doe",
      "profile_headline": "VP Sales at Acme"
    },
    "contact_data": {
      "has_email": true,
      "email_status": "verified",
      "contact_emails": [{ "email": "jane.doe@acme.com", "type": "work", "status": "verified" }],
      "contact_phones": [{ "phone": "+14155550133", "type": "mobile" }],
      "contact_current_experiences": [{ "company_name": "Acme", "company_domain": "acme.com", "job_title": "VP Sales" }]
    }
  }
}

Person enrich also returns career history in resume_data when held: past roles, education and certifications. The script does not store it. Add it to profile if you want it.

What it costs

One record is one person or one company you receive. A people enrich that matches is 1 record, and a company enrich that matches is 1 record. A 404, a count and an empty page cost nothing.

On Free, the 1,000 records are one-off, so the hourly job stops with a 402 once they are spent. On a paid plan there are no credits. Solo is $97 a month and Agency is $497 a month, each with a fair-use pace instead of a balance. See pricing for the rest.

Troubleshooting

Error	Cause	Fix
401	The `x-api-key` header is missing or the key is wrong.	Re-export `LEADOCEAN_API_KEY` with a fresh key.
402	Your records are spent. Free is 1,000, one-off.	Check `GET /v1/account`, or compare plans on pricing.
404	We hold nothing for that identifier.	Nothing to fix. It costs 0, and the script marks the row `not_found`.
429	Requests came faster than the plan's rate limit: 5 a second on Free, 200 on Agency.	Wait the `Retry-After` seconds. The script sleeps and retries.
Rows never update	The script failed before commit, or `enriched_at` is already set.	Read `enrich.log` and check `where enriched_at is null`.
Empty search results	A search filter used a value that is not an exact enum string.	Call `leadocean_list_enum_values` for the exact string.

FAQ

Can I enrich by name and company domain?

Not directly. Enrich takes exactly one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by person_id.

Will the job re-spend records on rows it already enriched?

No. It only selects rows where enriched_at is null, and it sets that column on a match and on a 404.

How do I size a company list before I spend anything?

Use GET /v1/companies/count or the leadocean_count_companies MCP tool. Both are free and return an exact total.

Can I use pg_cron instead of system cron?

Yes, if your host allows the extension. pg_cron runs SQL, so it would call a function or an HTTP extension, and the Python script is simpler to test.

Do other warehouses work the same way?

The call is identical. See Snowflake, Databricks and BigQuery. More recipes are in the blog hub.

Enrich your Postgres rows on a schedule, free

Free to start. No credit card. 1,000 records to spend whenever you like.

Get your free API key →