# NocoDB Compatibility Contract

Phase 1 status: investigation and compatibility design only. No production data, schema, schedules, credentials, or scraper behavior were changed.

## Verified Current NocoDB Identity

Verified from read-only `nocodb bases list`, `nocodb tables list`, `nocodb request GET /api/v1/db/meta/tables/mswfxq541pe6khe`, and read-only PostgreSQL metadata via `dbx exec nocodb-postgres`.

| Item | Verified value |
| --- | --- |
| Workspace id | `wr326moe` |
| Base/project id | `pc0fmz2i62ewg6n` |
| Base title | `Luxeillum Leads` |
| Source id | `bvdjbqo5f8yg6vn` |
| Lead table id | `mswfxq541pe6khe` |
| Lead table title | `Scraper Leads` |
| Physical table | `pc0fmz2i62ewg6n."Scraper Leads"` |
| Approx row count sampled by API | `4,170,561` on 2026-07-04 |

Credential values are intentionally omitted.

## Verified Schema

The table has one database unique constraint: primary key on physical column `id`. Read-only SQL showed no unique constraint on `osm_type`, `osm_id`, `osm_url`, website, phone, or source fields.

| Title | Column | Noco type | DB type | Required | Default | Current owner |
| --- | --- | --- | --- | --- | --- | --- |
| Id | `id` | ID | integer | yes | sequence | NocoDB |
| CreatedAt | `created_at` | CreatedTime | timestamp | no | system | NocoDB |
| UpdatedAt | `updated_at` | LastModifiedTime | timestamp | no | system | NocoDB |
| nc_created_by | `created_by` | CreatedBy | varchar | no | system | NocoDB |
| nc_updated_by | `updated_by` | LastModifiedBy | varchar | no | system | NocoDB |
| nc_order | `nc_order` | Order | numeric | no | system | NocoDB |
| nc_row_meta | `nc_row_meta` | Meta | jsonb | no | system | NocoDB |
| job_id | `job_id` | SingleLineText | text | no | blank/null | scraper |
| country | `country` | SingleLineText | text | no | blank/null | scraper |
| country_name | `country_name` | SingleLineText | text | no | blank/null | scraper |
| country_code | `country_code` | SingleLineText | text | no | blank/null | scraper |
| job_status | `job_status` | SingleLineText | text | no | blank/null | scraper refresh |
| osm_type | `osm_type` | SingleLineText | text | no | blank/null | OSM scraper |
| osm_id | `osm_id` | SingleLineText | text | no | blank/null | OSM scraper |
| osm_url | `osm_url` | URL | text | no | blank/null | OSM scraper |
| name | `name` | SingleLineText | text | no | blank/null | scraper, smart refresh |
| category | `category` | SingleLineText | text | no | blank/null | scraper, smart refresh |
| subcategory | `subcategory` | SingleLineText | text | no | blank/null | scraper, smart refresh |
| website | `website` | URL | text | no | blank/null | scraper, website enrichment, review |
| phone | `phone` | PhoneNumber | varchar | no | blank/null | scraper, smart refresh |
| email | `email` | Email | varchar | no | blank/null | scraper, smart refresh |
| address | `address` | LongText | text | no | blank/null | scraper, smart refresh |
| raw_tags_json | `raw_tags_json` | LongText | text | no | blank/null | OSM scraper merge |
| source_bbox_json | `source_bbox_json` | LongText | text | no | blank/null | scraper merge |
| scraped_at | `scraped_at` | DateTime | timestamp | no | current sync time | scraper refresh |
| lead_created_at | `lead_created_at` | DateTime | timestamp | no | blank/null | scraper |
| lead_updated_at | `lead_updated_at` | DateTime | timestamp | no | blank/null | scraper refresh |
| query_name | `query_name` | SingleLineText | text | no | blank/null | scraper |
| source | `source` | SingleLineText | text | no | blank/null | scraper |
| place_id | `place_id` | SingleLineText | text | no | blank/null | Google scraper |
| cid | `cid` | SingleLineText | text | no | blank/null | Google scraper |
| data_id | `data_id` | SingleLineText | text | no | blank/null | Google scraper |
| maps_link | `maps_link` | URL | text | no | blank/null | Google scraper |
| all_subcategories | `all_subcategories` | LongText | text | no | blank/null | scraper merge |
| categories_json | `categories_json` | LongText | text | no | blank/null | Google/Foursquare |
| complete_address_json | `complete_address_json` | LongText | text | no | blank/null | Google/Foursquare |
| review_count | `review_count` | Number | bigint | no | blank/null | Google/Foursquare |
| review_rating | `review_rating` | Decimal | numeric | no | blank/null | Google/Foursquare |
| business_status | `business_status` | SingleLineText | text | no | blank/null | scraper |
| price_range | `price_range` | SingleLineText | text | no | blank/null | Foursquare |
| raw_json | `raw_json` | LongText | text | no | blank/null | non-OSM scrapers |
| city | `city` | SingleLineText | text | no | blank/null | scraper |
| area | `area` | SingleLineText | text | no | blank/null | scraper |
| state_region | `state_region` | SingleLineText | text | no | blank/null | scraper |
| postcode | `postcode` | SingleLineText | text | no | blank/null | scraper |
| lead_country | `lead_country` | SingleLineText | text | no | blank/null | scraper |
| Enriched Luxeillum | `Enriched_Luxeillum` | Checkbox | bool | no | false | hospitality enrichment |
| foursquare_id | `foursquare_id` | SingleLineText | text | no | blank/null | Foursquare scraper |
| __nc_deleted | `__nc_deleted` | Deleted | boolean | no | false | NocoDB |
| foursquare_url | `foursquare_url` | URL | text | no | blank/null | Foursquare scraper |
| Last modified time | virtual | LastModifiedTime | virtual | no | system | NocoDB |
| Email Found | `Email_Found` | Checkbox | bool | no | false | email enrichment |
| Blocked Luxeillum | `Blocked` | Checkbox | bool | no | false | enrichment/blocklist |
| Enriched Saas | `Enriched_Saas` | Checkbox | bool | no | false | SaaS enrichment |
| query_display_name | `query_display_name` | SingleLineText | text | no | blank/null | scraper |
| selector_id | `selector_id` | SingleLineText | text | no | blank/null | scraper |
| industry_slug | `industry_slug` | SingleLineText | text | no | blank/null | scraper |
| Website Found by Scraper | `Website_Found_By_Scraper` | Checkbox | bool | no | false | website enrichment |
| Website Last Checked At | `website_last_checked_at` | DateTime | timestamp | no | blank/null | website enrichment |
| Website Enrichment Status | `website_enrichment_status` | SingleLineText | text | no | blank/null | website enrichment service |
| Website Enrichment Reason | `website_enrichment_reason` | LongText | text | no | blank/null | website enrichment service |
| Website Review Candidates | `Website_Review_Candidates` | LongText | text | no | blank/null | website enrichment/review |
| Website Review Summary | `Website_Review_Summary` | LongText | text | no | blank/null | website review |
| Website Review Status | `Website_Review_Status` | SingleLineText | text | no | blank/null | website review |
| Website Review Selected URL | `Website_Review_Selected_URL` | URL | text | no | blank/null | website review |
| Blocked SaaS | `Blocked_SaaS` | Checkbox | bool | no | false | SaaS workflow |
| Offer URL | `Offer_URL` | URL | text | no | blank/null | SaaS/redesign workflow |
| Prospect URL | `Prospect_URL` | URL | text | no | blank/null | SaaS/redesign workflow |

