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.