View as Markdown

4. Data: tables, flows and schedules

4.1 Two kinds of data

  • Small data saved by tools (settings, lists, notes): saved automatically, personal or shared (chapter 5.2).
  • Tables: datasets from a few rows to a few million, imported from CSV files or made by SQL blocks. Each change makes a new version.

4.2 Tables

  • Import: Workshop → Add → Import a file (table), or Import a file in a flow. Choose a CSV; the separator and the encoding are detected. Table name: lowercase letters, digits and _, starting with a letter. Importing into an existing table creates a new version of it.
  • Open a table: two tabs, Data (sort, filter, pages of 100 rows) and Versions (who made each version, and restore an older one). A large table (more than 2,000,000 rows or 300 MB) shows its first 200 rows only: the folder's Runner computes it (7.10).
  • Export: CSV or SQLite.
  • Tools can change rows (add, edit, delete) with sql.exec (chapter 5.3). Changes are kept in the table's journal and saved into a new version automatically every 500 changes, or before an import, an undo or a block that rewrites the table. Everyone sees them at once. A change that became impossible (for example the same id created twice at the same moment) is set aside and shown in the table's history.
  • Protected tables (lock icon): in a shared folder, a table can keep each member's rows apart. Each member sees and changes only the rows they wrote; the folder's managers (Can manage) see them all, with who wrote each row (member_name). Some columns can be reserved for managers (an approval, a status), and some values unique across all members (a time slot). Such a table is created by a manager when a tool that needs it is added (chapter 5.5). It doesn't appear in members' Workshop; for managers, its Versions tab becomes Members (who wrote, how many rows). It can't be imported into, undone or read by a block yet.
  • Tables with a rule (lock icon): in an organisation, a table can give each person only the rows of their values in the organisation's directory (their region, their agency…), and a linked table follows its main table (chapter 3.9). Admins see every row. Only a block run by an admin can read it whole: that is how a summary table is made (chapter 9.12).
  • Size: in Blackslate's storage, a table version is limited to about 50 MB compressed (several hundred thousand rows). With your own storage (chapter 6), 5 GB compressed per version; beyond 2,000,000 rows a table is large and the folder's Runner computes it on its disk (tens of millions of rows, 7.10).

4.3 Flows and blocks

A flow is a set of tables and the SQL blocks that turn them into results, shown as a graph.

  • New flow (Workshop → Add), or Import a flow… (a flow your AI wrote, 4.6). In the flow: Import a file, Add ▾ (New block, Fetch from a web address 4.5, Send to a web service 4.12, Read from a warehouse 4.7, Run in a warehouse 4.10; on a phone, in ⋯), Build all (or Build on my Runner when blocks run on the Runner, 7.10), Schedule; and in ⋯: Copy for my AI, Update from…, Export (4.6).
  • A block has three tabs:
    • Inputs: the tables it reads, each under a short name (alias) used in the script.
    • Transformations: the SQL script (SQLite 3.49, see 5.3). It must create the output, by default create table out as select …. Copy context for my AI copies a description of the inputs (with 3 sample rows if you tick it) and the rules, ready for your AI to write the SQL.
    • Outputs: Published table (the name of the result) and Created by the script as (out), plus Storage: Latest version only (default), Keep 3 versions, Keep 10 versions, Temporary (deleted after 1 to 72 hours, rebuilt when needed).
  • Dry run runs without publishing; Run publishes; Run with everything after it runs the blocks that depend on it. A failed block stops the ones after it. A block that reads a large table runs on the folder's Runner: Try it on my Runner instead (7.10).

4.4 Schedules

A flow's Schedule panel:

  • When: every N minutes, every day at a time, or some days of the week at a time, in your time zone (summer time respected).
  • On: which device runs it.
    • This device (a browser or the installed app): runs only when Blackslate is open on it. A missed time is reported ("Blackslate wasn't open on the device").
    • A Runner (" · Runner, always on"): runs around the clock, even when Blackslate is closed (chapter 7).
  • Run: all blocks, or only what changed (the blocks whose script or input tables changed since their last run; the blocks that read from outside — a web address, a data warehouse — or run in a warehouse always run, since what they read may have changed).
  • Schedules (left column of Workshop) lists every schedule and every device.

A schedule pauses itself when the flow is changed by someone else, when you lose write access, or when its device is removed. Review it and resume it. Notifications tell you when a scheduled run fails, is missed or is paused.

4.5 Fetch from a web address

