Infrastructure Schema Demo — scope-engine Template

Date: 2026-05-23 Status: Design approved Location: scope-engine/templates/sql-schema/

Goal

Demonstrate the full gen ecosystem (gen-schema, gen-graph, scope-engine) via a complex infrastructure schema DSL — the SQL equivalent of nest-traits' CSS engine. 22 kinds modeling a multi-datacenter, multi-environment fleet. Two output modes:

DDL generation — migration-ordered CREATE TABLE statements from the schema definition
SQL query engine — parse and evaluate SQL-like queries (SELECT, FROM, JOIN, WHERE) against live infrastructure data, resolving joins via gen-graph FK traversal

The parallel to nest-traits: nest uses CSS selectors to match DOM nodes and deliver configuration. This demo uses SQL queries to match infrastructure resources and return result sets. The query engine is the centerpiece — DDL and ACL synthesis are supporting showcases.

Showcases: SQL string parser, join resolution via gen-graph, refinement contracts, row-polymorphic validators, scope-engine graph construction, deep FK chains, cross-model ACL synthesis, and migration-ordered DDL.

Architecture

Layer	Library	Role
Type definitions	gen-schema	22 kinds with parent topology, cross-cutting refs, refinement contracts
Graph construction	scope-engine	Kinds as nodes, refs as import edges, `buildNodes` for indexed graph
Graph queries	gen-graph	FK-based join resolution, migration ordering, cycle detection, impact analysis
SQL engine	template-local	SQL string parser, query planner, WHERE evaluation, JOIN via graph edges

Template-local code: SQL parser, query engine, DDL generation, ACL synthesis, refinements.

Data Model — 22 Kinds

Infrastructure Topology (parent edges)

datacenter
  └── network        (parent: datacenter)
       └── subnet    (parent: network)
            └── vlan  (parent: subnet)

environment ─── (standalone: dev, staging, prod)

server
  └── interface      (parent: server)

service
  └── port           (parent: service)

domain
  └── dns-record     (parent: domain)

loadbalancer
  └── backend        (parent: loadbalancer)

ldap-group ─── (standalone group declarations)

access-policy ─── (standalone policy declarations)

effective-access ─ (synthesized at eval time)
network-reachability ─ (synthesized at eval time)

Cross-Cutting References (import edges)

server ──refs──→ datacenter, environment, subnet
server.replaces ──refs──→ server (self-ref, nullable — rolling replacement)
interface ──refs──→ vlan
service ──refs──→ server, environment
service-dependency ──refs──→ service (dual: upstream + downstream)
dns-record ──refs──→ server (nullable), loadbalancer (nullable) — at least one required
loadbalancer ──refs──→ datacenter, environment
loadbalancer.failover ──refs──→ loadbalancer (self-ref, nullable)
backend ──refs──→ service
firewall-rule ──refs──→ subnet (dual: src + dst), server (dual: src + dst, nullable)
certificate ──refs──→ server (nullable), loadbalancer (nullable) — at least one required
schedule ──refs──→ service, server
ldap-role ──refs──→ ldap-group
user ──refs──→ ldap-role, server (setOf — multiple server assignments)
user.manager ──refs──→ user (self-ref, nullable — org hierarchy)
access-policy ──refs──→ ldap-role
effective-access ──refs──→ user, access-policy (synthesized)
network-reachability ──refs──→ server (dual: src + dst, synthesized)

Kind Definitions