Unknown ownership is protected.

## Current OSM Insert Payload

Verified in `src/nocodb.js:409` `buildRecord()`. The OSM scraper inserts only fields that exist in the target table.

```json
{
  "query_name": "derived from job taxonomy",
  "query_display_name": "job keyword/display label",
  "selector_id": "job selector id",
  "industry_slug": "derived taxonomy slug",
  "source": "OSM",
  "job_id": "scraper job uuid",
  "country": "job country input",
  "country_name": "Nominatim display name or country",
  "country_code": "Nominatim country_code or blank",
  "job_status": "job.status",
  "osm_type": "node|way|relation",
  "osm_id": "OSM numeric id as string",
  "osm_url": "https://www.openstreetmap.org/{type}/{id}",
  "name": "OSM name tag or blank",
  "category": "first matched business category tag value",
  "subcategory": "cuisine, tourism:type, brand, operator, or blank",
  "all_subcategories": "joined array, currently blank for OSM leads",
  "website": "normalized OSM website/contact:website or blank",
  "phone": "trimmed OSM phone/contact:phone or blank",
  "email": "lowercase OSM email/contact:email or blank",
  "business_status": "CLOSED or blank",
  "address": "addr:full or assembled address",
  "city": "addr city/town/village/municipality/hamlet",
  "area": "addr suburb/district/neighbourhood/county",
  "state_region": "addr state/province/region/state_district",
  "postcode": "addr:postcode",
  "lead_country": "addr:country or is_in:country",
  "raw_tags_json": "JSON.stringify(tags)",
  "source_bbox_json": "JSON.stringify(source bbox)",
  "scraped_at": "lead updated/created time or now ISO",
  "lead_created_at": "lead created time or null",
  "lead_updated_at": "lead updated time or null"
}
```

