7. Database, Security, and Seed 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.

Declare schema and table posture

final opsModule = Module(
  name: 'ops',
  schema: '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',
  ),
  seed: const ModuleSeed(
    datasetSize: SeedDatasetSize.validation,
    realism: SeedRealism.regulated,
    tenants: ['demo_company.hyderabad'],
    scenarios: ['active area', 'retired area'],
  ),
);

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,
    ],
  ),
  seed: const EntitySeed(
    targetRows: 12,
    scenarios: ['classified room', 'wash bay'],
  ),
  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,
          ),
          seed: FieldSeed(
            strategy: SeedValueStrategy.code,
            examples: ['WASH-01', 'COMP-201'],
          ),
        ),
      ],
    ),
  ],
);

Compile and inspect before installing

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

print(compilation.toReport());
print(compilation.ddl.script);
print(compilation.runtimeDdl.script);
print(database.json['summary']);

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.

What is enforced today?

Declaration	Physical consequence
Field.nullable, type, defaults	Column DDL
Entity.uniqueKeys	UNIQUE constraint
foreign-key relationship	FK column and constraint
versioning: EntityVersioning.versions	version column and immutable history table
versioning: const EntityVersioning.revisions()	immutable save history plus governed business 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?

Seeds are scenarios, not authority

ModuleSeed, EntitySeed, and FieldSeed guide deterministic/demo generation. They do not create production master data or bypass validation.

Use:

• smoke for the smallest bootable graph;
• demo for readable product exploration;
• validation for policy, lifecycle, and evidence cases;
• load for scale and query-plan tests.

Mark personal, patient, or confidential-looking fields sensitive: true so a generator keeps them synthetic.

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.

Reference: Runtime boundary.