Kind	Parent	Refs	Key Options	Refinements
`datacenter`	—	—	`region` (str)
`environment`	—	—	`tier` (str)	refined: dev/staging/prod
`network`	datacenter	—	`cidr` (str)	refined: CIDR format
`subnet`	network	—	`cidr` (str), `gateway` (str)	refined: CIDR, ipv4Address
`vlan`	subnet	—	`id` (int), `name` (str)	refined: 1-4094
`server`	—	datacenter, environment, subnet	`hostname` (str), `os` (str), `cores` (int), `ram_gb` (int), `tags` (list of str), `replaces` (nullable self-ref)	refined: nonEmpty hostname, positive cores/ram
`interface`	server	vlan	`mac` (str), `ip` (str), `primary` (bool)	refined: macAddress, ipv4Address
`service`	—	server, environment	`protocol` (str), `healthcheck` (str)	refined: tcp/udp/http/grpc
`service-dependency`	—	service (dual: upstream, downstream)	`required` (bool), `protocol` (str)	refined: serviceProtocol; validator: no-self-dependency
`port`	service	—	`number` (int), `protocol` (str), `expose` (bool)	refined: tcpPort (1-65535)
`domain`	—	environment	`tld` (bool), `wildcard` (bool)
`dns-record`	domain	server (nullable), loadbalancer (nullable)	`type` (str), `ttl` (int)	refined: A/AAAA/CNAME/MX/TXT, positive ttl; validator: at-least-one-target
`loadbalancer`	—	datacenter, environment	`algorithm` (str), `failover` (nullable self-ref)	refined: roundrobin/leastconn/iphash
`backend`	loadbalancer	service	`weight` (int), `maxconn` (int)	refined: positive weight, positive maxconn
`firewall-rule`	—	subnet (dual: src, dst), server (dual: src, dst, nullable)	`protocol` (str), `port` (int), `action` (str), `priority` (int)	refined: serviceProtocol, tcpPort, firewallAction, positive priority
`certificate`	—	server (nullable), loadbalancer (nullable)	`domains` (list of str), `issuer` (str), `expires-days` (int)	refined: certIssuer, positive expires-days; validator: cert-has-target
`schedule`	—	service, server	`cron` (str), `enabled` (bool)
`ldap-group`	—	—	`gid` (int), `description` (str)
`ldap-role`	—	ldap-group	`permissions` (list of str)
`user`	—	ldap-role, server (setOf — multiple assignments)	`uid` (int), `shell` (str), `ssh-key` (str), `manager` (nullable self-ref)
`access-policy`	—	ldap-role	`resource-kind` (str), `scope` (str), `actions` (list of str)	refined: resource-kind in server/service/loadbalancer; scope in direct/transitive
`effective-access`	—	user, access-policy	`resource` (str), `actions` (list of str), `via` (str)	Synthesized — engine materializes from policies + graph
`network-reachability`	—	server (dual: src, dst)	`path` (list of str), `allowed-ports` (list of int)	Synthesized — engine materializes from firewall rules + topology

Demo Fleet Data

# Datacenters
datacenters.us-east-1 = { region = "us-east"; };
datacenters.eu-west-1 = { region = "eu-west"; };

# Environments
environments.prod = { tier = "prod"; };
environments.staging = { tier = "staging"; };

# Networking: us-east-1
networks."us-east-1.primary" = { cidr = "10.0.0.0/16"; };
subnets."us-east-1.primary.web" = { cidr = "10.0.1.0/24"; gateway = "10.0.1.1"; };
subnets."us-east-1.primary.db" = { cidr = "10.0.2.0/24"; gateway = "10.0.2.1"; };
vlans."us-east-1.primary.web.100" = { id = 100; name = "web-vlan"; };
vlans."us-east-1.primary.db.200" = { id = 200; name = "db-vlan"; };

# Networking: eu-west-1
networks."eu-west-1.primary" = { cidr = "172.16.0.0/16"; };
subnets."eu-west-1.primary.app" = { cidr = "172.16.1.0/24"; gateway = "172.16.1.1"; };

# Servers
servers.web-1 = { hostname = "web-1"; os = "nixos"; cores = 4; ram_gb = 8;
                  datacenter = "us-east-1"; environment = "prod"; subnet = "us-east-1.primary.web"; };
servers.web-2 = { hostname = "web-2"; os = "nixos"; cores = 4; ram_gb = 8;
                  datacenter = "us-east-1"; environment = "prod"; subnet = "us-east-1.primary.web"; };
servers.db-1 = { hostname = "db-1"; os = "nixos"; cores = 8; ram_gb = 32;
                 datacenter = "us-east-1"; environment = "prod"; subnet = "us-east-1.primary.db"; };
servers.api-1 = { hostname = "api-1"; os = "nixos"; cores = 4; ram_gb = 16;
                  datacenter = "eu-west-1"; environment = "prod"; subnet = "eu-west-1.primary.app"; };

# Interfaces
interfaces."web-1.eth0" = { mac = "00:11:22:33:44:01"; ip = "10.0.1.10"; primary = true; vlan = "us-east-1.primary.web.100"; };
interfaces."db-1.eth0" = { mac = "00:11:22:33:44:02"; ip = "10.0.2.10"; primary = true; vlan = "us-east-1.primary.db.200"; };