A block can take its rows from a web address: an API, or a CSV or JSON file published online. In the flow: Fetch from a web address.

  • Source (its first tab): the Address (https only), the Key if the address needs one, the Format (JSON or CSV) and, for JSON, where the rows are (data.items; empty: the whole answer).
  • The answer becomes the table fetched, all columns as text, like an imported file (JSON: one column per key, nested values as JSON text). Transformations shapes it with SQL like any block, by default create table out as select * from fetched. The block may also read other tables (Inputs, optional).
  • Try it on my Runner asks the folder's Runner to read the address now and shows the first rows. Nothing is published.
  • It runs on the folder's Runner only (chapter 7, Runner 1.4 or later), when the flow is scheduled: Dry run, Run and Build all in the browser can't read a web address. A schedule runs it at most every 15 minutes.
  • Keys (Add a key, for the folder's owner): a name, the address it is only sent to (api.example.com), how it is sent (the header Authorization: Bearer …, another header, or a parameter of the address), and the key. It is kept encrypted in Blackslate's vault, never shown again, and given only to the folder's Runner, only for that address. Members who can edit the folder see the keys by name. Changing a key's address needs the key again.
  • Safety: https and public addresses only (the Runner refuses an address that leads to a private or local network, like a NAS or a home router), no redirection followed, 20 MB and 30 seconds at most per read. The source is part of the block: a change by someone else pauses its schedule until it is reviewed (4.4).
  • Ask my AI for help (in Source) gives your AI the address (values that look like keys hidden), the format, the last try and the SQL; never the key.
  • Several pages (JSON): many APIs give their rows by pages. In Source, Several pages says how the API gives the next one: the next address (in the answer, like links.next, or in its Link header; followed only on the same address name), a page number (page=1, 2, 3…, until a page is empty), an offset (offset=0, 100, 200…, until a page is shorter), or a cursor read in the answer and sent back. At most: 20 pages by default, 50 at most; 20 MB and 2 minutes for all the pages together. Try it on my Runner says how many pages it read, and when an answer shows signs of pages (a next address, a Link header, a next_cursor), it offers Read all the pages.
  • The other way, sending rows to a service: Send to a web service (4.12). Not yet: signing in with an account (OAuth).

4.6 Flows as text: your AI writes the flow

A whole flow can be written as text, in an open format (leavenit-flow, JSON), by your AI. You paste it in Blackslate, try it, and if something fails or isn't what you wanted, you give your AI what it needs and paste its new version.

  1. Start: Workshop → Add → Import a flow… → Copy the format and the tables for my AI. Paste it in your conversation with your AI and say what the flow must produce (the columns and a few rows of each table of the folder are in it).
  2. Import: paste your AI's answer in Import a flow… (the words and the ```json fence around it don't matter), or Choose a file. Before anything is saved, Blackslate shows each block (the tables it reads → the table it writes), what is wrong in the text (said so that your AI can fix it), and the tables the folder doesn't have yet. Import creates the flow in the open folder.
  3. Try: Build all runs the blocks in order. If one fails, Copy for my AI (in the red message, or in ⋯) copies the flow, what went wrong, the tables it reads (columns and 3 rows of each) and the notes about the flow (chapter 3.10). Paste it to your AI.
  4. Update: ⋯ → Update from… → paste the corrected flow. Blocks are matched by name: Blackslate shows those changed, new and deleted before saving. A changed block keeps its history (each save is a version). Deleting a block needs Can manage, and it can't be brought back: Export first to keep a copy. Then Build all again.
  5. Export (⋯): downloads the flow in the same format (<name>.flow.json), to keep a copy, to put it in another folder (Import a flow… there), or to give it to your AI to change.

The format, for your AI (it is also in the text copied by the buttons above):

{
  "format": "leavenit-flow",
  "version": 1,
  "flow": { "name": "Sales summary", "description": "optional, 500 characters at most" },
  "blocks": [
    {
      "name": "Sales by type",
      "inputs": ["sales", "clients"],
      "output": "sales_by_type",
      "sql": "create table out as\nselect c.type, sum(s.amount) as amount\nfrom sales s join clients c on c.id = s.client_id\ngroup by c.type\norder by c.type;"
    }
  ]
}
  • inputs: the tables the block reads; in its SQL each one is a table under its own name. A short name: {"table": "sales", "as": "s"}. At least one input (a fetch block may have none). 10 at most.
  • output: the table the block writes. Its sql (SQLite 3.49, chapter 5.5) must create the table out: create table out as select …. If it creates another table: "output": {"table": "sales_by_type", "from": "result"}.
  • A block can read the output of another block: Blackslate runs them in the right order. Block names are unique in a folder (1 to 80 characters), each output is written by one block only, and there is no loop.
  • storage (optional): latest (default), keep3, keep10, or temporary with "hours" (1 to 72) (4.3).
  • warehouse (optional): the block reads one of the folder's data warehouses (4.7), by its name: {"name": "Redshift prod", "query": "select …", "limit": 1000000} (one select, in that warehouse's own SQL), or {"name": "Redshift prod", "table": "sales.orders", "columns": ["order_id", "amount"], "where": "order_date >= '2026-01-01'"}; files of a data lake: {"name": "Data lake", "path": "orders/", "format": "parquet", "query": "select … from files"}. Its SQL reads the table fetched. On import, Blackslate links the name to the folder's warehouse of that name (capitals don't matter) and says so if there's none. Never a password in a flow, and Export never writes one.
  • send (optional): the block also sends its table to a warehouse of the folder that allows writing (4.8), by its name: {"name": "Redshift prod (writing)", "table": "clean_orders", "mode": "replace"} ("append", or "upsert" with "key": ["order_id"]; a data lake: "format": "parquet" or "csv"). On import, Blackslate checks that the warehouse allows writing.
  • web_send (optional): the block sends each row of its result to one of the folder's web services, one call per row, each row once (4.12): {"service": "api.shop.com", "method": "POST", "address": "/v1/orders/{order_id}/ship", "body": "row", "once_by": "order_id"}. Its output is the log of what was sent. Never a key in a flow: the folder's web service has it.
  • fetch (optional): the block reads a web address (4.5): {"url": "https://api.example.com/orders", "format": "json", "path": "data.items"} (csv too; "pages" as in 4.5). Its SQL reads the table fetched. Never put a key in a flow: on import, Blackslate gives the block the folder's key for that address (if there are several, you choose), and Export never writes it.
  • No data in a flow: only table names. A table it reads must be in the folder (imported) or written by one of its blocks; until it is, the blocks that read it fail with "not found".
  • Not imported: schedules (set one once the flow works). A schedule set by someone else pauses when the flow is updated, as when a block is changed by hand (4.4).
