A foreign key to any unique column

A form builder becomes a database the moment two forms need to point at each other.

In PaperPoint, forms are materialised as real PostgreSQL tables — form_<slug>, one column per field, migrated additively when the form changes. Once that is true, the obvious next request arrives: the site inspection form should let me pick a site from the site register, not retype its name.

That is a foreign key. And it is a foreign key with three properties no Django ForeignKey has:

• the target table did not exist when Django started;
• its columns are named by a user, in a studio, at runtime;
• the key being pointed at is not necessarily the primary key.

The third one is where the design gets interesting.

Two references, deliberately two field types

("data_select_form", _("Reference to another form")),
("data_select_column", _("Reference to a unique column")),

They look like a general case and a special case. They are not — they store different things, and the difference is the point.

data_select_form stores the target row’s UUID:

"""`data_select_form` field — FK to a row of another materialized form.

Configured with `target_form_slug` and optional `display_column`. The widget
queries `form_<target_form_slug>` directly at render time and populates a
`<select>` with `(id, display_column-or-id)` tuples.

Stored value is the target row UUID.
"""

data_select_column stores a business value:

"""`data_select_column` field — FK to an arbitrary UNIQUE column of another form.

Differs from `data_select_form`: the stored value is NOT the target row's id,
but the value of an arbitrary UNIQUE column on that row (e.g. SKU, email,
license plate). The target column MUST have a UNIQUE constraint at the PG
level — validation should happen at form-build time in the studio (not here).
"""

The instinct of anyone who has normalised a schema is that the second one is wrong: store the id, join for the value. That instinct is right for an application schema and wrong here, and it is worth being precise about why.

These tables are exported. Constantly. A form’s whole purpose is to produce rows that leave the system — as a spreadsheet handed to a warehouse, a CSV opened by an accountant, a report read by a client who has never heard of this platform. A column containing 3f2a…-…-9c81 is worth nothing in any of those places, and joining it back requires the exporter to know about a relationship that was declared in a form studio.

SKU-4417 is worth something in all of them. So when the target has a real business key, storing the business key is what makes the export self-contained.

The cost is paid where costs of this kind belong — as a constraint on the target:

The target column MUST have a UNIQUE constraint at the PG level.

Without it, the reference is ambiguous, and the field type stops meaning anything. That check lives in the studio, at the moment somebody configures the field, rather than in the field class — because the useful time to refuse is when the form is designed, not when a hundred people are filling it in.

Building SQL against a table nobody declared

Both fields read their options with raw SQL, because there is no model to query:

table = f"form_{target_slug}"
col = display_column or "id"
with connection.cursor() as cur:
    cur.execute(
        f'SELECT id::text, {col}::text FROM "{table}" WHERE _is_active = true ORDER BY 2'
    )
    return [(row[0], row[1] or row[0]) for row in cur.fetchall()]

Table and column names cannot be parameterised — %s binds values, not identifiers — so they are interpolated. Which makes the guard above them the load-bearing line of the module:

def _fetch_choices(target_slug: str, display_column: str | None) -> list[tuple[str, str]]:
    """Return `(id, display)` tuples from the target materialized table.

    Caller is responsible for having validated `target_slug` and `display_column`
    at form-build time; we assume trusted input here. A suspicious identifier
    raises ValueError rather than letting SQL injection slip through.
    """
    if not target_slug.replace("_", "").isalnum():
        raise ValueError(f"Refusing non-slug target: {target_slug!r}")
    if display_column and not display_column.replace("_", "").isalnum():
        raise ValueError(f"Refusing non-slug column: {display_column!r}")

Three things about this are worth copying.

It is an allowlist, not a denylist. replace("_", "").isalnum() accepts letters, digits and underscores and refuses everything else. There is no list of dangerous characters to keep up to date, and no quoting scheme to get subtly wrong.

It raises rather than sanitising. A name that fails the check is a configuration error, not input to be cleaned. Silently stripping characters would produce a query against a different, possibly existing table, which is worse than a loud failure.