# Services
services.nginx = { protocol = "http"; healthcheck = "/health"; server = "web-1"; environment = "prod"; };
services.postgres = { protocol = "tcp"; healthcheck = "/ready"; server = "db-1"; environment = "prod"; };
services.api = { protocol = "grpc"; healthcheck = "/grpc.health.v1.Health/Check"; server = "api-1"; environment = "prod"; };

# Ports
ports."nginx.http" = { number = 80; protocol = "tcp"; expose = true; };
ports."nginx.https" = { number = 443; protocol = "tcp"; expose = true; };
ports."postgres.pg" = { number = 5432; protocol = "tcp"; expose = false; };
ports."api.grpc" = { number = 50051; protocol = "tcp"; expose = true; };

# Domains & DNS
domains."example.com" = { tld = true; wildcard = false; environment = "prod"; };
domains."api.example.com" = { tld = false; wildcard = false; environment = "prod"; };
dns-records."example.com.web" = { type = "A"; ttl = 300; server = "web-1"; };
dns-records."api.example.com.api" = { type = "A"; ttl = 60; loadbalancer = "lb-prod-east"; };

# Load balancers
loadbalancers.lb-prod-east = { algorithm = "roundrobin"; datacenter = "us-east-1"; environment = "prod"; };
backends."lb-prod-east.web-1" = { weight = 50; maxconn = 1000; service = "nginx"; };
backends."lb-prod-east.web-2" = { weight = 50; maxconn = 1000; service = "nginx"; };

# LDAP
ldap-groups.engineering = { gid = 1000; description = "Engineering team"; };
ldap-groups.ops = { gid = 1001; description = "Operations team"; };
ldap-roles.admin = { permissions = [ "sudo" "deploy" "restart" ]; ldap-group = "ops"; };
ldap-roles.developer = { permissions = [ "deploy" "logs" ]; ldap-group = "engineering"; };
users.alice = { uid = 1000; shell = "/bin/zsh"; ssh-key = "ssh-ed25519 AAAA...";
                ldap-role = "admin"; servers = [ "web-1" "db-1" ]; };  # setOf ref
users.bob = { uid = 1001; shell = "/bin/bash"; ssh-key = "ssh-ed25519 BBBB...";
              ldap-role = "developer"; servers = [ "api-1" ]; manager = "alice"; };  # self-ref

# Server self-refs (rolling replacement chain)
# web-2 replaces web-1 (migration tracking)
servers.web-2 = { hostname = "web-2"; os = "nixos"; cores = 4; ram_gb = 8;
                  datacenter = "us-east-1"; environment = "prod"; subnet = "us-east-1.primary.web";
                  tags = [ "web" "frontend" ]; replaces = "web-1"; };

# Interface for web-2
interfaces."web-2.eth0" = { mac = "00:11:22:33:44:03"; ip = "10.0.1.11"; primary = true; vlan = "us-east-1.primary.web.100"; };

# LB failover chain
loadbalancers.lb-prod-east-standby = { algorithm = "roundrobin"; datacenter = "us-east-1"; environment = "prod";
                                       failover = "lb-prod-east"; };  # self-ref

# Service dependencies (inter-service dependency graph)
service-dependencies.api-needs-postgres = {
  upstream = "postgres"; downstream = "api";
  required = true; protocol = "tcp";
};
service-dependencies.nginx-proxies-api = {
  upstream = "api"; downstream = "nginx";
  required = false; protocol = "grpc";
};

# Firewall rules (dual subnet refs, self-ref possible)
firewall-rules.web-to-db = {
  src-subnet = "us-east-1.primary.web"; dst-subnet = "us-east-1.primary.db";
  protocol = "tcp"; port = 5432; action = "allow"; priority = 100;
};
firewall-rules.web-to-web-health = {
  src-subnet = "us-east-1.primary.web"; dst-subnet = "us-east-1.primary.web";  # self-ref
  protocol = "tcp"; port = 8080; action = "allow"; priority = 200;
};
firewall-rules.deny-db-outbound = {
  src-subnet = "us-east-1.primary.db"; dst-subnet = "eu-west-1.primary.app";
  protocol = "tcp"; port = 443; action = "deny"; priority = 50;
};

# Certificates (nullable dual refs — at-least-one validator)
certificates.wildcard-example = {
  domains = [ "*.example.com" "example.com" ];
  issuer = "letsencrypt"; expires-days = 90;
  loadbalancer = "lb-prod-east";
};
certificates.api-internal = {
  domains = [ "api.internal" ];
  issuer = "internal-ca"; expires-days = 365;
  server = "api-1";
};

