# CSV Foreign-Key Import Gate (`bread-kim/csv-foreign-key-import-gate`) Actor

Validate foreign-key relationships across multiple CSV files before import. Detect orphaned references, quarantine invalid rows, and export clean CSV files plus detailed validation reports.

- **URL**: https://apify.com/bread-kim/csv-foreign-key-import-gate.md
- **Developed by:** [bread kim](https://apify.com/bread-kim) (community)
- **Stats:** 3 total users, 2 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

$20.00 / 1,000 results

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## CSV Foreign-Key Import Gate

Private Apify Actor that audits up to five related inline CSV tables before import. It quarantines every duplicate primary-key member, empty required/key row, orphan, and descendant of an invalid parent. It never edits source values or invents replacements.

### Use

Provide `tables` and `foreignKeys` as in [`examples/input.json`](examples/input.json). Names and headers are safe identifiers; values are exact case-sensitive UTF-8 text. There is no trimming, case folding, numeric guessing, URL fetch, database access, AI, browser, proxy, or external enrichment. A completed audit returns `PASS` or `QUARANTINED`; quarantine is a useful business result, not an Actor failure.

The default dataset contains exactly one authoritative summary and no raw cells. `OUTPUT`, `REPORT.JSON`, `REPORT.MD`, `MANIFEST.JSON`, and `ACCEPTED_FILES.JSON` are in the default key-value store. `ACCEPTED_FILES.JSON` maps each table to a fixed uppercase, collision-resistant record key such as `ACCEPTED_DEPARTMENTS_<8_HEX>.CSV`. Download a table from:

`https://api.apify.com/v2/key-value-stores/{STORE_ID}/records/{RECORD_KEY}`

Accepted CSV files necessarily contain customer values. Keep the Actor private, restrict storage access, and configure/deal with Apify retention according to your policy. Reports and the dataset expose coordinates, codes, counts and hashes—not raw cells or per-cell hashes.

### Exact behavior

- BOM, quoted commas, multiline fields, CRLF, composite keys and valid FK cycles are supported through `csv-parse` 7.0.3.
- Empty means the exact empty string. A nullable composite FK may be entirely empty; a partially empty tuple is quarantined.
- Duplicate PK tuples quarantine all members. Descendant quarantine propagates to a fixed point. Mutually valid cycles remain accepted and are flagged.
- Leading zeros, whitespace, row order and cell text are preserved. CSV quoting/newlines may be canonically re-encoded without changing parsed values.
- Before spreadsheet export, cells beginning (after leading control/whitespace) with `=`, `+`, or `@` are quarantined. `-` is quarantined unless the remainder is a conservative decimal/scientific numeric form; exact values such as `-12.5` remain unchanged.
- Cycles may require deferred constraints or a database-specific import order. This tool does not promise general SQL readiness or compatibility with database types, checks, triggers, locale, or collation.

Limits: 2 MiB JSON input, 1–5 tables, 5000 total data rows, 64 columns/table, 16 KiB/cell, and 20 foreign keys. References may target only the declared parent primary key in exact order. Unknown fields, unsafe/prototype identifiers, duplicate headers/names, malformed CSV and recognized credentials/private keys are rejected without echoing CSV.

### CI and local development

Submit the same bounded input on each release and use `status`, hashes, counts, and stable issue codes as the gate. Future automation can retrieve `OUTPUT`, verify `MANIFEST.JSON`, and import only keys listed by `ACCEPTED_FILES.JSON`; it must still apply database-specific validation.

```sh
npm ci --ignore-scripts
npm test
npm run typecheck
npm run build
```

No-input local production startup with `CRAWLEE_STORAGE_DIR` writes `INVALID_INPUT` to `OUTPUT` and no dataset. `AUTOMATON_CONTAINER_SMOKE=1` runs only an embedded deterministic no-network analysis.

# Actor input Schema

## `tables` (type: `array`):

One to five named inline CSV tables, together limited to 5000 data rows and 2 MiB input.

## `foreignKeys` (type: `array`):

Up to twenty exact relationships; referenced columns must equal the target primary key in the same order.

## Actor input object example

```json
{
  "tables": [
    {
      "name": "accounts",
      "csv": "id,name\r\n01,Ada\r\n01,Duplicate\r\n02,Lin\r\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "name"
      ]
    },
    {
      "name": "orders",
      "csv": "id,account_id,note\n10,01,child of duplicate\n11,99,orphan\n12,02,\"accepted, link\"\n13,02,\"multi\nline\"\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "note"
      ]
    },
    {
      "name": "items",
      "csv": "id,order_id,amount\n100,10,-12.5\n101,12,007\n102,11,3\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "amount"
      ]
    }
  ],
  "foreignKeys": [
    {
      "table": "orders",
      "columns": [
        "account_id"
      ],
      "referencesTable": "accounts",
      "referencesColumns": [
        "id"
      ],
      "nullable": false
    },
    {
      "table": "items",
      "columns": [
        "order_id"
      ],
      "referencesTable": "orders",
      "referencesColumns": [
        "id"
      ],
      "nullable": false
    }
  ]
}
```

# Actor output Schema

## `results` (type: `string`):

Exactly one completed audit summary without raw cells.

## `outputRecord` (type: `string`):

Full result conforming to the versioned output contract.

## `jsonReport` (type: `string`):

Coordinate-only structured report.

## `markdownReport` (type: `string`):

Human-readable counts, status and limitations.

## `manifest` (type: `string`):

Checksums for every payload artifact.

## `acceptedFiles` (type: `string`):

Maps table names to fixed CSV record keys, checksums and row counts.

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "tables": [
        {
            "name": "accounts",
            "csv": "id,name\r\n01,Ada\r\n01,Duplicate\r\n02,Lin\r\n",
            "primaryKey": [
                "id"
            ],
            "requiredColumns": [
                "name"
            ]
        },
        {
            "name": "orders",
            "csv": "id,account_id,note\n10,01,child of duplicate\n11,99,orphan\n12,02,\"accepted, link\"\n13,02,\"multi\nline\"\n",
            "primaryKey": [
                "id"
            ],
            "requiredColumns": [
                "note"
            ]
        },
        {
            "name": "items",
            "csv": "id,order_id,amount\n100,10,-12.5\n101,12,007\n102,11,3\n",
            "primaryKey": [
                "id"
            ],
            "requiredColumns": [
                "amount"
            ]
        }
    ],
    "foreignKeys": [
        {
            "table": "orders",
            "columns": [
                "account_id"
            ],
            "referencesTable": "accounts",
            "referencesColumns": [
                "id"
            ],
            "nullable": false
        },
        {
            "table": "items",
            "columns": [
                "order_id"
            ],
            "referencesTable": "orders",
            "referencesColumns": [
                "id"
            ],
            "nullable": false
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("bread-kim/csv-foreign-key-import-gate").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "tables": [
        {
            "name": "accounts",
            "csv": """id,name\r
01,Ada\r
01,Duplicate\r
02,Lin\r
""",
            "primaryKey": ["id"],
            "requiredColumns": ["name"],
        },
        {
            "name": "orders",
            "csv": """id,account_id,note
10,01,child of duplicate
11,99,orphan
12,02,\"accepted, link\"
13,02,\"multi
line\"
""",
            "primaryKey": ["id"],
            "requiredColumns": ["note"],
        },
        {
            "name": "items",
            "csv": """id,order_id,amount
100,10,-12.5
101,12,007
102,11,3
""",
            "primaryKey": ["id"],
            "requiredColumns": ["amount"],
        },
    ],
    "foreignKeys": [
        {
            "table": "orders",
            "columns": ["account_id"],
            "referencesTable": "accounts",
            "referencesColumns": ["id"],
            "nullable": False,
        },
        {
            "table": "items",
            "columns": ["order_id"],
            "referencesTable": "orders",
            "referencesColumns": ["id"],
            "nullable": False,
        },
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("bread-kim/csv-foreign-key-import-gate").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "tables": [
    {
      "name": "accounts",
      "csv": "id,name\\r\\n01,Ada\\r\\n01,Duplicate\\r\\n02,Lin\\r\\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "name"
      ]
    },
    {
      "name": "orders",
      "csv": "id,account_id,note\\n10,01,child of duplicate\\n11,99,orphan\\n12,02,\\"accepted, link\\"\\n13,02,\\"multi\\nline\\"\\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "note"
      ]
    },
    {
      "name": "items",
      "csv": "id,order_id,amount\\n100,10,-12.5\\n101,12,007\\n102,11,3\\n",
      "primaryKey": [
        "id"
      ],
      "requiredColumns": [
        "amount"
      ]
    }
  ],
  "foreignKeys": [
    {
      "table": "orders",
      "columns": [
        "account_id"
      ],
      "referencesTable": "accounts",
      "referencesColumns": [
        "id"
      ],
      "nullable": false
    },
    {
      "table": "items",
      "columns": [
        "order_id"
      ],
      "referencesTable": "orders",
      "referencesColumns": [
        "id"
      ],
      "nullable": false
    }
  ]
}' |
apify call bread-kim/csv-foreign-key-import-gate --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,bread-kim/csv-foreign-key-import-gate"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/l2uQnzswtlJZLdUvn/builds/Q1AjxPncuq1rKwdge/openapi.json