It states its trust boundary out loud. Caller is responsible for having validated at form-build time; we assume trusted input here. Defence in depth with an explicit contract: the studio is the real gate, this is the backstop, and the docstring says which is which so nobody later removes the “redundant” check.

The sibling module runs the same guard over three identifiers in one loop:

for name in (target_slug, source_column, display_column or ""):
    if name and not name.replace("_", "").isalnum():
        raise ValueError(f"Refusing non-slug identifier: {name!r}")

The if name and is doing real work — display_column is optional, and an empty string is not a suspicious identifier, it is an absent one.

One type on the way out, the schema’s type on the way in

Every selected column is cast:

f'SELECT {source_column}::text, {display}::text '

"""
The stored value's PG type matches the target column's type; in Django form
space we always expose it as a string and let the SchemaManager cast on write.
"""

The target column might be text, integer, numeric or date — the studio decides, per form, at runtime. A <select> element has exactly one type, and it is string. So: cast to text on read, expose as string through the whole form layer, and hand the string back to the component that already knows every column’s declared type to cast on write.

The alternative — introspecting information_schema to discover the type and building a matching form field — is more clever and adds a second place that believes it knows the schema. One authority for types, consulted at write time, is the version that cannot drift.

Two more clauses appear in every one of these queries:

f'FROM "{table}" WHERE _is_active = true '
f'AND {source_column} IS NOT NULL ORDER BY 2'

_is_active is the soft-delete flag on materialised rows: a deleted site must not be offerable, but must remain readable on the inspections that already reference it. And IS NOT NULL on the source column matters specifically for the value-storing variant — a null business key would produce an option whose stored value is nothing, which passes validation and means nothing afterwards.

ORDER BY 2 sorts by the display column: the list is read by a human, and the order that matters is the one they see, not the one stored.

Validating a reference two different ways

class DataSelectFormField(forms.ChoiceField):
    default_error_messages = {
        "invalid_choice": _("This row does not exist in the target form."),
    }

    def clean(self, value):
        value = super().clean(value)
        if not value:
            return value
        try:
            uuid.UUID(value)
        except (ValueError, TypeError) as exc:
            raise ValidationError(self.error_messages["invalid_choice"]) from exc
        return value

class DataSelectColumnField(forms.ChoiceField):
    default_error_messages = {
        "invalid_choice": _("This value no longer exists in the target form."),
    }

    def clean(self, value):
        # ChoiceField validates value-in-choices; we just forward.
        return super().clean(value)

ChoiceField already checks membership in the choices — which were read from the target table at build time — so the second class adds nothing, and says so. The first adds a UUID parse, which is redundant with the membership check today and is cheap insurance for the day choices are loaded lazily or by AJAX.

The error messages are the part I would not let a reviewer merge away. This row does not exist in the target form and this value no longer exists in the target form describe two genuinely different situations: a reference to something that was never there, and a reference to something that has since been removed. The second is the common one — someone deactivates a site while an inspection form is open in another tab — and telling the user which of the two happened is the difference between “try again” and “ask who deleted the site”.

Degrading to a text box, on purpose

Every builder for these fields ends the same way:

def _build_data_select_form(field_model, field_class, **kwargs):
    cfg = getattr(field_model, "cross_form_config", {}) or {}
    slug = cfg.get("target_form_slug")
    if not slug:
        logger.error("data_select_form %s: missing target_form_slug", field_model.name)
        return forms.CharField(**kwargs)
    try:
        return field_class(
            target_form_slug=slug, display_column=cfg.get("display_column"), **kwargs
        )
    except Exception as exc:
        logger.error("data_select_form %s: %s", field_model.name, exc)
        return forms.CharField(**kwargs)

This is the rule the whole dynamic form layer runs on: a misconfigured field falls back to a CharField; it does not break the whole form.

For these two field types the rule earns its keep more than anywhere else, because their failure modes are external. The target form can be deleted. Its table can be dropped. A column can be renamed. None of those are actions taken by the person currently filling in the form, and all of them would otherwise turn one broken reference into a 500 on a page with thirty other answers on it.