# Schedules (dual refs: service + server)
schedules.db-backup = { cron = "0 2 * * *"; service = "postgres"; server = "db-1"; enabled = true; };
schedules.log-rotate = { cron = "0 0 * * 0"; service = "nginx"; server = "web-1"; enabled = true; };

# Access policies — bridge LDAP roles to infrastructure resources
access-policies.ops-server-sudo = {
  ldap-role = "admin";
  resource-kind = "server";
  scope = "direct";         # only servers the user is assigned to
  actions = [ "sudo" "restart" "ssh" ];
};
access-policies.ops-lb-manage = {
  ldap-role = "admin";
  resource-kind = "loadbalancer";
  scope = "transitive";     # LBs reachable from assigned server's services
  actions = [ "drain" "reload" ];
};
access-policies.eng-service-deploy = {
  ldap-role = "developer";
  resource-kind = "service";
  scope = "transitive";     # services on assigned server + reachable via deps
  actions = [ "deploy" "logs" "rollback" ];
};
access-policies.eng-server-logs = {
  ldap-role = "developer";
  resource-kind = "server";
  scope = "direct";
  actions = [ "logs" "ssh" ];
};

# Effective access — SYNTHESIZED by the engine, not declared by users.
# The engine walks: user → server → (services on server) → (LBs via backends)
# and intersects with access-policy permissions per resource-kind.
#
# Example materialized output:
#   effective-access."alice→web-1" = {
#     user = "alice"; resource = "server:web-1";
#     actions = [ "sudo" "restart" "ssh" ];
#     via = "access-policy:ops-server-sudo";
#   };
#   effective-access."alice→nginx" = {
#     user = "alice"; resource = "service:nginx";
#     via = "access-policy:ops-lb-manage";  # transitive: web-1 runs nginx
#   };
#   effective-access."bob→api" = {
#     user = "bob"; resource = "service:api";
#     actions = [ "deploy" "logs" "rollback" ];
#     via = "access-policy:eng-service-deploy";
#   };

Refinement Contracts

refinements = {
  cidr = {
    check = v: builtins.match "^[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+/[0-9]+$" v != null;
    message = "must be valid CIDR notation (e.g., 10.0.0.0/16)";
  };
  ipv4Address = {
    check = v: builtins.match "^[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+$" v != null;
    message = "must be a valid IPv4 address";
  };
  macAddress = {
    check = v: builtins.match "^[0-9a-fA-F]{2}(:[0-9a-fA-F]{2}){5}$" v != null;
    message = "must be a valid MAC address (e.g., 00:11:22:33:44:55)";
  };
  vlanId = {
    check = v: v >= 1 && v <= 4094;
    message = "VLAN ID must be 1-4094";
  };
  envTier = {
    check = v: builtins.elem v [ "dev" "staging" "prod" ];
    message = "must be dev, staging, or prod";
  };
  serviceProtocol = {
    check = v: builtins.elem v [ "tcp" "udp" "http" "grpc" ];
    message = "must be tcp, udp, http, or grpc";
  };
  dnsRecordType = {
    check = v: builtins.elem v [ "A" "AAAA" "CNAME" "MX" "TXT" ];
    message = "must be A, AAAA, CNAME, MX, or TXT";
  };
  lbAlgorithm = {
    check = v: builtins.elem v [ "roundrobin" "leastconn" "iphash" ];
    message = "must be roundrobin, leastconn, or iphash";
  };
  firewallAction = {
    check = v: builtins.elem v [ "allow" "deny" ];
    message = "must be allow or deny";
  };
  certIssuer = {
    check = v: builtins.elem v [ "letsencrypt" "internal-ca" "acme" ];
    message = "must be letsencrypt, internal-ca, or acme";
  };
};

Plus reusable refinements from gen-schema: tcpPort, nonEmpty, positive.

Row-Polymorphic Validators

mkFieldValidator {
  name = "server-ram-proportional";
  fields = [ "cores" "ram_gb" ];
  check = s: s.ram_gb >= s.cores * 2;
  message = "server RAM must be at least 2x cores";
}

mkFieldValidator {
  name = "primary-interface-has-ip";
  fields = [ "primary" "ip" ];
  check = i: !i.primary || i.ip != "";
  message = "primary interface must have an IP address";
}

mkFieldValidator {
  name = "dns-record-has-target";
  fields = [ "server" "loadbalancer" ];
  check = r: r.server != null || r.loadbalancer != null;
  message = "DNS record must reference a server or loadbalancer";
}

