npm package: @flatfile/blueprint
Description: The universal DDL for defining data structures across Flatfile.
Latest version: 0.0.15

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

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