Null/blank behavior: `buildRecord()` sends empty strings for most scalar fields, `null` for `lead_created_at` and `lead_updated_at` when absent, JSON strings for tag/bbox fields, and ISO timestamps from JavaScript `new Date().toISOString()`. NocoDB API returns timestamps as `YYYY-MM-DD HH:mm:ss+00:00`.

## Current Lookup And Refresh Behavior

Verified in `src/nocodb.js:453-820`.

Direct match fields: `email`, `data_id`, `place_id`, `cid`, `maps_link`, `osm_id`, `osm_url`, `foursquare_id`, `foursquare_url`.

Lookup-only field: `phone`.

Website fallback is only considered for incoming records without direct-match values. It matches same normalized website plus same normalized name, phone, or address.

Phone fallback is only considered after website fallback and requires same normalized phone plus same normalized name, website, or address.

Refresh policy:

| Field set | Current behavior |
| --- | --- |
| `job_status`, `scraped_at`, `lead_updated_at` | Always refresh if different. |
| `all_subcategories` | Token merge. |
| `raw_tags_json`, `source_bbox_json` | JSON merge. |
| `review_count`, `review_rating` | Replace only with larger numeric value. |
| Empty existing fields | Fill from meaningful incoming values. |
| Non-replaceable fields | Never replace once meaningful: source/job/country ids and source ids/URLs. |
| Smart fields | Replace only if incoming value scores better: name, category, subcategory, website, phone, email, address, city, area, state, postcode, lead_country. |

Current sync limits: `SYNC_BATCH_SIZE = 50`, `UPDATE_BATCH_SIZE = 25`, `QUERY_CHUNK_SIZE = 25`, request timeout default `30s`, sync window default `45s`, GET/PATCH retry max 3 with 750 ms exponential base. POST creates are not retried by `apiRequest()` because only GET/PATCH are in `RETRYABLE_METHODS`.

## Field Ownership Matrix