mkFieldValidator {
  name = "cert-has-target";
  fields = [ "server" "loadbalancer" ];
  check = c: c.server != null || c.loadbalancer != null;
  message = "certificate must be bound to a server or loadbalancer";
}

mkFieldValidator {
  name = "no-self-dependency";
  fields = [ "upstream" "downstream" ];
  check = d: d.upstream != d.downstream;
  message = "service cannot depend on itself";
}

ACL Synthesis

The cross-model bridge between LDAP identity and infrastructure resources. The engine synthesizes effective-access entries by walking the dependency graph:

For each user, resolve their ldap-role and assigned server
For each access-policy matching that role:
- scope = "direct": the user's assigned server (or services on it, or LBs fronting those services) based on resource-kind
- scope = "transitive": use gen-graph.reachableFrom to walk the dependency graph from the user's server, collecting all resources of the target resource-kind
Emit effective-access entries pairing user × resource × actions × policy source

synthesizeAccess = fleet: nodes: graphLib:
  let
    userEntries = lib.concatMap (userName:
      let
        user = fleet.user.${userName};
        role = fleet.ldap-role.${user.ldap-role};
        server = user.server;
        # All resources reachable from user's server via dependency graph
        reachable = graphLib.reachableFrom nodes "server:${server}";
        matchingPolicies = lib.filter (p:
          p.ldap-role == user.ldap-role
        ) (builtins.attrValues fleet.access-policy);
      in
      lib.concatMap (policy:
        let
          # Resolve target resources based on scope and resource-kind
          directTargets =
            if policy.resource-kind == "server" then
              # Direct: just the user's assigned servers
              map (s: "server:${s}") (if builtins.isList server then server else [ server ])
            else if policy.resource-kind == "service" then
              # Direct: services running on user's assigned servers
              lib.filter (id:
                let svcName = lib.removePrefix "service:" id; in
                lib.hasPrefix "service:" id && builtins.elem (fleet.service.${svcName}.server or "") (if builtins.isList server then server else [ server ])
              ) (map (n: "service:${n}") (builtins.attrNames (fleet.service or {})))
            else [];
          targets =
            if policy.scope == "direct" then directTargets
            else  # transitive: walk dependency graph from user's servers
              lib.filter (id: lib.hasPrefix "${policy.resource-kind}:" id) reachable;
        in
        map (target: {
          name = "${userName}→${target}";
          value = {
            inherit userName target;
            inherit (policy) actions;
            via = policy.name;
          };
        }) targets
      ) matchingPolicies
    ) (builtins.attrNames fleet.user);
  in
  builtins.listToAttrs userEntries;

This is the demo's first cross-model synthesis — LDAP identity × infrastructure topology × policy rules → materialized permissions, powered by gen-graph's transitive reachability queries.

Network Reachability Synthesis

Second cross-model synthesis: materializes which servers can reach which other servers based on firewall rules and network topology. Uses gen-graph's pathsBetween to find network paths and intersects with firewall rules to determine allowed ports.

synthesizeReachability = fleet: nodes: graphLib:
  let
    allServers = builtins.attrNames (fleet.server or {});
    serverPairs = lib.concatMap (src:
      map (dst: { inherit src dst; }) (builtins.filter (d: d != src) allServers)
    ) allServers;
  in
  lib.listToAttrs (lib.concatMap ({ src, dst }:
    let
      srcSubnet = fleet.server.${src}.subnet;
      dstSubnet = fleet.server.${dst}.subnet;
      # Find firewall rules allowing traffic between these subnets
      allowedRules = lib.filter (r:
        r.action == "allow"
        && r.src-subnet == srcSubnet
        && r.dst-subnet == dstSubnet
      ) (builtins.attrValues (fleet.firewall-rule or {}));
      allowedPorts = map (r: r.port) allowedRules;
      # Network path via gen-graph
      paths = graphLib.pathsBetween nodes "server:${src}" "server:${dst}";
    in
    lib.optional (allowedRules != []) {
      name = "${src}→${dst}";
      value = {
        src-server = src; dst-server = dst;
        path = if paths != [] then builtins.head paths else [ src dst ];
        inherit allowedPorts;
      };
    }
  ) serverPairs);

