Extending the Schema

This page describes how to add or change tables and columns in the Farm Data Model. It is intended for developers contributing to fdm-core, and assumes familiarity with The Asset–Action Model and the Naming Conventions.

The schema is the contract that every other package in the monorepo depends on, and it is deployed against databases holding production farm records. Changes are therefore additive and reversible by default, and are accompanied by a generated migration, updated documentation and a changeset.

Where the schema lives

The schema is defined in TypeScript with Drizzle ORM, in fdm-core/src/db/:

File	PostgreSQL schema	Notes
schema.ts	fdm	The farm data model. Most changes belong here.
schema-authz.ts	fdm-authz	Roles, permissions and the audit trail.
schema-calculator.ts	fdm-calculator	Cached calculation results.
schema-authn.ts	fdm-authn	Generated by better-auth with pnpm db:generate-authn. Not edited by hand.

Deciding what to model

The first question is whether the new information describes an asset or an action.

A useful test is whether the information has a date on which it occurred. If it does, it is an action and belongs in its own table, even when it initially appears to be a property of an existing asset. The link between a field and the farm managing it is an example: it is modelled as the field_acquiring action, carrying a start date and a tenure type, because a farm can take a field into use and later release it.

Two consequences follow from the model and are worth stating explicitly:

• Do not store derived values. A column holding a computed total or balance duplicates information already present in the recorded actions, becomes inconsistent when an underlying record is corrected, and fixes a calculation method in place. Record the individual events and let fdm-calculator derive the aggregate. Where a calculation is expensive, its result is cached in the fdm-calculator schema.
• Do not delete assets. An asset that is no longer in use is ended through a dedicated action table, such as field_discarding or cultivation_ending, so that the history recorded against it remains valid.

Structured information belongs in typed columns. A jsonb column is appropriate only where the payload is genuinely open-ended, such as an arbitrary snapshot in the audit trail; it should not be used as a general-purpose metadata container, because its contents cannot be validated, queried efficiently, or migrated reliably.

Table definition

Tables follow a consistent shape. The example below shows the elements that are expected on every table in the fdm schema.

export const cultivations = fdmSchema.table(
  "cultivations",
  {
    b_lu: text().primaryKey(),
    b_lu_catalogue: text()
      .notNull()
      .references(() => cultivationsCatalogue.b_lu_catalogue),
    b_lu_yield: numericCasted(), // kg DM / ha
    created: timestamp({ withTimezone: true }).notNull().defaultNow(),
    updated: timestamp({ withTimezone: true }),
  },
  (table) => [uniqueIndex("b_lu_idx").on(table.b_lu)],
)

export type cultivationsTypeSelect = typeof cultivations.$inferSelect
export type cultivationsTypeInsert = typeof cultivations.$inferInsert

• The exported constant is the camelCase form of the table name (fieldAcquiring for field_acquiring).
• created and updated are present on every table.
• The primary key carries a unique index named <column>_idx; composite keys use primaryKey({ columns: [...] }), as in field_acquiring.
• The inferred TypeSelect and TypeInsert types are exported, as the rest of the monorepo imports them rather than redeclaring shapes.
• Identifiers are text columns populated by createId() from fdm-core/src/id.ts. Database-generated sequences are not used.

Column types

• Numeric agronomic values use numericCasted(), which stores the value as SQL numeric and parses it to a JavaScript number; it exists because Drizzle otherwise returns numeric columns as strings. Floating-point column types are not used for measured values.
• Timestamps are always timestamp({ withTimezone: true }).
• Geometry uses the geometry custom type and requires a GiST index, for example index("b_geom_idx").using("gist", table.b_geometry).
• Each numeric column carries a trailing comment stating its unit, for example // kg N / ha.

Enumerated values

An enumerated column is declared as an exported array of { value, label } pairs together with a PostgreSQL enum. The array is exported because fdm-app uses it to render the options, and the enum's name in PostgreSQL matches the column it is used for.

export const soilAnalysisSourceOptions = [
  { value: "nl-rva-l006", label: "Eurofins Agro" },
  { value: "other", label: "Overig" },
]

export const soilAnalysisSourceEnum = fdmSchema.enum(
  "a_source",
  soilAnalysisSourceOptions.map((x) => x.value) as [string, ...string[]],
)

Values are stable machine codes and labels are the display text. Where a national code list applies, its codes are used unchanged.

Making the change

1. Edit the schema file, applying the naming and structural conventions above.
2. Generate the migration: cd fdm-core && pnpm db:generate. Drizzle Kit compares the schema against the recorded migration state and writes SQL to fdm-core/src/db/migrations/. Migrations are not written by hand, and migrations that have already been merged are never edited.
3. Review the generated SQL. Confirm that it performs the intended change and nothing more. An unexpected DROP COLUMN, or an ALTER COLUMN ... SET NOT NULL on a table that already holds data, indicates a destructive change; renames and type changes need an explicit data-migration step so that existing rows are preserved.
4. Update the core functions in the corresponding fdm-core/src/<domain>.ts, together with its <domain>.types.d.ts and its colocated <domain>.test.ts.
5. Update the documentation: Database Schema and the relevant Core Concepts page. The documentation is the published description of the model.
6. Run the tests: cd fdm-core && pnpm exec dotenvx run -- vitest run src/<domain>.test.ts. The tests run against a real PostgreSQL instance with PostGIS enabled, configured through the POSTGRES_HOST, POSTGRES_PORT, POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD environment variables. See Setting up a Local Environment.
7. Check the conventions: pnpm check-schema from the repository root, described below.
8. Add a changeset: pnpm changeset, selecting @nmi-agro/fdm-core and any affected downstream package. See Releasing FDM.
9. Rebuild before working downstream. Other packages import the built output of fdm-core, so run pnpm turbo build before fdm-app or fdm-calculator will see the new types.

Pull requests target the development branch.

Migrations are applied automatically when fdm-app starts, so a schema change without a committed migration file will not reach any database.

Authorization

If the change introduces a resource that users can be granted access to, it is registered in fdm-core/src/authorization.ts, which defines the resources, the roles owner, advisor and researcher, and the actions read, write, list and share. See Authorization.

Public functions in fdm-core take the FDM instance and the acting principal as their first two arguments, run mutations inside a transaction, verify access with checkPermission before reading or writing data, and wrap thrown errors with handleError so that failures carry context. Filtering a query by farm identifier is not a substitute for a permission check.

Automated convention check

A check script verifies the conventions that can be validated mechanically:

pnpm check-schema             # reports errors; run in CI
pnpm check-schema -- --strict # additionally lists numeric columns without a unit comment

It validates column naming per schema, the presence of created and updated on tables in the fdm schema, the exported inferred types, the correspondence between an enum's PostgreSQL name and its column, and the use of jsonb. The script requires no database and no build step, and runs on every push as the Schema Conventions workflow.

Genuine exceptions are recorded in the allowlist at the top of .github/skills/fdm-schema/scripts/check-schema-conventions.mjs, each with a stated reason.