What the fallback does not do is hide the problem: logger.error with the field name and the exception, every time the form is built. The user gets a degraded field, the operator gets a log line. The failure that would be unacceptable is the silent one — and it is worth noting the shape here is logger.error, not a bare except: pass.

The picker reads a different store

Here is the part of this feature I would flag in review, and it is worth writing down rather than smoothing over.

The form field reads the materialised table. The studio’s picker — the endpoint that populates the configuration UI and any AJAX search — reads the submissions JSON:

submissions_qs = template.submissions.all().order_by('-date_submitted')

if column:
    # `data` is a JSONField — distinct() over a JSON key isn't
    # portable across DB backends, so we fold in Python over the
    # (capped) submission set. Acceptable for the picker use-case.
    seen = {}
    for sub in submissions_qs.iterator():
        raw = (sub.data or {}).get(column)
        ...

Two stores for the same data, with different freshness and different semantics. The comment defends the Python-side fold honestly — portability, and a picker does not need to be exact — and the cap keeps it bounded. But the deeper seam is that a value present in the JSON and absent from the materialised table (or the reverse, after a schema migration) will make the picker and the field disagree.

That is a real limitation, it is not visible from either file alone, and the right note to leave next to it is: the materialised table is the authority; the submissions JSON is a convenience for the studio. Anything that makes a decision should read the table.

The row mode has a small, honest hack in it too:

for sub in submissions_qs[: limit * 2].iterator():
    # `limit * 2` upstream so the search filter still finds enough
    # matches — bounded by `limit` after filtering.

Fetch twice the cap so that filtering in Python still returns a full page. Approximate, bounded, and documented as such — which is the correct treatment for a type-ahead list, and would be the wrong treatment for anything counted.

The bug in the query-parameter name

My favourite comment in the endpoint:

q        case-insensitive substring filter on labels. (Named
         `q` rather than `search` because the parent ViewSet
         binds `?search=` to its global SearchFilter, which
         would narrow the FormTemplate queryset before
         `get_object()` and 404 us out.)

A custom DRF @action on a ViewSet inherits the ViewSet’s filter_backends, and SearchFilter runs against get_queryset() before get_object() resolves the pk. So a request for ?search=pump on template 42 filters templates by “pump”, 42 is not among them, and the endpoint returns 404 — for a template that plainly exists.

Nobody deduces that from a stack trace. Somebody loses an hour to it, renames the parameter, and then either writes that comment or leaves the next person to lose the same hour.

Refusing the public version

"""
Permission: `IsAuthenticated` (inherited). The endpoint is *not*
mirrored under `/public/` for V1: a public form referencing another
form's rows would leak data we haven't audited the access controls
of. If a use-case lands later, model it explicitly.
"""

The platform has a whole public surface — anonymous submissions, embedded forms, QR intake. Mirroring this endpoint there would have been three lines and would have made the feature complete.

It would also mean an anonymous visitor to a public form can enumerate the rows of whatever other form it references, through an access path nobody has looked at. If a use-case lands later, model it explicitly is the right disposal: not “never”, but “not by default, and not without someone deciding”.

Writing that sentence in the docstring is what turns a gap into a decision. The same gap with no comment is indistinguishable from an oversight, and the next developer adding public endpoints will helpfully fill it in.

What carries over

• Store the business key when the rows will be exported. A UUID is correct and useless in a spreadsheet; the constraint that makes it safe (UNIQUE on the target column) belongs to the target’s schema.
• Identifiers cannot be parameterised — allowlist and raise. name.replace("_", "").isalnum(), then ValueError. Never sanitise an identifier into a different valid one.
• Say which layer is the real gate. “The caller validates; this is the backstop” keeps the backstop from being deleted as redundant.
• Cast to one type on read, cast back on write from a single authority. Two components that both believe they know the schema will disagree.
• Distinguish “never existed” from “no longer exists” in the error a user reads. It selects a different next action.
• Name the seam when two stores hold the same data, and say which one is authoritative — otherwise the next reader will trust whichever they opened first.