This exercises pathsBetween (the gen-graph primitive that nest-traits doesn't use) and creates a second materialized view joining firewall rules × network topology × server assignments.

Gen-Graph Queries

The dependency graph has deep chains and cross-cutting refs. Example queries on the demo fleet:

# Full dependency chain from api.example.com
graphLib.reachableFrom nodes "dns-record:api.example.com.api"
# → loadbalancer:lb-prod-east → backend:lb-prod-east.web-1 → service:nginx → server:web-1
#   → datacenter:us-east-1, environment:prod, subnet:us-east-1.primary.web

# Impact of losing db-1
graphLib.dependents nodes "server:db-1"
# → service:postgres, interface:db-1.eth0, user:alice (if assigned to db-1)

# Migration ordering: create independent entities first
graphLib.roots nodes
# → datacenter, environment, ldap-group (no incoming refs)

# Circular dependency detection
graphLib.cycles nodes
# → [] (clean fleet)

# All servers in us-east-1 (via select, not graph traversal)
graphLib.select nodes (n: n.type == "server" && n.decls.datacenter == "us-east-1")

# ACL queries — cross-model
# "What resources can alice access?" (synthesized from access-policies + graph)
effectiveAccess = synthesizeAccess fleet nodes graphLib;
# → { "alice→web-1" = { actions = ["sudo" "restart" "ssh"]; ... };
#     "alice→nginx" = { actions = ["deploy" "logs" "rollback"]; ... };
#     ... }

# "Who has sudo on web-1?"
sudoOnWeb1 = lib.filterAttrs (_: ea:
  ea.target == "server:web-1" && builtins.elem "sudo" ea.actions
) effectiveAccess;

SQL Query Engine

The centerpiece of the demo — a SQL string parser and query evaluator that mirrors nest-traits' CSS selector engine. Users write SQL queries as strings; the engine parses them, resolves table names to gen-schema kinds, evaluates JOINs via gen-graph FK traversal, and applies WHERE predicates against live instance data.

Supported SQL Subset

SQL Feature	Implementation
`SELECT col, col`	Project fields from matching instances
`SELECT *`	Return all fields
`FROM kind`	Resolve to gen-schema kind's instance registry
`FROM kind alias`	Alias for qualified column refs (`s.hostname`)
`JOIN kind ON fk = pk`	Resolve FK ref via gen-graph import edges
`LEFT JOIN`	Include non-matching rows with nulls
`WHERE col = 'val'`	Equality predicate on instance field
`WHERE col != 'val'`	Inequality
`WHERE col IN (...)`	Set membership
`WHERE 'val' IN col`	Reverse membership (value in list field)
`WHERE col IS NULL`	Null check
`WHERE col IS NOT NULL`	Non-null check
`AND` / `OR`	Boolean combinators
`ORDER BY col`	Sort result set
`LIMIT n`	Truncate result set

Parser

SQL string → AST (attrset). Mirrors nest-traits' css.nix parser architecture.

# parseSql "SELECT s.hostname FROM servers s WHERE s.datacenter = 'us-east-1'"
# → {
#     select = [ { table = "s"; column = "hostname"; } ];
#     from = { kind = "server"; alias = "s"; };
#     joins = [];
#     where = { op = "="; left = { table = "s"; column = "datacenter"; }; right = "us-east-1"; };
#     orderBy = null;
#     limit = null;
#   }

Query Evaluation

The evaluator takes a parsed AST and the fleet data, resolves kinds to instance registries, and produces result sets.

# Simple SELECT
query fleet ''
  SELECT hostname, os, cores
  FROM servers
  WHERE datacenter = 'us-east-1' AND environment = 'prod'
''
# → [
#   { hostname = "web-1"; os = "nixos"; cores = 4; }
#   { hostname = "web-2"; os = "nixos"; cores = 4; }
#   { hostname = "db-1"; os = "nixos"; cores = 8; }
# ]

# JOIN via FK — services running on servers in us-east-1
query fleet ''
  SELECT s.hostname, svc.name, p.number
  FROM servers s
  JOIN services svc ON svc.server = s.name
  JOIN ports p ON p.service = svc.name
  WHERE s.datacenter = 'us-east-1'
    AND p.expose = true
''
# → [
#   { hostname = "web-1"; name = "nginx"; number = 80; }
#   { hostname = "web-1"; name = "nginx"; number = 443; }
# ]

# Cross-model: who has sudo?
query fleet ''
  SELECT u.name, u.server, r.permissions
  FROM users u
  JOIN ldap_roles r ON u.ldap_role = r.name
  WHERE 'sudo' IN r.permissions
''
# → [ { name = "alice"; server = "web-1"; permissions = ["sudo" "deploy" "restart"]; } ]

# Infrastructure impact: what DNS records point at servers in subnet 10.0.1.0/24?
query fleet ''
  SELECT d.name, dns.type, s.hostname
  FROM dns_records dns
  JOIN domains d ON dns.domain = d.name
  JOIN servers s ON dns.server = s.name
  WHERE s.subnet = 'us-east-1.primary.web'
''

# Network inventory: VLANs with their full path
query fleet ''
  SELECT v.name, v.id, sub.cidr, net.cidr, dc.region
  FROM vlans v
  JOIN subnets sub ON v.subnet = sub.name
  JOIN networks net ON sub.network = net.name
  JOIN datacenters dc ON net.datacenter = dc.name
''

# ACL query via SQL
query fleet ''
  SELECT ea.user, ea.resource, ea.actions
  FROM effective_access ea
  JOIN access_policies ap ON ea.via = ap.name
  WHERE 'sudo' IN ea.actions
    AND ea.resource LIKE 'server:%'
''

JOIN Resolution

JOINs are resolved via the gen-schema ref graph, not by scanning all combinations:

Parse JOIN services svc ON svc.server = s.name — identifies FK: service.server refs server
Look up the FK in gen-schema's _meta.refEdges — confirms service → server via field server
For each row in the left table, resolve the FK value to the referenced instance (O(1) via instance registry lookup)
Produce the joined row

This is the SQL equivalent of nest's selector matching — instead of walking the DOM tree, the engine walks the FK graph.

Comparison with Nest CSS

Nest CSS	SQL Query Engine
`parseCssSel` string parser	`parseSql` string parser
`matchesOne` evaluates selector against node	`evalWhere` evaluates predicate against row
`:has(trait)` child selector	`JOIN ... ON fk = pk` FK traversal
`:within(trait)` ancestor selector	Multi-hop `JOIN` chains
`[attr=val]` attribute selector	`WHERE col = 'val'`
`*` star selector	`SELECT *`
`#id` ID selector	`WHERE name = 'id'`
`.class` class selector	`WHERE type = 'kind'`
Rule delivers `{ nixos = config; }`	Query delivers result set rows

DDL Generation

Generates SQL DDL for the full schema — 22 tables with foreign keys, in migration order.

-- Roots first (no incoming FKs)
CREATE TABLE datacenter (
  name text PRIMARY KEY NOT NULL,
  region text NOT NULL
);

CREATE TABLE environment (
  name text PRIMARY KEY NOT NULL,
  tier text NOT NULL CHECK (tier IN ('dev', 'staging', 'prod'))
);

CREATE TABLE ldap_group (
  name text PRIMARY KEY NOT NULL,
  gid int NOT NULL,
  description text
);

-- Then dependents...
CREATE TABLE network (
  name text PRIMARY KEY NOT NULL,
  cidr text NOT NULL,
  datacenter text NOT NULL REFERENCES datacenter(name)
);

-- ... down to leaves
CREATE TABLE user_ (
  name text PRIMARY KEY NOT NULL,
  uid int NOT NULL,
  shell text NOT NULL,
  ssh_key text,
  ldap_role text NOT NULL REFERENCES ldap_role(name),
  manager text REFERENCES user_(name)
);

-- Junction table for user ↔ server (setOf ref)
CREATE TABLE user_server (
  user_ text NOT NULL REFERENCES user_(name),
  server text NOT NULL REFERENCES server(name),
  PRIMARY KEY (user_, server)
);

-- Firewall rules (dual FK to same table)
CREATE TABLE firewall_rule (
  name text PRIMARY KEY NOT NULL,
  src_subnet text NOT NULL REFERENCES subnet(name),
  dst_subnet text NOT NULL REFERENCES subnet(name),
  src_server text REFERENCES server(name),
  dst_server text REFERENCES server(name),
  protocol text NOT NULL CHECK (protocol IN ('tcp', 'udp', 'http', 'grpc')),
  port int NOT NULL CHECK (port >= 1 AND port <= 65535),
  action text NOT NULL CHECK (action IN ('allow', 'deny')),
  priority int NOT NULL CHECK (priority > 0)
);

-- Self-referential FK
CREATE TABLE server (
  name text PRIMARY KEY NOT NULL,
  hostname text NOT NULL,
  os text NOT NULL,
  cores int NOT NULL CHECK (cores > 0),
  ram_gb int NOT NULL CHECK (ram_gb > 0),
  tags text[],
  datacenter text NOT NULL REFERENCES datacenter(name),
  environment text NOT NULL REFERENCES environment(name),
  subnet text NOT NULL REFERENCES subnet(name),
  replaces text REFERENCES server(name)
);

Indexes (derived from ref fields)

CREATE INDEX idx_server_datacenter ON server(datacenter);
CREATE INDEX idx_server_environment ON server(environment);
CREATE INDEX idx_server_subnet ON server(subnet);
CREATE INDEX idx_firewall_priority ON firewall_rule(src_subnet, priority);
CREATE INDEX idx_service_dep_upstream ON service_dependency(upstream);
CREATE INDEX idx_service_dep_downstream ON service_dependency(downstream);

Views (derived from cross-model synthesis)

CREATE VIEW user_permissions AS
  SELECT ea.user_ AS username, ea.resource, ea.actions, ea.via
  FROM effective_access ea;

CREATE VIEW server_network_map AS
  SELECT s.name AS server, i.ip, i.mac, v.name AS vlan, sub.cidr AS subnet
  FROM server s
  JOIN interface i ON i.server = s.name AND i.primary_ = true
  JOIN vlan v ON i.vlan = v.name
  JOIN subnet sub ON v.subnet = sub.name;

CREATE VIEW service_dependency_chain AS
  SELECT sd.downstream AS service, sd.upstream AS depends_on, sd.required
  FROM service_dependency sd;

File Structure

templates/sql-schema/
├── flake.nix              # inputs: scope-engine, gen-schema, gen-graph, gen, nixpkgs
├── lib/
│   ├── default.nix        # public API: evalSchema, query, generateDDL, migrationOrder, synthesizeAccess, synthesizeReachability
│   ├── schema.nix         # 22 gen-schema kinds, refinements, validators
│   ├── sql.nix            # SQL string parser (parseSql, tokenize)
│   ├── engine.nix         # query evaluator (evalQuery, evalWhere, resolveJoin)
│   ├── ddl.nix            # DDL string generation (tables, indexes, views)
│   ├── acl.nix            # ACL synthesis (access-policy × graph → effective-access)
│   ├── reachability.nix   # network reachability synthesis (firewall rules × topology → server pairs)
│   └── fleet.nix          # demo fleet data (the example infrastructure)
├── tests.nix              # ~65 tests across 10 suites
└── README.md

Test Suites

Suite	Tests	Covers
`schema`	~6	All 22 kinds defined, parent topology correct, ref resolution
`refinement`	~6	CIDR, VLAN ID, env tier, protocol, DNS type, tcpPort
`graph`	~8	FK edges, migration ordering, cycle detection, reachableFrom, dependents, impact
`ddl`	~6	Table DDL, FK constraints, migration order, CHECK constraints, reserved word escaping
`sql-parser`	~8	Tokenizer, SELECT/FROM/JOIN/WHERE parsing, aliases, multi-join, IN/IS NULL
`sql-engine`	~8	Simple select, filtered select, single join, multi-join chain, LEFT JOIN, ORDER BY, LIMIT
`acl`	~6	Policy resolution, direct vs transitive scope, effective-access synthesis, "who has sudo on X"
`reachability`	~4	Firewall rule intersection, pathsBetween exercise, allowed ports, cross-subnet
`fleet`	~5	Demo fleet validates, cross-datacenter refs resolve, self-refs, setOf refs
`integration`	~4	Full pipeline: define → validate → query → graph → order → DDL → ACL → reachability

Pipeline

User declares infrastructure (22 kinds, Nix attrsets)
  ↓
gen-schema: validate all refinements (CIDR, VLAN, ports, protocols, DNS types)
gen-schema: run row-polymorphic validators (server resources, primary interface)
  ↓
gen-schema.buildInstanceGraph: produce { parentGraph, importGraph, decls, types }
  ↓
scope-engine.buildNodes: index into flat node map
  ↓
gen-graph: detect cycles, compute migration order, run impact queries
  ↓
acl.nix: synthesize effective-access from access-policies + graph reachability
  ↓
Two output modes:
  → sql.nix + engine.nix: parse SQL queries, evaluate against fleet data, return result sets
  → ddl.nix: generate CREATE TABLE statements in dependency order with FK constraints

sini/2026-05-23-sql-schema-demo-design.md

Select an option

No results found