| Field or relationship | Current writer | Meaning | New OSM service may insert | May refresh | Must preserve |
| --- | --- | --- | --- | --- | --- |
| `job_id`, `job_status` | current scraper | Source run identity/status | yes, with new service run id/status | `job_status` only | existing values on adopted rows |
| `source` | scrapers | Source label (`OSM`, `gmaps`, etc.) | yes (`OSM`) | no | yes |
| `osm_type`, `osm_id`, `osm_url` | OSM scraper | OSM identity | yes | only if adopting exact blank fields | yes |
| `query_name`, `query_display_name`, `selector_id`, `industry_slug` | scraper taxonomy | Targeting/category provenance | yes | no on adopted rows | yes |
| `name`, `category`, `subcategory` | scraper | Business/category data | yes | conservative smart refresh only | yes if manually/workflow changed |
| `website` | scraper, website enrichment/review | Official website | yes if source has it | only with source-baseline proof and no enrichment/review ownership | yes |
| `phone`, `email` | scraper, enrichment | Contact data | yes | only with source-baseline proof or proven source refresh | yes |
| `address`, `city`, `area`, `state_region`, `postcode`, `lead_country` | scraper | Location data | yes | only with source-baseline proof or proven source refresh | yes if downstream touched or provenance unknown |
| `business_status` | scraper | OSM lifecycle status | yes | yes only from OSM source | preserve workflow edits |
| `raw_tags_json`, `source_bbox_json` | scraper | Raw/source context | yes | sidecar preferred; JSON merge if Noco-owned | preserve |
| `scraped_at`, `lead_created_at`, `lead_updated_at` | scraper | Source sync times | yes | `scraped_at`/`lead_updated_at` only | `lead_created_at` |
| Google/Foursquare fields | other scrapers | Non-OSM source identity | no | no | yes |
| Review/rating fields | non-OSM scrapers | External review data | no | no | yes |
| `Enriched_Luxeillum`, `Enriched_Saas` | email enrichment scripts | Source row processed | no | no | yes |
| `Email_Found` | email enrichment | Email signal found | no | no | yes |
| `Blocked`, `Blocked_SaaS` | enrichment/blocklist | Excluded from downstream | no | no | yes |
| Website enrichment/review fields | website enrichment/review | Enrichment state/review audit | no | no | yes |
| `Offer_URL`, `Prospect_URL` | SaaS/redesign workflow | Downstream generated assets | no | no | yes |
| Noco system fields | NocoDB | Record lifecycle | no | no | yes |

Fields written by the old OSM scraper are not automatically protected merely because they are nonblank. `website`, `phone`, `email`, `address`, location fields, category fields, `name`, `business_status`, and raw source fields may remain source-owned only when the source-baseline sidecar proves the current value matches the adopted source payload, a proven source refresh, or the corresponding adopted `raw_tags_json`. Unknown provenance is protected.

## Noco Access Abstraction

The production `Scraper Leads` table has approximately 4.17 million rows. The replacement must not perform large adoption scans through small paginated API loops when a read-only database path is available, but it also must never write directly to the NocoDB PostgreSQL database.

Repository boundary:

```text
NocoReadRepository
NocoWriteRepository
```

Phase 2A.5 implements only the read boundary. Its public method surface is
exactly `read_schema()` and `iter_pages(request)`. Requests use bounded keyset
pagination, exact schema fingerprints, a fixed scalar projection, and detached
immutable row values. The PostgreSQL implementation accepts an injected
fetch-only executor, verifies read-only transaction state, and has no arbitrary
query, transaction, commit, or mutation method. Fixture reads implement the
same port for offline tests. `NocoWriteRepository` remains unimplemented.

Recommended behavior:

- `NocoReadRepository` uses a restricted read-only PostgreSQL connection for bulk adoption scans, reconciliation reads, duplicate analysis, relationship checks, and schema-drift detection.
- `NocoWriteRepository` uses the NocoDB API for all production creates, updates, and deletes.
- Direct PostgreSQL writes to NocoDB are forbidden.
- The read implementation remains replaceable so small tests can use Noco API fixtures or local fixtures instead of PostgreSQL.
- A final duplicate/precondition check runs immediately before every API write.
- Schema drift is detected before each production reconciliation and any mismatch fails closed.
- No credentials are stored in this document or in generated reports.

The read repository may inspect physical table metadata and row values, but production mutation authority belongs only to the Noco API adapter. If the API write fails after sidecar planning, the action remains unapplied and must be retried idempotently or marked failed; the system must not repair it with a direct SQL update.