fix: add cross-file Ref declarations for relspecgo merge

- Add explicit Ref: blocks at bottom of each DBML file for cross-file FK relationships - files.dbml: stored_files → thoughts, projects - skills.dbml: project_skills/project_guardrails → projects - meta.dbml: chat_histories → projects - Update Makefile to use the relspec merge workflow where applicable - Regenerate 020_generated_schema.sql with proper cross-file constraints Addresses review comment on PR #20
feat: add DBML schema files and relspecgo migration generation
2026-04-04 15:13:50 +02:00 · 2026-04-04 14:53:33 +02:00 · 2026-04-04 12:25:25 +00:00 · 2026-04-04 14:16:02 +02:00 · 2026-04-03 11:38:59 +00:00
20 changed files with 4823 additions and 64 deletions
--- a/30
+++ b/30
@@ -7,13 +7,17 @@ PATCH_INCREMENT ?= 1
 VERSION_TAG ?= $(shell git describe --tags --exact-match 2>/dev/null || echo dev)
 COMMIT_SHA ?= $(shell git rev-parse --short HEAD 2>/dev/null || echo unknown)
 BUILD_DATE ?= $(shell date -u +%Y-%m-%dT%H:%M:%SZ)
+RELSPEC ?= $(shell command -v relspec 2>/dev/null || echo $(HOME)/go/bin/relspec)
+SCHEMA_FILES := $(sort $(wildcard schema/*.dbml))
+MERGE_TARGET_TMP := $(CURDIR)/.cache/schema.merge-target.dbml
+GENERATED_SCHEMA_MIGRATION := migrations/020_generated_schema.sql
 LDFLAGS := -s -w \
 	-X $(BUILDINFO_PKG).Version=$(VERSION_TAG) \
 	-X $(BUILDINFO_PKG).TagName=$(VERSION_TAG) \
 	-X $(BUILDINFO_PKG).Commit=$(COMMIT_SHA) \
 	-X $(BUILDINFO_PKG).BuildDate=$(BUILD_DATE)

-.PHONY: all build clean migrate release-version test
+.PHONY: all build clean migrate release-version test generate-migrations check-schema-drift

 all: build

@@ -50,3 +54,27 @@ migrate:

 clean:
 	rm -rf $(BIN_DIR)
+
+generate-migrations:
+	@test -n "$(SCHEMA_FILES)" || (echo "No DBML schema files found in schema/" >&2; exit 1)
+	@command -v $(RELSPEC) >/dev/null 2>&1 || (echo "relspec not found; install git.warky.dev/wdevs/relspecgo/cmd/relspec@latest" >&2; exit 1)
+	@mkdir -p $(dir $(MERGE_TARGET_TMP))
+	@: > $(MERGE_TARGET_TMP)
+	@schema_list=$$(printf '%s\n' $(SCHEMA_FILES) | paste -sd, -); \
+	$(RELSPEC) merge --target dbml --target-path $(MERGE_TARGET_TMP) --source dbml --from-list "$$schema_list" --output pgsql --output-path $(GENERATED_SCHEMA_MIGRATION)
+
+check-schema-drift:
+	@test -f $(GENERATED_SCHEMA_MIGRATION) || (echo "$(GENERATED_SCHEMA_MIGRATION) is missing; run make generate-migrations" >&2; exit 1)
+	@command -v $(RELSPEC) >/dev/null 2>&1 || (echo "relspec not found; install git.warky.dev/wdevs/relspecgo/cmd/relspec@latest" >&2; exit 1)
+	@mkdir -p $(dir $(MERGE_TARGET_TMP))
+	@tmpfile=$$(mktemp); \
+	: > $(MERGE_TARGET_TMP); \
+	schema_list=$$(printf '%s\n' $(SCHEMA_FILES) | paste -sd, -); \
+	$(RELSPEC) merge --target dbml --target-path $(MERGE_TARGET_TMP) --source dbml --from-list "$$schema_list" --output pgsql --output-path $$tmpfile; \
+	if ! cmp -s $$tmpfile $(GENERATED_SCHEMA_MIGRATION); then \
+		echo "Schema drift detected between schema/*.dbml and $(GENERATED_SCHEMA_MIGRATION)" >&2; \
+		diff -u $(GENERATED_SCHEMA_MIGRATION) $$tmpfile || true; \
+		rm -f $$tmpfile; \
+		exit 1; \
+	fi; \
+	rm -f $$tmpfile
--- a/internal/app/app.go
+++ b/internal/app/app.go
@@ -158,7 +158,9 @@ func Run(ctx context.Context, configPath string) error {

 func routes(logger *slog.Logger, cfg *config.Config, info buildinfo.Info, db *store.DB, provider ai.Provider, keyring *auth.Keyring, oauthRegistry *auth.OAuthRegistry, tokenStore *auth.TokenStore, authCodes *auth.AuthCodeStore, dynClients *auth.DynamicClientStore, activeProjects *session.ActiveProjects) (http.Handler, error) {
 	mux := http.NewServeMux()
-	authMiddleware := auth.Middleware(cfg.Auth, keyring, oauthRegistry, tokenStore, logger)
+	accessTracker := auth.NewAccessTracker()
+	oauthEnabled := oauthRegistry != nil && tokenStore != nil
+	authMiddleware := auth.Middleware(cfg.Auth, keyring, oauthRegistry, tokenStore, accessTracker, logger)
 	filesTool := tools.NewFilesTool(db, activeProjects)
 	metadataRetryer := tools.NewMetadataRetryer(context.Background(), db, provider, cfg.Capture, cfg.AI.Metadata.Timeout, activeProjects, logger)

@@ -198,7 +200,7 @@ func routes(logger *slog.Logger, cfg *config.Config, info buildinfo.Info, db *st
 	mux.Handle(cfg.MCP.Path, authMiddleware(mcpHandler))
 	mux.Handle("/files", authMiddleware(fileHandler(filesTool)))
 	mux.Handle("/files/{id}", authMiddleware(fileHandler(filesTool)))
-	if oauthRegistry != nil && tokenStore != nil {
+	if oauthEnabled {
 		mux.HandleFunc("/.well-known/oauth-authorization-server", oauthMetadataHandler())
 		mux.HandleFunc("/oauth-authorization-server", oauthMetadataHandler())
 		mux.HandleFunc("/oauth/register", oauthRegisterHandler(dynClients, logger))
@@ -227,59 +229,7 @@ func routes(logger *slog.Logger, cfg *config.Config, info buildinfo.Info, db *st
 		_, _ = w.Write([]byte("ready"))
 	})

-	mux.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
-		if r.URL.Path != "/" {
-			http.NotFound(w, r)
-			return
-		}
-
-		if r.Method != http.MethodGet && r.Method != http.MethodHead {
-			w.Header().Set("Allow", "GET, HEAD")
-			http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
-			return
-		}
-
-		const homePage = `<!doctype html>
-<html lang="en">
-<head>
-  <meta charset="utf-8">
-  <meta name="viewport" content="width=device-width, initial-scale=1">
-  <title>AMCS</title>
-  <style>
-    body { margin: 0; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif; background: #f5f7fb; color: #172033; }
-    main { max-width: 860px; margin: 48px auto; background: #fff; border-radius: 12px; box-shadow: 0 10px 28px rgba(23, 32, 51, 0.12); overflow: hidden; }
-    .content { padding: 28px; }
-    h1 { margin: 0 0 12px 0; font-size: 2rem; }
-    p { margin: 0; line-height: 1.5; color: #334155; }
-    .actions { margin-top: 18px; }
-    .link { display: inline-block; padding: 10px 14px; border-radius: 8px; background: #172033; color: #fff; text-decoration: none; font-weight: 600; }
-    .link:hover { background: #0f172a; }
-    img { display: block; width: 100%; height: auto; }
-  </style>
-</head>
-<body>
-  <main>
-    <img src="/images/project.jpg" alt="Avelon Memory Crystal project image">
-    <div class="content">
-      <h1>Avelon Memory Crystal Server (AMCS)</h1>
-      <p>AMCS is a memory server that captures, links, and retrieves structured project thoughts for AI assistants using semantic search, summaries, and MCP tools.</p>
-      <div class="actions">
-        <a class="link" href="/llm">LLM Instructions</a>
-        <a class="link" href="/oauth-authorization-server">OAuth Authorization Server</a>
-        <a class="link" href="/healthz">Health Check</a>
-      </div>
-    </div>
-  </main>
-</body>
-</html>`
-		w.Header().Set("Content-Type", "text/html; charset=utf-8")
-		w.WriteHeader(http.StatusOK)
-		if r.Method == http.MethodHead {
-			return
-		}
-
-		_, _ = w.Write([]byte(homePage))
-	})
+	mux.HandleFunc("/", homeHandler(info, accessTracker, oauthEnabled))

 	return observability.Chain(
 		mux,
--- a/internal/app/status.go
+++ b/internal/app/status.go
@@ -0,0 +1,171 @@
+package app
+
+import (
+	"fmt"
+	"html"
+	"net/http"
+	"strings"
+	"time"
+
+	"git.warky.dev/wdevs/amcs/internal/auth"
+	"git.warky.dev/wdevs/amcs/internal/buildinfo"
+)
+
+const connectedWindow = 10 * time.Minute
+
+type statusPageData struct {
+	Version        string
+	BuildDate      string
+	Commit         string
+	ConnectedCount int
+	TotalKnown     int
+	Entries        []auth.AccessSnapshot
+	OAuthEnabled   bool
+}
+
+func renderHomePage(info buildinfo.Info, tracker *auth.AccessTracker, oauthEnabled bool, now time.Time) string {
+	entries := tracker.Snapshot()
+	data := statusPageData{
+		Version:        fallback(info.Version, "dev"),
+		BuildDate:      fallback(info.BuildDate, "unknown"),
+		Commit:         fallback(info.Commit, "unknown"),
+		ConnectedCount: tracker.ConnectedCount(now, connectedWindow),
+		TotalKnown:     len(entries),
+		Entries:        entries,
+		OAuthEnabled:   oauthEnabled,
+	}
+
+	var b strings.Builder
+	b.WriteString(`<!doctype html>
+<html lang="en">
+<head>
+  <meta charset="utf-8">
+  <meta name="viewport" content="width=device-width, initial-scale=1">
+  <title>AMCS</title>
+  <style>
+    body { margin: 0; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif; background: #f5f7fb; color: #172033; }
+    main { max-width: 980px; margin: 48px auto; background: #fff; border-radius: 12px; box-shadow: 0 10px 28px rgba(23, 32, 51, 0.12); overflow: hidden; }
+    .content { padding: 28px; }
+    h1, h2 { margin: 0 0 12px 0; }
+    p { margin: 0; line-height: 1.5; color: #334155; }
+    .actions { margin-top: 18px; display: flex; flex-wrap: wrap; gap: 10px; }
+    .link { display: inline-block; padding: 10px 14px; border-radius: 8px; background: #172033; color: #fff; text-decoration: none; font-weight: 600; }
+    .link:hover { background: #0f172a; }
+    .stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(160px, 1fr)); gap: 12px; margin-top: 24px; }
+    .card { background: #eef2ff; border-radius: 10px; padding: 16px; }
+    .label { display: block; font-size: 0.85rem; text-transform: uppercase; letter-spacing: 0.04em; color: #475569; }
+    .value { display: block; margin-top: 6px; font-size: 1.4rem; font-weight: 700; color: #0f172a; }
+    .meta { margin-top: 28px; color: #475569; font-size: 0.95rem; }
+    table { width: 100%; border-collapse: collapse; margin-top: 16px; }
+    th, td { text-align: left; padding: 10px 8px; border-bottom: 1px solid #e2e8f0; vertical-align: top; }
+    th { font-size: 0.85rem; text-transform: uppercase; letter-spacing: 0.04em; color: #475569; }
+    .empty { margin-top: 16px; color: #64748b; }
+    code { font-family: ui-monospace, SFMono-Regular, Menlo, monospace; }
+    img { display: block; width: 100%; height: auto; }
+  </style>
+</head>
+<body>
+  <main>
+    <img src="/images/project.jpg" alt="Avelon Memory Crystal project image">
+    <div class="content">
+      <h1>Avelon Memory Crystal Server (AMCS)</h1>
+      <p>AMCS is a memory server that captures, links, and retrieves structured project thoughts for AI assistants using semantic search, summaries, and MCP tools.</p>
+      <div class="actions">
+        <a class="link" href="/llm">LLM Instructions</a>
+        <a class="link" href="/healthz">Health Check</a>
+        <a class="link" href="/readyz">Readiness Check</a>`)
+	if data.OAuthEnabled {
+		b.WriteString(`
+        <a class="link" href="/oauth-authorization-server">OAuth Authorization Server</a>`)
+	}
+	b.WriteString(`
+      </div>
+
+      <div class="stats">
+        <div class="card">
+          <span class="label">Connected users</span>
+          <span class="value">` + fmt.Sprintf("%d", data.ConnectedCount) + `</span>
+        </div>
+        <div class="card">
+          <span class="label">Known principals</span>
+          <span class="value">` + fmt.Sprintf("%d", data.TotalKnown) + `</span>
+        </div>
+        <div class="card">
+          <span class="label">Version</span>
+          <span class="value">` + html.EscapeString(data.Version) + `</span>
+        </div>
+      </div>
+
+      <div class="meta">
+        <strong>Build date:</strong> ` + html.EscapeString(data.BuildDate) + ` &nbsp;•&nbsp;
+        <strong>Commit:</strong> <code>` + html.EscapeString(data.Commit) + `</code> &nbsp;•&nbsp;
+        <strong>Connected window:</strong> last 10 minutes
+      </div>
+
+      <h2 style="margin-top: 28px;">Recent access</h2>`)
+	if len(data.Entries) == 0 {
+		b.WriteString(`
+      <p class="empty">No authenticated access recorded yet.</p>`)
+	} else {
+		b.WriteString(`
+      <table>
+        <thead>
+          <tr>
+            <th>Principal</th>
+            <th>Last accessed</th>
+            <th>Last path</th>
+            <th>Requests</th>
+          </tr>
+        </thead>
+        <tbody>`)
+		for _, entry := range data.Entries {
+			b.WriteString(`
+          <tr>
+            <td><code>` + html.EscapeString(entry.KeyID) + `</code></td>
+            <td>` + html.EscapeString(entry.LastAccessedAt.UTC().Format(time.RFC3339)) + `</td>
+            <td>` + html.EscapeString(entry.LastPath) + `</td>
+            <td>` + fmt.Sprintf("%d", entry.RequestCount) + `</td>
+          </tr>`)
+		}
+		b.WriteString(`
+        </tbody>
+      </table>`)
+	}
+	b.WriteString(`
+    </div>
+  </main>
+</body>
+</html>`)
+
+	return b.String()
+}
+
+func fallback(value, defaultValue string) string {
+	if strings.TrimSpace(value) == "" {
+		return defaultValue
+	}
+	return value
+}
+
+func homeHandler(info buildinfo.Info, tracker *auth.AccessTracker, oauthEnabled bool) http.HandlerFunc {
+	return func(w http.ResponseWriter, r *http.Request) {
+		if r.URL.Path != "/" {
+			http.NotFound(w, r)
+			return
+		}
+
+		if r.Method != http.MethodGet && r.Method != http.MethodHead {
+			w.Header().Set("Allow", "GET, HEAD")
+			http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
+			return
+		}
+
+		w.Header().Set("Content-Type", "text/html; charset=utf-8")
+		w.WriteHeader(http.StatusOK)
+		if r.Method == http.MethodHead {
+			return
+		}
+
+		_, _ = w.Write([]byte(renderHomePage(info, tracker, oauthEnabled, time.Now())))
+	}
+}
--- a/internal/app/status_test.go
+++ b/internal/app/status_test.go
@@ -0,0 +1,84 @@
+package app
+
+import (
+	"io"
+	"log/slog"
+	"net/http"
+	"net/http/httptest"
+	"strings"
+	"testing"
+	"time"
+
+	"git.warky.dev/wdevs/amcs/internal/auth"
+	"git.warky.dev/wdevs/amcs/internal/buildinfo"
+	"git.warky.dev/wdevs/amcs/internal/config"
+)
+
+func TestRenderHomePageHidesOAuthLinkWhenDisabled(t *testing.T) {
+	tracker := auth.NewAccessTracker()
+	page := renderHomePage(buildinfo.Info{Version: "v1.2.3", BuildDate: "2026-04-04", Commit: "abc123"}, tracker, false, time.Date(2026, 4, 4, 12, 0, 0, 0, time.UTC))
+
+	if strings.Contains(page, "/oauth-authorization-server") {
+		t.Fatal("page unexpectedly contains OAuth link")
+	}
+	if !strings.Contains(page, "Connected users") {
+		t.Fatal("page missing Connected users stat")
+	}
+}
+
+func TestRenderHomePageShowsTrackedAccess(t *testing.T) {
+	tracker := auth.NewAccessTracker()
+	now := time.Date(2026, 4, 4, 12, 0, 0, 0, time.UTC)
+	tracker.Record("client-a", "/files", "127.0.0.1:1234", "tester", now)
+
+	page := renderHomePage(buildinfo.Info{Version: "v1.2.3"}, tracker, true, now)
+
+	for _, needle := range []string{"client-a", "/files", "1</span>", "/oauth-authorization-server"} {
+		if !strings.Contains(page, needle) {
+			t.Fatalf("page missing %q", needle)
+		}
+	}
+}
+
+func TestHomeHandlerAllowsHead(t *testing.T) {
+	handler := homeHandler(buildinfo.Info{Version: "v1"}, auth.NewAccessTracker(), false)
+	req := httptest.NewRequest(http.MethodHead, "/", nil)
+	rec := httptest.NewRecorder()
+
+	handler.ServeHTTP(rec, req)
+
+	if rec.Code != http.StatusOK {
+		t.Fatalf("status = %d, want %d", rec.Code, http.StatusOK)
+	}
+	if body := rec.Body.String(); body != "" {
+		t.Fatalf("body = %q, want empty for HEAD", body)
+	}
+}
+
+func TestMiddlewareRecordsAuthenticatedAccess(t *testing.T) {
+	keyring, err := auth.NewKeyring([]config.APIKey{{ID: "client-a", Value: "secret"}})
+	if err != nil {
+		t.Fatalf("NewKeyring() error = %v", err)
+	}
+	tracker := auth.NewAccessTracker()
+	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
+	handler := auth.Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, tracker, logger)(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+		w.WriteHeader(http.StatusNoContent)
+	}))
+
+	req := httptest.NewRequest(http.MethodGet, "/files", nil)
+	req.Header.Set("x-brain-key", "secret")
+	rec := httptest.NewRecorder()
+	handler.ServeHTTP(rec, req)
+
+	if rec.Code != http.StatusNoContent {
+		t.Fatalf("status = %d, want %d", rec.Code, http.StatusNoContent)
+	}
+	snap := tracker.Snapshot()
+	if len(snap) != 1 {
+		t.Fatalf("len(snapshot) = %d, want 1", len(snap))
+	}
+	if snap[0].KeyID != "client-a" || snap[0].LastPath != "/files" {
+		t.Fatalf("snapshot[0] = %+v, want keyID client-a and path /files", snap[0])
+	}
+}
--- a/internal/auth/access_tracker.go
+++ b/internal/auth/access_tracker.go
@@ -0,0 +1,81 @@
+package auth
+
+import (
+	"sort"
+	"sync"
+	"time"
+)
+
+type AccessSnapshot struct {
+	KeyID          string
+	LastPath       string
+	RemoteAddr     string
+	UserAgent      string
+	RequestCount   int
+	LastAccessedAt time.Time
+}
+
+type AccessTracker struct {
+	mu      sync.RWMutex
+	entries map[string]AccessSnapshot
+}
+
+func NewAccessTracker() *AccessTracker {
+	return &AccessTracker{entries: make(map[string]AccessSnapshot)}
+}
+
+func (t *AccessTracker) Record(keyID, path, remoteAddr, userAgent string, now time.Time) {
+	if t == nil || keyID == "" {
+		return
+	}
+
+	t.mu.Lock()
+	defer t.mu.Unlock()
+
+	entry := t.entries[keyID]
+	entry.KeyID = keyID
+	entry.LastPath = path
+	entry.RemoteAddr = remoteAddr
+	entry.UserAgent = userAgent
+	entry.LastAccessedAt = now.UTC()
+	entry.RequestCount++
+	t.entries[keyID] = entry
+}
+
+func (t *AccessTracker) Snapshot() []AccessSnapshot {
+	if t == nil {
+		return nil
+	}
+
+	t.mu.RLock()
+	defer t.mu.RUnlock()
+
+	items := make([]AccessSnapshot, 0, len(t.entries))
+	for _, entry := range t.entries {
+		items = append(items, entry)
+	}
+
+	sort.Slice(items, func(i, j int) bool {
+		return items[i].LastAccessedAt.After(items[j].LastAccessedAt)
+	})
+
+	return items
+}
+
+func (t *AccessTracker) ConnectedCount(now time.Time, window time.Duration) int {
+	if t == nil {
+		return 0
+	}
+
+	cutoff := now.UTC().Add(-window)
+	t.mu.RLock()
+	defer t.mu.RUnlock()
+
+	count := 0
+	for _, entry := range t.entries {
+		if !entry.LastAccessedAt.Before(cutoff) {
+			count++
+		}
+	}
+	return count
+}
--- a/internal/auth/access_tracker_test.go
+++ b/internal/auth/access_tracker_test.go
@@ -0,0 +1,45 @@
+package auth
+
+import (
+	"testing"
+	"time"
+)
+
+func TestAccessTrackerRecordAndSnapshot(t *testing.T) {
+	tracker := NewAccessTracker()
+	older := time.Date(2026, 4, 4, 10, 0, 0, 0, time.UTC)
+	newer := older.Add(2 * time.Minute)
+
+	tracker.Record("client-a", "/files", "10.0.0.1:1234", "agent-a", older)
+	tracker.Record("client-b", "/mcp", "10.0.0.2:1234", "agent-b", newer)
+	tracker.Record("client-a", "/files/1", "10.0.0.1:1234", "agent-a2", newer.Add(30*time.Second))
+
+	snap := tracker.Snapshot()
+	if len(snap) != 2 {
+		t.Fatalf("len(snapshot) = %d, want 2", len(snap))
+	}
+	if snap[0].KeyID != "client-a" {
+		t.Fatalf("snapshot[0].KeyID = %q, want client-a", snap[0].KeyID)
+	}
+	if snap[0].RequestCount != 2 {
+		t.Fatalf("snapshot[0].RequestCount = %d, want 2", snap[0].RequestCount)
+	}
+	if snap[0].LastPath != "/files/1" {
+		t.Fatalf("snapshot[0].LastPath = %q, want /files/1", snap[0].LastPath)
+	}
+	if snap[0].UserAgent != "agent-a2" {
+		t.Fatalf("snapshot[0].UserAgent = %q, want agent-a2", snap[0].UserAgent)
+	}
+}
+
+func TestAccessTrackerConnectedCount(t *testing.T) {
+	tracker := NewAccessTracker()
+	now := time.Date(2026, 4, 4, 12, 0, 0, 0, time.UTC)
+
+	tracker.Record("recent", "/mcp", "", "", now.Add(-2*time.Minute))
+	tracker.Record("stale", "/mcp", "", "", now.Add(-11*time.Minute))
+
+	if got := tracker.ConnectedCount(now, 10*time.Minute); got != 1 {
+		t.Fatalf("ConnectedCount() = %d, want 1", got)
+	}
+}
--- a/internal/auth/keyring_test.go
+++ b/internal/auth/keyring_test.go
@@ -39,7 +39,7 @@ func TestMiddlewareAllowsHeaderAuthAndSetsContext(t *testing.T) {
 		t.Fatalf("NewKeyring() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		keyID, ok := KeyIDFromContext(r.Context())
 		if !ok || keyID != "client-a" {
 			t.Fatalf("KeyIDFromContext() = (%q, %v), want (client-a, true)", keyID, ok)
@@ -63,7 +63,7 @@ func TestMiddlewareAllowsBearerAuthAndSetsContext(t *testing.T) {
 		t.Fatalf("NewKeyring() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		keyID, ok := KeyIDFromContext(r.Context())
 		if !ok || keyID != "client-a" {
 			t.Fatalf("KeyIDFromContext() = (%q, %v), want (client-a, true)", keyID, ok)
@@ -90,7 +90,7 @@ func TestMiddlewarePrefersExplicitHeaderOverBearerAuth(t *testing.T) {
 		t.Fatalf("NewKeyring() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		keyID, ok := KeyIDFromContext(r.Context())
 		if !ok || keyID != "client-a" {
 			t.Fatalf("KeyIDFromContext() = (%q, %v), want (client-a, true)", keyID, ok)
@@ -119,7 +119,7 @@ func TestMiddlewareAllowsQueryParamWhenEnabled(t *testing.T) {
 		HeaderName:      "x-brain-key",
 		QueryParam:      "key",
 		AllowQueryParam: true,
-	}, keyring, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	}, keyring, nil, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		w.WriteHeader(http.StatusNoContent)
 	}))

@@ -138,7 +138,7 @@ func TestMiddlewareRejectsMissingOrInvalidKey(t *testing.T) {
 		t.Fatalf("NewKeyring() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{HeaderName: "x-brain-key"}, keyring, nil, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		t.Fatal("next handler should not be called")
 	}))

--- a/internal/auth/middleware.go
+++ b/internal/auth/middleware.go
@@ -6,6 +6,7 @@ import (
 	"log/slog"
 	"net/http"
 	"strings"
+	"time"

 	"git.warky.dev/wdevs/amcs/internal/config"
 )
@@ -14,11 +15,16 @@ type contextKey string

 const keyIDContextKey contextKey = "auth.key_id"

-func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthRegistry, tokenStore *TokenStore, log *slog.Logger) func(http.Handler) http.Handler {
+func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthRegistry, tokenStore *TokenStore, tracker *AccessTracker, log *slog.Logger) func(http.Handler) http.Handler {
 	headerName := cfg.HeaderName
 	if headerName == "" {
 		headerName = "x-brain-key"
 	}
+	recordAccess := func(r *http.Request, keyID string) {
+		if tracker != nil {
+			tracker.Record(keyID, r.URL.Path, r.RemoteAddr, r.UserAgent(), time.Now())
+		}
+	}
 	return func(next http.Handler) http.Handler {
 		return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 			// 1. Custom header → keyring only.
@@ -30,6 +36,7 @@ func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthReg
 						http.Error(w, "invalid API key", http.StatusUnauthorized)
 						return
 					}
+					recordAccess(r, keyID)
 					next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), keyIDContextKey, keyID)))
 					return
 				}
@@ -39,12 +46,14 @@ func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthReg
 			if bearer := extractBearer(r); bearer != "" {
 				if tokenStore != nil {
 					if keyID, ok := tokenStore.Lookup(bearer); ok {
+						recordAccess(r, keyID)
 						next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), keyIDContextKey, keyID)))
 						return
 					}
 				}
 				if keyring != nil {
 					if keyID, ok := keyring.Lookup(bearer); ok {
+						recordAccess(r, keyID)
 						next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), keyIDContextKey, keyID)))
 						return
 					}
@@ -66,6 +75,7 @@ func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthReg
 					http.Error(w, "invalid OAuth client credentials", http.StatusUnauthorized)
 					return
 				}
+				recordAccess(r, keyID)
 				next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), keyIDContextKey, keyID)))
 				return
 			}
@@ -79,6 +89,7 @@ func Middleware(cfg config.AuthConfig, keyring *Keyring, oauthRegistry *OAuthReg
 						http.Error(w, "invalid API key", http.StatusUnauthorized)
 						return
 					}
+					recordAccess(r, keyID)
 					next.ServeHTTP(w, r.WithContext(context.WithValue(r.Context(), keyIDContextKey, keyID)))
 					return
 				}
--- a/internal/auth/oauth_registry_test.go
+++ b/internal/auth/oauth_registry_test.go
@@ -42,7 +42,7 @@ func TestMiddlewareAllowsOAuthBasicAuthAndSetsContext(t *testing.T) {
 		t.Fatalf("NewOAuthRegistry() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{}, nil, oauthRegistry, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{}, nil, oauthRegistry, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		keyID, ok := KeyIDFromContext(r.Context())
 		if !ok || keyID != "oauth-client" {
 			t.Fatalf("KeyIDFromContext() = (%q, %v), want (oauth-client, true)", keyID, ok)
@@ -70,7 +70,7 @@ func TestMiddlewareRejectsOAuthMissingOrInvalidCredentials(t *testing.T) {
 		t.Fatalf("NewOAuthRegistry() error = %v", err)
 	}

-	handler := Middleware(config.AuthConfig{}, nil, oauthRegistry, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+	handler := Middleware(config.AuthConfig{}, nil, oauthRegistry, nil, nil, testLogger())(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
 		t.Fatal("next handler should not be called")
 	}))

--- a/migrations/020_generated_schema.sql
+++ b/migrations/020_generated_schema.sql
--- a/schema/README.md
+++ b/schema/README.md
@@ -0,0 +1,35 @@
+# Schema workflow
+
+The `schema/*.dbml` files are the database schema source of truth.
+
+## Generate SQL migrations
+
+Run:
+
+```bash
+make generate-migrations
+```
+
+This uses `relspec` to convert the DBML files into PostgreSQL SQL and writes the generated schema migration to:
+
+- `migrations/020_generated_schema.sql`
+
+## Check schema drift
+
+Run:
+
+```bash
+make check-schema-drift
+```
+
+This regenerates the SQL from `schema/*.dbml` and compares it with `migrations/020_generated_schema.sql`.
+If the generated output differs, the command fails so CI can catch schema drift.
+
+## Workflow
+
+1. Update the DBML files in `schema/`
+2. Run `make generate-migrations`
+3. Review the generated SQL
+4. Commit both the DBML changes and the generated migration
+
+Existing handwritten migrations stay in place. Going forward, update the DBML first and regenerate the SQL from there.
--- a/schema/calendar.dbml
+++ b/schema/calendar.dbml
@@ -0,0 +1,44 @@
+Table family_members {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  relationship text
+  birth_date date
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+}
+
+Table activities {
+  id uuid [pk, default: `gen_random_uuid()`]
+  family_member_id uuid [ref: > family_members.id]
+  title text [not null]
+  activity_type text
+  day_of_week text
+  start_time time
+  end_time time
+  start_date date
+  end_date date
+  location text
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    day_of_week
+    family_member_id
+    (start_date, end_date)
+  }
+}
+
+Table important_dates {
+  id uuid [pk, default: `gen_random_uuid()`]
+  family_member_id uuid [ref: > family_members.id]
+  title text [not null]
+  date_value date [not null]
+  recurring_yearly boolean [not null, default: false]
+  reminder_days_before int [not null, default: 7]
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    date_value
+  }
+}
--- a/schema/core.dbml
+++ b/schema/core.dbml
@@ -0,0 +1,48 @@
+Table thoughts {
+  id bigserial [pk]
+  guid uuid [unique, not null, default: `gen_random_uuid()`]
+  content text [not null]
+  metadata jsonb [default: `'{}'::jsonb`]
+  created_at timestamptz [default: `now()`]
+  updated_at timestamptz [default: `now()`]
+  project_id uuid [ref: > projects.guid]
+  archived_at timestamptz
+}
+
+Table projects {
+  id bigserial [pk]
+  guid uuid [unique, not null, default: `gen_random_uuid()`]
+  name text [unique, not null]
+  description text
+  created_at timestamptz [default: `now()`]
+  last_active_at timestamptz [default: `now()`]
+}
+
+Table thought_links {
+  from_id bigint [not null, ref: > thoughts.id]
+  to_id bigint [not null, ref: > thoughts.id]
+  relation text [not null]
+  created_at timestamptz [default: `now()`]
+
+  indexes {
+    (from_id, to_id, relation) [pk]
+    from_id
+    to_id
+  }
+}
+
+Table embeddings {
+  id bigserial [pk]
+  guid uuid [unique, not null, default: `gen_random_uuid()`]
+  thought_id uuid [not null, ref: > thoughts.guid]
+  model text [not null]
+  dim int [not null]
+  embedding vector [not null]
+  created_at timestamptz [default: `now()`]
+  updated_at timestamptz [default: `now()`]
+
+  indexes {
+    (thought_id, model) [unique]
+    thought_id
+  }
+}
--- a/schema/crm.dbml
+++ b/schema/crm.dbml
@@ -0,0 +1,53 @@
+Table professional_contacts {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  company text
+  title text
+  email text
+  phone text
+  linkedin_url text
+  how_we_met text
+  tags "text[]" [not null, default: `'{}'`]
+  notes text
+  last_contacted timestamptz
+  follow_up_date date
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    last_contacted
+    follow_up_date
+  }
+}
+
+Table contact_interactions {
+  id uuid [pk, default: `gen_random_uuid()`]
+  contact_id uuid [not null, ref: > professional_contacts.id]
+  interaction_type text [not null]
+  occurred_at timestamptz [not null, default: `now()`]
+  summary text [not null]
+  follow_up_needed boolean [not null, default: false]
+  follow_up_notes text
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    (contact_id, occurred_at)
+  }
+}
+
+Table opportunities {
+  id uuid [pk, default: `gen_random_uuid()`]
+  contact_id uuid [ref: > professional_contacts.id]
+  title text [not null]
+  description text
+  stage text [not null, default: 'identified']
+  value "decimal(12,2)"
+  expected_close_date date
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    stage
+  }
+}
--- a/schema/files.dbml
+++ b/schema/files.dbml
@@ -0,0 +1,25 @@
+Table stored_files {
+  id bigserial [pk]
+  guid uuid [unique, not null, default: `gen_random_uuid()`]
+  thought_id uuid [ref: > thoughts.guid]
+  project_id uuid [ref: > projects.guid]
+  name text [not null]
+  media_type text [not null]
+  kind text [not null, default: 'file']
+  encoding text [not null, default: 'base64']
+  size_bytes bigint [not null]
+  sha256 text [not null]
+  content bytea [not null]
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    thought_id
+    project_id
+    sha256
+  }
+}
+
+// Cross-file refs (for relspecgo merge)
+Ref: stored_files.thought_id > thoughts.guid [delete: set null]
+Ref: stored_files.project_id > projects.guid [delete: set null]
--- a/schema/household.dbml
+++ b/schema/household.dbml
@@ -0,0 +1,31 @@
+Table household_items {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  category text
+  location text
+  details jsonb [not null, default: `'{}'`]
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    category
+  }
+}
+
+Table household_vendors {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  service_type text
+  phone text
+  email text
+  website text
+  notes text
+  rating int
+  last_used date
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    service_type
+  }
+}
--- a/schema/maintenance.dbml
+++ b/schema/maintenance.dbml
@@ -0,0 +1,30 @@
+Table maintenance_tasks {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  category text
+  frequency_days int
+  last_completed timestamptz
+  next_due timestamptz
+  priority text [not null, default: 'medium']
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    next_due
+  }
+}
+
+Table maintenance_logs {
+  id uuid [pk, default: `gen_random_uuid()`]
+  task_id uuid [not null, ref: > maintenance_tasks.id]
+  completed_at timestamptz [not null, default: `now()`]
+  performed_by text
+  cost "decimal(10,2)"
+  notes text
+  next_action text
+
+  indexes {
+    (task_id, completed_at)
+  }
+}
--- a/schema/meals.dbml
+++ b/schema/meals.dbml
@@ -0,0 +1,49 @@
+Table recipes {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [not null]
+  cuisine text
+  prep_time_minutes int
+  cook_time_minutes int
+  servings int
+  ingredients jsonb [not null, default: `'[]'`]
+  instructions jsonb [not null, default: `'[]'`]
+  tags "text[]" [not null, default: `'{}'`]
+  rating int
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    cuisine
+    tags
+  }
+}
+
+Table meal_plans {
+  id uuid [pk, default: `gen_random_uuid()`]
+  week_start date [not null]
+  day_of_week text [not null]
+  meal_type text [not null]
+  recipe_id uuid [ref: > recipes.id]
+  custom_meal text
+  servings int
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    week_start
+  }
+}
+
+Table shopping_lists {
+  id uuid [pk, default: `gen_random_uuid()`]
+  week_start date [unique, not null]
+  items jsonb [not null, default: `'[]'`]
+  notes text
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    week_start
+  }
+}
--- a/schema/meta.dbml
+++ b/schema/meta.dbml
@@ -0,0 +1,32 @@
+Table chat_histories {
+  id uuid [pk, default: `gen_random_uuid()`]
+  session_id text [not null]
+  title text
+  channel text
+  agent_id text
+  project_id uuid [ref: > projects.guid]
+  messages jsonb [not null, default: `'[]'`]
+  summary text
+  metadata jsonb [not null, default: `'{}'`]
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    session_id
+    project_id
+    channel
+    agent_id
+    created_at
+  }
+}
+
+Table tool_annotations {
+  id bigserial [pk]
+  tool_name text [unique, not null]
+  notes text [not null, default: '']
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+}
+
+// Cross-file refs (for relspecgo merge)
+Ref: chat_histories.project_id > projects.guid [delete: set null]
--- a/schema/skills.dbml
+++ b/schema/skills.dbml
@@ -0,0 +1,46 @@
+Table agent_skills {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [unique, not null]
+  description text [not null, default: '']
+  content text [not null]
+  tags "text[]" [not null, default: `'{}'`]
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+}
+
+Table agent_guardrails {
+  id uuid [pk, default: `gen_random_uuid()`]
+  name text [unique, not null]
+  description text [not null, default: '']
+  content text [not null]
+  severity text [not null, default: 'medium']
+  tags "text[]" [not null, default: `'{}'`]
+  created_at timestamptz [not null, default: `now()`]
+  updated_at timestamptz [not null, default: `now()`]
+}
+
+Table project_skills {
+  project_id uuid [not null, ref: > projects.guid]
+  skill_id uuid [not null, ref: > agent_skills.id]
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    (project_id, skill_id) [pk]
+    project_id
+  }
+}
+
+Table project_guardrails {
+  project_id uuid [not null, ref: > projects.guid]
+  guardrail_id uuid [not null, ref: > agent_guardrails.id]
+  created_at timestamptz [not null, default: `now()`]
+
+  indexes {
+    (project_id, guardrail_id) [pk]
+    project_id
+  }
+}
+
+// Cross-file refs (for relspecgo merge)
+Ref: project_skills.project_id > projects.guid [delete: cascade]
+Ref: project_guardrails.project_id > projects.guid [delete: cascade]
Author	SHA1	Message	Date
SGC	6c6b49b45c	fix: add cross-file Ref declarations for relspecgo merge - Add explicit Ref: blocks at bottom of each DBML file for cross-file FK relationships - files.dbml: stored_files → thoughts, projects - skills.dbml: project_skills/project_guardrails → projects - meta.dbml: chat_histories → projects - Update Makefile to use the relspec merge workflow where applicable - Regenerate 020_generated_schema.sql with proper cross-file constraints Addresses review comment on PR #20	2026-04-04 15:13:50 +02:00
SGC	59c43188e5	feat: add DBML schema files and relspecgo migration generation - Add schema/*.dbml covering all existing tables (001-019) - Wire relspecgo via make generate-migrations target - Add make check-schema-drift for CI drift detection - Add schema/README.md documenting the DBML-first workflow Closes #19	2026-04-04 14:53:33 +02:00
Warky	f0e242293f	Merge pull request 'feat(app): add lightweight status access tracking' (#16 ) from feat/status-page-access-tracking into main Reviewed-on: #16	2026-04-04 12:25:25 +00:00
Jack O'Neill	50870dd369	feat(app): add lightweight status access tracking	2026-04-04 14:16:02 +02:00
Warky	b93f1d14f0	Merge pull request 'docs: audit plan/todo docs against current repo state' (#15 ) from docs/plan-todo-audit into main Reviewed-on: #15	2026-04-03 11:38:59 +00:00