7. Database and Security Hints

Database generation is a compiler pass over the effective Blueprint. Hints describe expected posture; emitted SQL and installed policies provide enforcement.

The compiler and inspection APIs in this chapter are exported by package:vyuh_blueprint_server/vyuh_blueprint_server.dart.

Review the generated schema before installing it; apply seed data to the configured database.

To declare database roles and per-entity grants, see Database Access Profiles.

Declare schema and table posture

final opsModule = Module(
  name: 'ops',
  version: '1.0.0',
  entities: [areaEntity],
  db: const ModuleDb(
    extensions: ['pgcrypto'],
    auditMode: DbAuditMode.full,
    realtime: DbRealtimeMode.outboxOnly,
    rlsMode: DbRlsMode.enforced,
    outboxMode: DbOutboxMode.schema,
    retentionRef: 'gxp_10_years',
  ),
);

Add entity and field hints where the access shape is known:

final areaEntity = Entity(
  // ...
  db: const EntityDb(
    expectedRows: DbRowScale.medium,
    writeRate: DbWriteRate.low,
    readPattern: DbReadPattern.runtimeList,
    queryPatterns: [
      QueryPattern.filter,
      QueryPattern.search,
      QueryPattern.sort,
    ],
  ),
  facets: [
    IdentityFacet(
      fields: const [
        TextField(
          'code',
          title: 'Code',
          maxLength: 80,
          nullable: false,
          indexed: true,
          db: FieldDb(
            indexed: true,
            cardinality: DbCardinality.unique,
            queryPatterns: [QueryPattern.search, QueryPattern.sort],
          ),
          ui: FieldUI(
            filterable: true,
            sortable: true,
            searchable: true,
          ),
        ),
      ],
    ),
  ],
);

Compile and inspect before installing

final compilation = DbCompiler.compile(blueprint);
final database = DatabaseInspection.from(
  blueprint: blueprint,
  compilation: compilation,
);

print(compilation.toReport());
for (final file in [
  ...compilation.modelFiles.files,
  ...compilation.runtimeFiles.files,
]) {
  print('${file.path}\n${file.sql}');
}
print(database.json['summary']);

The model DDL comes as ordered files: 000_infrastructure.sql, 010_types.sql, one 1NN_<schema>.<table>.sql per entity in dependency order, 800_deferred_foreign_keys.sql, 900_schema_access.sql (schema-wide REVOKEs, first so later GRANTs stand), then one 9NN_<schema>.<table>_access.sql per entity (its _full view, RLS and policies). No file passes 1,000 lines; a longer section splits into _partN files. Install the files in list order.

DbCompiler combines:

• DdlEmitter: schemas, enums, identity/facet tables, history/audit tables, indexes, full views, outbox infrastructure;
• RuntimeDdlEmitter: common execution/runtime tables;
• DerivedArtifacts: explicit report of inferred tables, columns, constraints, reverse relationships, and infrastructure;
• DbPlan: inspectable RLS, partitioning, retention, index, trigger, view, and policy-reference decisions.
• DatabaseInspection: the serializable schemas, tables, columns, constraints, indexes, relationships, triggers, views, and complete SQL built from the emitted artifacts rather than a parallel UI guess.

Treat the report as a review artifact. Apply generated migrations only after validation and human review.

Native advanced fields remain fail-closed. A GeoPointField requires ModuleDb.extensions: ['postgis']; a VectorField requires vector. The DDL compiler emits native column types only with the required module posture. Code-level DDL verification is not a substitute for installing and exercising those extensions in the target PostgreSQL environment.

What is enforced today?

Declaration	Physical consequence
Field.nullable, type, defaults	Column DDL
Entity.uniqueKeys	UNIQUE constraint
foreign-key relationship	FK column and constraint
history: History.versions()	version column and immutable history table
history: History.revisions()	immutable save history plus governed entity revision table, metadata, and evidence/action links
facet lifecycle transitions	PostgreSQL trigger that rejects undeclared state edges
field index hints	index plan and emitted indexes where supported
module outbox posture	schema outbox infrastructure
runtime RLS	RLS enabled on emitted runtime tables
module/entity policy refs	inspectable DB plan

A hint is not a security boundary

DbRlsMode.enforced declares required posture. It does not, by itself, invent tenant predicates, grants, or application identities for every domain table. Production readiness requires installed ENABLE/FORCE ROW LEVEL SECURITY, explicit policies, least-privilege grants, service-role separation, migration verification, and negative tests against the deployed database.

Do not claim that UI hiding or an API predicate enforces tenancy. The database must reject an unauthorized row read/write even if the client and API are bypassed.

A production RLS review checklist

For each table:

1. Which immutable session claims establish actor, tenant, and site?
2. Which policy covers SELECT, INSERT, UPDATE, and DELETE?
3. Is WITH CHECK as strict as USING?
4. Can an owner or service role bypass RLS, and is that path isolated?
5. Are referenced rows protected across module boundaries?
6. Are audit, action, evidence, and outbox records append-only?
7. Do tests prove both allowed and denied cases?

Checkpoint

Compile the tutorial Blueprint and answer:

• Which columns and tables were declared directly?
• Which were derived?
• Which RLS requirements are emitted and verified by the current compiler?
• Which tests must run against an installed database before you call it secure?

Next: UI, queries, and forms.