Transaction Reconciliation Process

Scope

This document defines the JSON file format, Cloud Storage upload path, and reconciliation rules.

Summary

When a JSON file is uploaded to the reconciliation bucket, an automated process compares reported transactions against database records.

File location

Bucket (safe example)

gs://example-conciliation-bucket

Path structure

principals/<principal-id>/conciliation/<filename>.json

Parameter	Description
<principal-id>	Principal identifier (without exposing real emails or real service accounts).
<filename>	Descriptive file name, ideally date-based.

Valid path example (anonymized)

gs://example-conciliation-bucket/principals/demo-principal-001/conciliation/conciliacion-2026-03-11.json

JSON file format

Structure

{
  "fechatransaccion": "YYYY-MM-DD",
  "items": [
    {
      "id": "string",
      "monto": 0,
      "referencia": "string",
      "fecha": "ISO 8601 timestamp",
      "sku": "string"
    }
  ]
}

Field definitions

Field	Type	Required	Rule
fechatransaccion	string	Yes	YYYY-MM-DD format in Mexico City time.
items	array	Yes	Minimum of one item.
items[].id	string	Yes	Unique transaction identifier.
items[].monto	number	Yes	Amount in MXN, up to 2 decimals.
items[].referencia	string	Yes	Transaction reference (phone or folio).
items[].fecha	string	Yes	ISO 8601 timestamp in UTC.
items[].sku	string	Yes	Product or service SKU.

Important timezone rule

items[].fecha is provided in UTC, but reconciliation is evaluated against fechatransaccion in Mexico City time.

Full example

File: transacciones-2026-02-13.json

{
  "fechatransaccion": "2026-02-13",
  "items": [
    {
      "id": "TX-55146",
      "monto": 30.0,
      "referencia": "6640000001",
      "fecha": "2026-02-14T00:25:51.98872Z",
      "sku": "A030"
    },
    {
      "id": "TX-55147",
      "monto": 100.0,
      "referencia": "6640000002",
      "fecha": "2026-02-14T00:29:44.530341Z",
      "sku": "A100"
    },
    {
      "id": "TX-55148",
      "monto": 50.0,
      "referencia": "6640000003",
      "fecha": "2026-02-13T20:15:30.123456Z",
      "sku": "A050"
    }
  ]
}

Timezone: UTC vs Mexico

For fechatransaccion: "2026-02-13" (Mexico), the queried UTC range is:

• Start: 2026-02-13 06:00:00 UTC
• End: 2026-02-14 05:59:59 UTC

Included transactions (example):

• 2026-02-13T08:00:00Z
• 2026-02-14T00:25:51Z
• 2026-02-14T05:59:00Z

Not included:

• 2026-02-14T06:00:00Z (already belongs to the next day in Mexico)

File upload

Choose the language or tool you prefer:

gsutil

gsutil cp transacciones-2026-03-11.json \
  gs://example-conciliation-bucket/principals/demo-principal-001/conciliation/

gsutil ls gs://example-conciliation-bucket/principals/demo-principal-001/conciliation/

Python

from google.cloud import storage

client = storage.Client()
bucket = client.bucket("example-conciliation-bucket")

principal_id = "demo-principal-001"
filename = "transacciones-2026-03-11.json"
blob_name = f"principals/{principal_id}/conciliation/{filename}"

blob = bucket.blob(blob_name)
blob.upload_from_filename(filename)

print(f"Uploaded file: {blob_name}")

Node.js

const { Storage } = require("@google-cloud/storage");

const storage = new Storage();
const bucketName = "example-conciliation-bucket";
const principalId = "demo-principal-001";
const filename = "transacciones-2026-03-11.json";
const destination = `principals/${principalId}/conciliation/${filename}`;

async function uploadFile() {
  await storage.bucket(bucketName).upload(filename, { destination });
  console.log(`${filename} uploaded to ${destination}`);
}

uploadFile();

cURL

# Use environment variables and placeholders; do not publish real tokens.
ACCESS_TOKEN="<temporary-access-token>"

curl -X POST \
  "https://storage.googleapis.com/upload/storage/v1/b/example-conciliation-bucket/o?uploadType=media&name=principals/demo-principal-001/conciliation/transacciones-2026-03-11.json" \
  -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  -H "Content-Type: application/json" \
  --data-binary @transacciones-2026-03-11.json

Reconciliation results

Successful reconciliation

Total in file: 10
Total in database: 10
Matches: 10
Missing in database: 0
Missing in file: 0
Amount discrepancies: 0

Detected differences

Transactions missing in database:

Total in file: 12
Total in database: 10
Matches: 10
Missing in database: 2
Missing in file: 0
Amount discrepancies: 0

Transactions missing in file:

Total in file: 8
Total in database: 10
Matches: 8
Missing in database: 0
Missing in file: 2
Amount discrepancies: 0

Amount discrepancies:

Total in file: 10
Total in database: 10
Matches: 8
Missing in database: 0
Missing in file: 0
Amount discrepancies: 2

References

• Cloud Storage Documentation