Message What to do
"This isn't valid JSON" the text is cut or has something added inside: ask your AI for the whole flow again, in one JSON code block
"A block with this name is already in this folder, in the flow …" the flow is already here: open it and use Update from…, or rename the blocks
"The table … is already written by the block …" another block of the folder writes that table: choose another output name
"… isn't in this folder" import that table (Import a file) before Build all, or ask your AI to read another one
"Its SQL must create the table "out"" the script must end with a create table out as select …

4.7 Read from a data warehouse

A block can read a data warehouse your company already has: a database (PostgreSQL, Amazon Redshift, MySQL, SQL Server, Oracle, ClickHouse…), a cloud warehouse (Snowflake, BigQuery, Databricks, Athena, Trino) or files in a data lake (Parquet, CSV or JSON files in Amazon S3, Google Cloud Storage, Azure Blob Storage or any S3-compatible storage). The warehouse runs the query; only its result comes into Blackslate, as a table.

  • Add a warehouse (the folder's owner): Workshop, ⋯ on the folder → Data warehouses → + Add a warehouse. Choose the kind (16 kinds, in 3 families; a search finds them: "aurora", "parquet"…), fill in its settings (the address, the database, the user…) and its sign-in details (a password, a key pair, a token, a service account key, an access key…), then Test the connection: the folder's Runner connects and says the version, how many schemas and tables it sees, and whether the account can only read. Save.
    • Not in the list? Many databases speak PostgreSQL (CockroachDB, YugabyteDB, Greenplum, Materialize…) or MySQL (TiDB, SingleStore, StarRocks, Doris…): choose that kind.
    • An account that can only read: Blackslate only reads, and such an account makes sure of it. Copy the SQL for a read-only account (or Copy the read-only permissions for the cloud kinds) gives the commands for the warehouse's administrator. If the account can write, the test says so ("can write"), without blocking.
    • Where the Runner connects from: the Runner connects from where it runs. For a database in a private network (Redshift in a VPC, a server at the office), the Runner must run in that network, or its address must be allowed by the warehouse. Connects through chooses which Runner of the folder (when there are several). The cloud warehouses (Snowflake, BigQuery, Databricks, Athena, the Redshift Data API) are reached over the internet: no network to open.
    • The sign-in details are kept in Blackslate's vault, never shown again (to change one, type it again; the others are kept), given only to the folder's Runner, for the run or the request that needs them. Nothing goes through Blackslate's servers: rows travel from the warehouse to the Runner, then to the folder's own storage.
    • Members who can edit the folder see the warehouses by name and state, never their settings or sign-in details. A warehouse can't be deleted while a block reads it (the message names the blocks).
  • Browse (in Data warehouses): the schemas, their tables and views with the number of rows estimated, a search over all the tables, and for the table chosen its Columns and a Preview of 20 rows, read on demand by the Runner (nothing is kept). For a data lake: its folders and files; a folder's Rows reads its files as one table. Use in a new flow… creates a flow with a block that reads the table chosen. Copy the tables for my AI copies the list of tables (with the columns of the one chosen).
  • The block: in a flow, Read from a warehouse. Its Source tab:
    • Warehouse: one of the folder's warehouses.
    • A table: the table (Browse… to choose it), its Columns (none ticked: all), Only the rows where (a filter, in the warehouse's own SQL) and At most. The query sent to … shows the query, exactly as it is sent (with one row more than At most, to know when there are more).
    • A query: one select (or with … select), in the warehouse's own SQL, written by you or your AI. Do the heavy work there (filters, joins, sums): a table of 200 million rows stays in the warehouse, and only the summary comes into Blackslate. Copy the warehouse's tables for my AI gives your AI the tables to write it.
    • Files of a data lake: Files (a file, a folder, or a pattern like orders/*.parquet or orders/**.parquet) and Format (Parquet, CSV, JSON / NDJSON). The files are read as one table named files (DuckDB's SQL): files with different columns are joined by name, and folders like year=2026/ become a column.
    • At most: 1,000 to 50,000,000 rows (1,000,000 by default; beyond 5,000,000, Runner 1.10). Beyond it, the block stops with a clear message: add a filter or a summary, or raise At most. Beyond 2,000,000 rows the table is large: the Runner computes it on its disk (7.10).
    • Try it on my Runner: the warehouse counts the rows and the Runner shows the columns and 20 rows. Nothing is published.
    • Ask my AI for help: the kind of warehouse, the query, the last try and the block's SQL; never a password or a key.
  • The result is the table fetched, with its types kept (numbers, dates as text 2026-01-31, true/false as 1/0, nested values as JSON text). Transformations shapes it with SQL like any block, by default create table out as select * from fetched. The block may also read other tables (Inputs, optional).
  • It runs on the folder's Runner only (Runner 1.7 or later), when the flow is scheduled or built there (Build on my Runner, 7.10): Dry run and Run in the browser can't read a warehouse. 2 hours at most per read (Runner 1.10; 30 minutes before). Like a web address, a change of the block by someone else pauses its schedule until it is reviewed (4.4).
  • Blackslate only reads. A query that isn't one select is refused before it is sent; reading happens in a read-only transaction wherever the warehouse has one (PostgreSQL, Redshift, MySQL, Oracle), and ClickHouse requests are sent read-only. The Runner reads the files of a data lake in its own folder only, and deletes them after the read.
  • Sending a table back into the warehouse: 4.8; a tool querying the warehouse live: 4.9. Not yet: reading only the new rows at each run, Apache Iceberg and Delta tables (their Parquet files can be read as files), Teradata, SAP HANA, IBM Db2.
Message What to do
"The Runner can't reach the warehouse (…)" check the host and the port, and that the Runner's network may reach it (same network, or its address allowed by the warehouse)
"The warehouse refused the sign-in: …" check the user and its password or key (Edit, type it again)
"The result has more than … rows (At most)" add a filter or a summary to the query, or raise At most
"The query must be one select …" Blackslate only reads: remove what changes the warehouse, and what comes after a ";"
"This block reads a data warehouse: it runs on the folder's Runner…" schedule the flow on the folder's Runner, or use Try it on my Runner
"This warehouse is reached through another Runner…" schedule the flow on that Runner, or change Connects through
"The folder's Runner didn't answer in time…" check that the Runner is on (⋯ → Runners) and that it is version 1.7 or later

4.8 Send a table to a data warehouse

The other way: a block's table can go back into the warehouse, after each run. A cleaning flow starts from a table of the warehouse, and its final table returns to the warehouse at each scheduled run, for the company's other tools (dashboards, BI) to read.

  • Allow writing (the folder's owner): in the warehouse's settings (⋯ on the folder → Data warehouses → Edit), the part Writing (off by default): Blocks may send tables to this warehouse, Only in the schema leavenit (a data lake: Only in the folder leavenit/). Blackslate creates its tables in this schema only, and never changes a table it didn't create. Copy the SQL for an account that writes only in "leavenit" gives the commands for the warehouse's administrator: an account that reads the warehouse and writes only in this schema. Test the connection also creates a table in the schema and removes it ("writes in "leavenit" only ✓"), and says if the account could write elsewhere. Either one connection that reads and writes, or two connections (one to read, one to write), each with its account. The list shows "writes in leavenit".
  • The block: in its Outputs tab, Send to a warehouse, On: the Warehouse (those that allow writing), the Table (leavenit. + its name: letters, digits and _, starting with a letter), and Each run:
    • Replace the table: the warehouse gets exactly this table. It is filled on the side first and swapped in at the end (in one go where the warehouse can: PostgreSQL, Redshift, MySQL, SQL Server, Snowflake, ClickHouse; BigQuery and Databricks replace it in one operation): never half written.
    • Add the new rows: each run adds its rows at the end. For a log or a history.
    • Update by key: rows with the same key (one or more columns that identify a row) are replaced, the others added. The key must be unique in the block's table (checked first). Not for ClickHouse, nor files.
    • Files of a data lake: Files leavenit/<name>/ and Format (Parquet or CSV). Replace: one file, data.parquet, replaced at each run (the other files of that folder are removed). Add: one more file per run, named by its date.
  • The Blackslate table stays, as usual: the warehouse gets a copy. Send now from my Runner sends the current published table right away, as the schedule will.
  • The block then runs on the folder's Runner only (Runner 1.8 or later), when the flow is scheduled; its SQL doesn't change, and Dry run still works in the browser. If the send fails, the Blackslate table is published anyway, the error is in Last runs (column Warehouse) and the flow's run is said failed on the Schedules page; the next blocks still run.
  • Types: whole numbers, numbers with decimals and text, with the warehouse's own types (bigint, double precision, varchar…), from what each column really holds (a column with one text value becomes text). Dates stay ISO text (2026-01-31), easy to convert in the warehouse. BigQuery and Parquet take letters, digits and _ in column names: others become _.
  • Kinds: PostgreSQL (and family), Redshift, MySQL (and family), SQL Server, Oracle, ClickHouse, Snowflake, BigQuery, Databricks, and files in S3, GCS, Azure or S3-compatible storage. Not Athena, Trino nor the Redshift Data API (use a direct Redshift connection).
  • Volume: the published table (tens of millions of rows for a large table, Runner 1.10, 7.10), 2 hours at most per send. Towards Redshift, rows go in grouped inserts: a few minutes for a million rows.
  • Security: only the folder's owner turns writing on and chooses the schema; a member who writes a block only chooses the connection and the table's name, in that schema. Two blocks can't send to the same table. A change of the block by someone else pauses its schedule until it is reviewed (4.4).
Message What to do
"leavenit.clean_orders already exists in … and wasn't created by Blackslate: choose another name." choose another table name in Outputs
"The block "…" already sends to leavenit.clean_orders: choose another name." choose another table name, or change the other block
"The columns changed (new: discount): choose Replace for one run, or change the block back." Add and Update by key keep the table's columns: send once with Replace
"order_id is not unique in this table (3 rows have 98120431): Update by key needs one row per key." fix the block's SQL, or choose a key that identifies a row
""…" no longer allows writing (Writing is off in its settings)." the owner turns Writing on again, or choose another warehouse, or turn Send off
"It can't create a table in "leavenit": …" (the test) give the account the right to create tables in that schema (Copy the SQL…)

4.9 Live queries from a tool

A tool can read a data warehouse live, each time it is opened (a store's dashboard, a customer card), without a flow or a table in between: it calls a live query the folder's owner declared, by its name, with values.

  • Declare (the folder's owner): Data warehouses → Live queries on a warehouse → + New query. Name (what the tool calls, like orders_by_store), What it gives (for you and your AI), the Query (one select, in the warehouse's own SQL; on a data lake, over the table files, with Files and Format). Each :name in the query is a parameter, found by Blackslate: its Type (Whole number, Number, Text, Date, Yes / no, or One of a list of values), and If the tool doesn't give it (Required, Empty, or a default value; a date can default to today-30). At most (100 to 10,000 rows) and Keep the answer for (from not at all to 1 hour: the same call within that time gets the same answer without asking the warehouse again). Try it on my Runner with example values shows the rows. Copy for my AI gives your AI the queries and how a tool calls them.
  • The values are never SQL: each one is checked against its type, then given to the warehouse as a value, with the warehouse's own parameters (Trino and Athena: a typed literal). A tool can't send its own query, nor change the declared one.
  • In the tool: window.leavenit.warehouse.query('orders_by_store', { store_id: 12 }) (chapter 5). The members see the queries' names, what they give and their parameters (Live queries), never their SQL.
  • Who can call: the folder's tools, for the people who can open them (members, read only too). Not the visitors of a public link, nor a copy of the tool in another folder.
  • Limits: 10,000 rows, 2 MB and 30 seconds per answer; 60 calls per minute per tool and person; 50 live queries per folder. Runner 1.9 or later. A warehouse can't be deleted while live queries use it.
Message What to do
"No live query named '…' in this folder (it has: …)" use one of the names listed, or ask the folder's owner to add it
"store_id must be a whole number (got 'douze')" give a value of the right type (the message says which)
"channel must be one of: web, store, app" give one of the values listed
"… took more than 30 seconds: the query needs a filter or a summary" the owner adds a filter or a summary to the query, or keeps the answer longer

4.10 Run in a data warehouse

When the data is too big to come into Blackslate (billions of rows), the block runs in the warehouse: its query, written in the warehouse's own SQL, runs there, and its result becomes a table of the warehouse. No row comes into Blackslate; Blackslate orders the blocks, schedules them, keeps the history of their SQL and says their failures. A block Read from a warehouse (4.7) then brings into Blackslate only what a tool needs.

  • Needs: a warehouse that allows writing (4.8: its owner turns Writing on, with its schema, leavenit), and the folder's Runner 1.11 or later. Kinds: PostgreSQL (and family), Redshift, MySQL (and family), SQL Server, Oracle, ClickHouse, Snowflake, BigQuery, Databricks. Not the files of a data lake, Athena, Trino nor the Redshift Data API.
  • The block: in a flow, Add ▾ → Run in a warehouse. Its tab In the warehouse:
    • Warehouse: those of the folder that allow writing ("writes in leavenit").
    • Creates the table: leavenit. + its name (letters, digits and _, starting with a letter; not lvn_…, kept for Blackslate's work tables).
    • Query: one select (or with … select), in the warehouse's own SQL. It may read any table the account can read, and the tables made by other blocks of the flow, named with their schema: leavenit.stores_clean. Blackslate finds them in the query and runs those blocks first ("Reads, from this flow: leavenit.stores_clean (block "Clean stores") — it runs after it."). A Read from a warehouse block that reads such a table (leavenit.daily_sales) also runs after the block that makes it, and a block that sends its table (4.8) before the blocks that read it in the warehouse.
    • Each run: Replace the table (computed on the side, swapped in at the end: never half written) or Add the new rows (for a history: each run adds its result; the columns must stay the same, or the run is refused and says which changed).
    • Try it on my Runner: the warehouse runs the query and the Runner shows its first 20 rows. Nothing is written.
    • Ask my AI for help: the kind of warehouse, its schema, the query and the last try; never a password or a key.
  • Blackslate wraps the select itself: create table … as select … (SQL Server: the select becomes a view for a moment, then select … into; BigQuery: create or replace table … as select). A query that isn't one select (or that would change the warehouse: insert, delete, drop…) is refused before it is sent. Never a table Blackslate didn't create: a table already there under that name is refused, untouched. Two blocks can't make the same table.
  • In the flow: the table of the warehouse is drawn in violet, under its name in the warehouse, with its rows ("PostgreSQL table · 40 rows"); the block says "in ". Clicking the table shows where it is, its rows, its columns, the block that makes it and those that read it. Outputs says where the table goes, and Last runs what each run did ("✓ 5,812,004 rows in leavenit.daily_sales · added · 1 min 2 s", or the warehouse's message).
  • It runs on the folder's Runner only, when the flow is built there (Build on my Runner) or scheduled: no Dry run nor Run in the browser. 2 hours at most per block. A failed block stops the ones after it, and the table it made before stays as it was. A change of the block by someone else pauses its schedule until it is reviewed (4.4).
  • In a flow written as text (4.6): "in_warehouse": {"name": "Ventes PG", "table": "daily_sales", "mode": "append", "query": "select …"}, with no inputs, output or sql.
  • Security: as for sending (4.8): only the folder's owner turns writing on and chooses the schema; the query runs with the warehouse's account, so it reads what that account can read. Use an account that reads what the flows need and writes only in that schema (Copy the SQL for an account that writes only in "leavenit").
Message What to do
"This block runs in a data warehouse: it runs on the folder's Runner…" use Build on my Runner, or schedule the flow on the folder's Runner
"Running a block in a warehouse needs Runner 1.11 or later" update the Runner (7.6)
""…" doesn't allow writing" the owner turns Writing on (4.8), or choose another warehouse
"leavenit.daily_sales already exists in … and wasn't created by Blackslate: choose another name." choose another table name
"The columns changed (new: discount): choose Replace the table for one run, or change the query back." Add keeps the table's columns: run once with Replace the table
"The query must be one select …" one select only: Blackslate makes the table from it

4.11 Dev, Test and Prod

Each shared folder has three versions: Prod, the one everyone uses, and Dev and Test, to try changes without touching it. Everything that existed before is Prod; Dev and Test start empty.

  • Each version has its own data: tables, flows, schedules, data warehouses, keys of web addresses and web services. Common to the three: the members and their rights, the sub-folders, the notes, the storage (the same bucket, each version in its own paths) and the Runner.
  • Each version has its own version of the folder's tools: a tool that lives in the folder has a version in Dev (its latest), in Test and in Prod. Every change of the tool goes to Dev first (by hand, with your AI, turning it into an app, restoring an old version): Prod keeps the version everyone uses until it is promoted. Edit tool says so ("This tool lives in … A change goes to Dev first … Prod keeps v12"), with its versions and Open in Dev; after saving, the tool opens in Dev. Its name, description and icon are common to the three versions. A tool gets versions once its folder uses Dev or Test; before, a change goes to everyone, as before.
  • Who: the switch Dev · Test · Prod, next to the folder's name, is for its admins and the members who can edit. Members who can only read see Prod only, as before. It always opens on Prod; the address says the version (&env=dev).
  • In Dev or Test, a band says so (violet for Dev, amber for Test), and what Promote would carry to the next version ("To promote to Test: 1 flow, 1 table.", or "Same as Test."). The first time, the version is empty: Copy from Prod brings Prod's flows and the structure of its tables (empty tables), never its rows, passwords or keys; or Start empty. Later: ⋯ on the folder → Copy from Prod….
  • Prod is everywhere: everyone gets a tool's Prod version: My tools, favorites, shortcuts in other folders, public links, the Showcase, the installed app, copies (Copy) and Export. Only Dev and Test, opened from the Workshop by the people who can edit, show their own version (in another folder too: a shortcut there follows the tool's versions). Its makers see in the tool's ⋯ when a newer version waits in Dev ("Dev has v14, not in Prod yet").
  • Promote: Promote to Test… (in Dev), Promote to Prod… (in Test), in the folder's bar; Promote… in the band of a flow's page (that flow only, ticked at first). It shows, item by item, what would change: a flow new, changed (block added, query changed, block removed) or the same; a table new (made empty) or with new or removed columns (the other version keeps its own rows; a removed column's values are deleted there, so it isn't ticked at first). It warns when someone changed the item directly in the other version since the last promotion: promoting replaces that change. An admin of the folder can also promote from Dev straight to Prod.
    • Warehouses and keys are found by name in the other version ("Ventes PG" in Test for "Ventes PG" in Dev), or chosen in the list. A flow whose warehouse has none there can't be promoted until one is added (⋯ on the folder → Data warehouses). A key missing there leaves the block without a key until one is added.
    • Tools (for their makers only): "v12 → v14", the changes of each version in between and Preview. Ticking a tool ticks the tables its new version uses that change (they are promoted first). In Prod, a tool of the Showcase that gets a new version goes back to review, as when it was changed before. The versions in Dev, Test and Prod are never removed from its history.
    • Schedules are copied with the flow when the other version has none (only those set on your own devices); a schedule already there is kept.
    • Flows are promoted all or nothing; the blocks run again there at the next build or schedule. Each promotion is in the folder's activity.
  • Only in Prod: public links, public calendars, tables published with a tool and the organisation's directory ("Only in Prod" in Dev and Test). Usage counts Prod only.
  • E-mails: in Dev and Test, an e-mail sent by a tool goes only to the person who sent it, with "[Dev]" or "[Test]" in its subject: an essay never reaches a client.
  • A tool opened in Dev (from the Workshop) runs its Dev version and reads and writes Dev's data, with a band at its top ("DEV data · the folder · v14", Open in Prod); in Test, its Test version with Test's data. From My tools, a favorite or a shared link, a tool always opens in Prod.
  • The Runner: the folder's Runner serves the three versions, on the same machine; it needs Runner 1.12 for Dev and Test (7.12).
  • My Workshop has no Dev or Test: to try things alone, make a shared folder with no one else in it.
  • Deleting the folder deletes its three versions.
Message What to do
"Only in Prod: public links, published tables and the directory…" switch to Prod for these
"The flow "…" uses the warehouse "…": add one in Test first…" add the warehouse in that version, or choose one in the list
"Only an admin of the folder can promote from Dev straight to Prod" promote to Test first, then from Test to Prod
"Dev and Test need Runner 1.12 or later" update the Runner (7.6)

4.12 Send to a web service

The other way of Fetch from a web address: a block that sends rows to a service (an online shop, a CRM, an invoicing or e-mailing service), one call per row. Its table of requests (orders to ship, contacts to add, invoices to make) is ordinary SQL; each row becomes one call. In a flow: Add ▾ → Send to a web service.

  • Only the folder's web services: the service must be declared by the folder's owner or an admin (⋯ on the folder → Web services, chapter 3), with its key if it needs one. A member who edits a flow can't send the folder's data anywhere else. The key stays in Blackslate's vault: the Runner adds it, it is never in the flow.
  • Send (its first tab): the Web service, the Method (POST, PUT, PATCH or DELETE), the Address, a path on the service where a column between braces is replaced by the row's value (/v1/orders/{order_id}/ship), the Body (the row's other columns as JSON, the column body that the SQL builds as JSON text, or nothing), and Each row is sent once, by: the column that identifies a row (order_id). Inputs and Transformations choose the rows to send, like any block: each row of out is one call.
  • Each row is sent once: the block's table (Result) is the log of what was sent, one line per row: the identifying column, status, http (the service's answer code), answer (its answer, 2,000 characters at most), tries, at. A row already sent is never sent again; a new row of out is sent at the next run. Another block, or a tool, can read the log (to mark an order "shipped" when the service said yes, or keep the identifier it returned).
    • sent: a 2xx answer.
    • failed: a 4xx answer (the service said no: sending it again as it is would say no again), or a row that can't be sent (a value of the address empty, a body over 64 KB). It waits: fix it, then Retry the failed rows (in Send) sends them again at the next run.
    • retry: a 5xx answer, no answer within 15 seconds, the service unreachable: sent again at the next runs, 5 tries at most (then failed). A 429 (too many requests) stops the run there: the rest goes at the next one.
    • simulated: see Dev and Test below.
    • Forget the sent rows… sends every row again at the next run, even those the service already received (only if the service ignores what it already has, or you want them twice).
  • Duplicates: a call left without an answer may have been received. Each call carries the header Idempotency-Key (the block and the row's value): services that know it (Stripe, Shopify and many others) ignore a call they already received.
  • Preview the calls computes the block's SQL here and shows the calls a run would make (the first three, and how many), with the rows already sent skipped. Nothing is sent by a preview. There is no "Try it on my Runner": a try would send a real request.
  • It runs on the folder's Runner only (Runner 1.13, chapter 7), when the flow is scheduled or built there (Build on my Runner): Dry run, Run and Build all in the browser can't send. At most 500 calls per run, 5 per second; the rest goes at the next runs. In a schedule "Only what changed", this block always runs (rows may wait for a retry). Same protections as Fetch: https, a public name, no private address, no redirection followed.
  • A change by someone else pauses its schedule until it is reviewed (4.4): the service, the method, the address and the body are part of the block.
  • In Dev and Test (4.11): each version has its own web services. If the version declares the service (the service's "sandbox", with its test key), the block sends for real; if it doesn't, the block simulates: nothing is sent, and its log says what would have been sent (status simulated). A simulated row isn't counted as sent: once the service is declared in that version, it is sent for real. Promoting the flow carries the block, never the web services nor their keys (Prod has its own). In Prod, a service that isn't declared stops the block without sending anything.
  • Web services (⋯ on the folder) says, for each service, the calls of the day from tools and from flows, and the flows that send to it. Remove stops it at once: the flows' rows wait.
Message What to do
"… is not a web service of this folder" its owner or an admin declares it (⋯ on the folder → Web services)
"The address uses {x}, but the rows to send have no column x" add the column to the SQL, or change the address
"Sending to a web service needs Runner 1.13 or later" update the Runner (7.6)
"This block sends its rows to a web service: it runs on the folder's Runner" Build on my Runner, or schedule the flow

4.13 Tables that people understand: descriptions

A table and each of its columns can say what it means. Most of it fills itself: where a column comes from (the SQL of the block that makes it), the description of the column it is copied from, and its values. The rest is one sentence, or drafted by your AI and then checked. For the tables of Blackslate and those of the folder's data warehouses.

  • In a table: each column's header has a small i (blue: described; amber: to check, or changed since; grey: not yet). It opens the column: what it means (with its unit), who wrote it and when, Where it comes from and Its values. The table's header says how many columns are described ("8 of 10 columns described · 1 to check") and, under its name, what the table is.
  • Where it comes from is read in the SQL of the block that makes the table: a column copied from another table (ventes.type), or the expression that computes it (sum(to_number(amount)), from ventes.amount, by type, month). Nothing to write, always up to date. Only the common shape is read (create table … as select … from … where … group by …, one table after another in the script); with with, union or a subquery in from, the column is only "computed by the block".
  • Inherited: a column copied as it is from another table (even through several blocks, or from a warehouse table read by a block) shows that column's description, marked inherited; it follows its source. Write another one gives it its own.
  • Its values (read in the table, here): the typical values, min – max, % empty and the number of distinct values. Not for a large table (the Runner computes it) nor a protected one.
  • Describe (on a column) or Edit: one sentence, and a unit or format if it has one (euros, days, AAAA-MM). About this table (in the table's header): what the table is, who makes it and from what, who reads it, and every column with what it means, where it comes from and its state.
  • Describe with my AI (in About this table): Blackslate prepares a text for your AI with the columns, their type, a few values per column, where they come from, who reads the table and what is already described (never a password or a key). Paste its answer: each description is shown, ticked when it is new (or only an AI wrote it before), not ticked when someone already checked that column. They arrive to check.
  • Four states: checked (written or checked by someone), to check (from an AI: It's right checks it), inherited, changed since (the SQL that computes the column changed after it was described: the description stays, with the old and the new calculation, until someone edits it or says It's right).
  • Who: everyone who can edit the folder writes and checks; members who can only read, read. Each description keeps who wrote it and when.
  • Where they serve then: Copy context for my AI (a block), Copy for my AI (a flow, the folder), the context a tool gives its AI for SQL: the descriptions are under the columns. The export of a tool (3.7) takes those of its tables, and they come back with a table its import makes. Promote (4.11) carries them with a table or a flow (a more recent description in the other version is kept); a promotion of descriptions alone isn't possible: they go with the next one.
  • A warehouse table (in Browse, its Columns): the comments the warehouse already has (PostgreSQL and Redshift COMMENT ON, MySQL, SQL Server, Oracle, ClickHouse, Snowflake, BigQuery, Databricks, Athena, Trino) are read, never changed; Blackslate's descriptions are the folder's, and a block that reads that table (as a table) passes them on. Profile these columns reads its first 10,000 rows through the Runner (Runner 1.14, 7.14), nothing kept. Without Runner 1.14, the warehouse's comments aren't read.