@flatfile/blueprint

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.

Option	Description	Default	Required?
key	The system name of this field. Primarily informs JSON and egress structures.		✅
type	One of string, number, boolean, date, enum, reference. Defines the handling of this property.		✅
label	A user-facing descriptive label designed to be displayed in the UI such as a table header.	key
description	A long form description of the property intended to be displayed to an end user.
constraints	An array of system level Validation Rules meant to be applied after hooks are run.	[]
config	Configuration relevant to the type of column. See property documentation below.	{}	Sometimes
multi	Will allow multiple values and store / provide the values in an array if set. Not all field types support arrays.	false
meta	Arbitrary object of values to pass through to hooks and egress	{}

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

Option	Description	Default
decimalPlaces	The number of decimal places to preserve accuracy to. Overages should be automatically rounded with a warning. A hook can pre-format to accomplish floor or ceil.	0

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

Option	Description	Required?
ref	Full path reference to another sheet/table configuration. Must be in the same workbook.	✅
relationship	The type of relationship this defines. Can be one of has-many or has-one	✅

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

Option		Description	Default	Required?
allowCustom		Permit the user to create new options for this specific field.	false
options[]		A list of valid options the user can select from		✅
	value	The value or ID of this option. This value will be sent in egress		✅
	label	A visual label for this option, defaults to value if not provided
	color	An optional color to assign this option
	icon	A reference pointer to a previously registered icon
	meta	An arbitrary JSON object to be associated with this option and made available to hooks

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.

Option	Description	Default
allowIndeterminate	Allow an indeterminate (null) option that is neither selected or unselected.	true

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.