Blueprint

@flatfile/blueprint is the point of reference for working with customer-defined data structures in Flatfile. We have a lot of different data problems to solve for across different systems such as the DAL, Pipelines, Workbooks, Turntable, and the SDK.

Objectives

• Describe any reasonable structure of data as defined by external systems
• Inform underlying databases engines on how best to store and retrieve data
• Inform rendering engines (such as tables or forms) on how best to render data points
• Inform matching or AI systems of ontological classifications of data
• Provide guidance to end-users that is specific to the data structure
• Provide configuration for database driven validations
• Report for analytics or future optimization purposes, what user or system defined hooks are running

Term Considerations

• Property — Describes an attribute of the entity (eg. name, email) and how it should behave.

Example Configuration

{
  "properties": [
    {
      "key": "code",
      "label": "Product Code",
      "type": "string",
      "description": "Unique identifier defining an individual product.",
      "constraints": [
        {
          "type": "unique",
          "config": {
            "caseSensitive": false
          }
        }
      ],
      "config": {
        "size": "tiny"
      }
    },
    {
      "key": "description",
      "type": "string"
    },
    {
      "key": "price",
      "type": "number",
      "config": {
        "decimalPlaces": 2
      }
    },
    {
      "key": "category",
      "label": "Product Category",
      "type": "enum",
      "multi": true,
      "config": {
        "allowCustom": false,
        "options": [
          {
            "value": 9,
            "label": "Kitchenware",
            "icon": "pots-and-pans",
            "color": "#f00000",
            "meta": {
              "product_code_prefix": "KI-"
            }
          },

          {
            "value": 9,
            "label": "Clothing",
            "meta": {
              "product_code_prefix": "CL-"
            }
          }
        ]
      }
    }
  ]
}

Core Properties

All properties have the following options. If a property type ends with [] (such as string[]) it should be stored, retrieved, and presented as an array of unlimited size.

string 🧵 (default)

Defines a property that should be stored and read as a basic string. Database engines should expect any length of text to be provided here unless explicitly defined in the config.

Configuration Options

• size: enum(tiny, normal, medium, long) — How much text should be storeable in this field?
  • tiny = up to 255 characters
  • ➡normal = 64kb (default)
  • medium = 16mb
  • long = 4gb

number #️⃣

Defines a property that should be stored and read as either an integer or floating point number. Database engines should look at the configuration to determine ideal storage format.

Configuration Options

reference 🔗

Warning

This is not comparable to a foreign key. There should be no specification of which column to reference by as it is multi-variate. A user may establish a relationship in mapping with a soft reference like a company name, once stored in our system we will reference by our internal IDs, and on egress we provide the known system_id if available.

Defines a reference to another sheet. Links should be established automatically by the matching engine or similar upon an evaluation of unique or similar columns between datasets.

Configuration Options

enum

Defines an enumerated list of options for the user to select from. Matching tooling attempts to resolve incoming data assigment to a valid option. The maximum number of options for this list is 100. For larger lists, users should use the reference or future lookup types.

Configuration Options

boolean

A true or false value type. Matching engines should attempt to resolve all common ways of representing this value and it should usually be displayed as a checkbox.

date

Store a field as a GMT date. Data hooks must convert this value into a YYYY-MM-DD format in order for it to be considered a valid value. Datetime should be a separate and future supported value as it must consider timezone.

Future Types

1. Linked Property — Populate this property with a value or values from a referenced table - data hooks can further transform. Should support filters.
   • Example 1: Show a readonly value on a contact based on the country of a linked company
2. Rollup — Use a datahook to sum or compute all the referenced values. Should support filtering.
   • Example 1: The sum() of all items.price * items.quty linked to an invoice
   • Example 2: The count of deals with status: lost linked to a salesperson
3. Groups/Sets — An arbitrarily named group of records that can either be merely grouped under a namespace or repeated similar to a relational set.
   • Example 1: List of contact_methods with a type, value, and priority.
   • Example 2: address group with street, city, ...
4. Remote Lookup — a type of field that relies on a datahook to return valid options instead of an enum list or a reference table.
   • Example 1: This field must be a valid flight number for the provided city and date. Users should be able to select from a list of valid flights when interacting with the cell.
   • Example 2: Global autocomplete for address entry. As you type, a set of suggestions appear in a dropdown.