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

``json
{
"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.

$3

- 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.

$3

| 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.

$3

| 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.

$3

| 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.