audit-db-schema — independently scanned and version-tracked by SaferSkills.
SaferSkills independently audited audit-db-schema (Agent Skill) and scored it 45/100 (orange). The audit ran 55 deterministic rules across Security, Supply Chain, Maintenance, Transparency, and Community; it found 1 high-severity and 0 lower-severity findings. The full rule-by-rule trace and per-finding evidence are below. Free, methodology-open.
Findings & checks · 1 flagged
A base64 string of 128+ characters appears in a documentation file. Encoded prompt injection hides the hostile instruction in base64 — invisible to keyword filters — and relies on the agent's ability to decode it at runtime. There is no normal authoring reason to embed a multi-hundred-byte base64 blob in skill docs.
*.sig, SIGNATURES) outside the documentation.Every scanned point with the score it earned and what moved between them.
First recorded scan — no prior version to compare against.
The primary manifest — the file an agent reads to learn what this artifact does.
Systematic audit of database schema implementation against industry standards for consistency, robustness, and validation.
Before auditing, discover the project's database setup.
Read the dependency manifest and configuration files:
| Signal | Technology |
|---|---|
@supabase/supabase-js in package.json | Supabase (Postgres) |
prisma in devDependencies, prisma/schema.prisma | Prisma ORM |
drizzle-orm in dependencies, drizzle/ directory | Drizzle ORM |
sequelize in dependencies | Sequelize ORM |
sqlalchemy in requirements | SQLAlchemy (Python) |
supabase/migrations/*.sql directory | Supabase migrations |
prisma/migrations/ directory | Prisma migrations |
drizzle/migrations/ or drizzle/*.sql | Drizzle migrations |
If Supabase is detected, discover the project ID:
CallMcpTool(server: "plugin-supabase-supabase", toolName: "list_projects", arguments: {})Match the project by name or URL from .env, .env.local, or supabase/config.toml. Record the PROJECT_ID for all subsequent MCP calls.
Glob: **/supabase/migrations/*.sql → Supabase SQL migrations
Glob: **/prisma/schema.prisma → Prisma schema
Glob: **/drizzle/schema.ts → Drizzle schema
Glob: **/src/db/schema.ts → Drizzle alt location
Glob: **/knexfile.* → Knex migrations
Glob: **/alembic/versions/*.py → SQLAlchemy migrationsDATABASE ENVIRONMENT:
- Database: [Supabase Postgres / raw Postgres / MySQL / SQLite]
- ORM: [Prisma / Drizzle / Sequelize / none]
- Project ID: [Supabase project ID or N/A]
- Migration tool: [Supabase CLI / Prisma Migrate / Drizzle Kit / Knex]
- Schema files: [list paths]
- Migration count: [N]If using Prisma:
CallMcpTool(server: "context7", toolName: "resolve-library-id", arguments: {
"libraryName": "prisma",
"query": "schema best practices indexes relations"
})CallMcpTool(server: "context7", toolName: "query-docs", arguments: {
"libraryId": "<RESOLVED_ID>",
"query": "schema best practices naming conventions indexes onDelete"
})If using Drizzle, resolve drizzle-orm instead.
CallMcpTool(server: "user-firecrawl", toolName: "firecrawl_search", arguments: {
"query": "PostgreSQL schema design best practices [current year]",
"limit": 5,
"sources": [{ "type": "web" }]
})Additional searches based on detected stack:
| Stack | Search Query |
|---|---|
| Supabase | Supabase RLS policies best practices performance [current year] |
| Prisma | Prisma schema design relations indexes best practices [current year] |
| Drizzle | Drizzle ORM schema patterns migrations [current year] |
| General | PostgreSQL indexing strategy production optimization |
Scrape the most authoritative result:
CallMcpTool(server: "user-firecrawl", toolName: "firecrawl_scrape", arguments: {
"url": "<BEST_RESULT_URL>",
"formats": ["markdown"],
"onlyMainContent": true
})If Supabase is detected, also search their docs:
CallMcpTool(server: "plugin-supabase-supabase", toolName: "search_docs", arguments: {
"query": "RLS policy performance best practices"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "list_tables", arguments: {
"project_id": "<PROJECT_ID>",
"schemas": ["public"],
"verbose": true
})This returns column details, primary keys, and foreign key constraints for every table.
CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "SELECT table_name, column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "SELECT tc.table_name, tc.constraint_name, tc.constraint_type, kcu.column_name, ccu.table_name AS foreign_table FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name WHERE tc.table_schema = 'public'"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'public' ORDER BY tablename"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "get_advisors", arguments: {
"project_id": "<PROJECT_ID>",
"type": "security"
})CallMcpTool(server: "plugin-supabase-supabase", toolName: "get_advisors", arguments: {
"project_id": "<PROJECT_ID>",
"type": "performance"
})Include remediation URLs from advisor results in the final report as clickable links.
| Rule | Standard | Check |
|---|---|---|
| Tables | snake_case, plural (users, posts) | No camelCase, no singular |
| Columns | snake_case (created_at, user_id) | No camelCase |
| Primary keys | id | Not user_id on own table |
| Foreign keys | {referenced_table_singular}_id (user_id) | Consistent pattern |
| Indexes | idx_{table}_{column(s)} | Descriptive names |
| Constraints | {table}_{column}_{type} (users_email_unique) | Descriptive names |
| Enums | snake_case type, UPPER_CASE values | Consistent casing |
| Boolean columns | is_ or has_ prefix (is_active, has_access) | Clear intent |
Audit query:
-- Find naming violations
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
AND (table_name ~ '[A-Z]' OR table_name !~ 's$');
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name ~ '[A-Z]';| Rule | Standard |
|---|---|
| Primary keys | uuid with gen_random_uuid() or cuid |
| Timestamps | timestamptz (NOT timestamp) |
| Money | numeric(12,2) or bigint (cents) — NEVER float/real |
text with CHECK constraint or citext | |
| Status/enum | Postgres enum type or text with CHECK |
| JSON | jsonb (NOT json) |
| Short strings | text preferred over varchar(n) in Postgres |
| Booleans | boolean with NOT NULL DEFAULT |
| IP addresses | inet type |
| Arrays | Native text[], integer[] where appropriate |
Audit queries:
-- Timestamp without timezone
SELECT table_name, column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'timestamp without time zone';
-- Float/real money columns
SELECT table_name, column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public'
AND data_type IN ('real', 'double precision')
AND (column_name LIKE '%price%' OR column_name LIKE '%amount%'
OR column_name LIKE '%cost%' OR column_name LIKE '%balance%');
-- json instead of jsonb
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'json';Every table MUST have:
| Column | Type | Default | Notes |
|---|---|---|---|
id | uuid | gen_random_uuid() | Primary key |
created_at | timestamptz | now() | NOT NULL |
updated_at | timestamptz | now() | NOT NULL, auto-trigger |
Audit queries:
-- Tables missing created_at or updated_at
SELECT t.table_name,
EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'created_at') AS has_created_at,
EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'updated_at') AS has_updated_at
FROM information_schema.tables t
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE';
-- Check updated_at trigger exists
SELECT event_object_table, trigger_name FROM information_schema.triggers
WHERE trigger_schema = 'public' AND action_statement LIKE '%updated_at%';| Constraint | When Required |
|---|---|
NOT NULL | Every column unless explicitly optional |
UNIQUE | Emails, slugs, external IDs, usernames |
CHECK | Enums, ranges, formats, positive numbers |
DEFAULT | Booleans, timestamps, status fields |
FOREIGN KEY | Every relationship column |
ON DELETE | CASCADE for owned data, SET NULL for optional refs, RESTRICT for critical |
Audit queries:
-- Nullable FK columns (usually a mistake)
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name LIKE '%_id'
AND is_nullable = 'YES' AND column_name != 'id';
-- FK columns without foreign key constraints
SELECT c.table_name, c.column_name FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.column_name LIKE '%_id' AND c.column_name != 'id'
AND NOT EXISTS (
SELECT 1 FROM information_schema.key_column_usage kcu
JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND kcu.table_name = c.table_name AND kcu.column_name = c.column_name
);
-- Booleans without DEFAULT
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'boolean' AND column_default IS NULL;| Rule | Standard |
|---|---|
| Foreign keys | Index on EVERY FK column |
| Frequent queries | Index on WHERE/ORDER BY columns |
| Unique lookups | Unique index on email, slug, external_id |
| Composite | Order: equality first, then range, then sort |
| RLS columns | Index columns used in RLS policies |
created_at | DESC index for chronological queries |
| Partial indexes | WHERE clause for subset queries |
Audit query:
-- FK columns without indexes
SELECT c.table_name, c.column_name FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.column_name LIKE '%_id' AND c.column_name != 'id'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes i
WHERE i.schemaname = 'public' AND i.tablename = c.table_name
AND i.indexdef LIKE '%' || c.column_name || '%'
);
-- Tables with no indexes besides PK
SELECT t.table_name, COUNT(i.indexname) as idx_count
FROM information_schema.tables t
LEFT JOIN pg_indexes i ON i.tablename = t.table_name AND i.schemaname = 'public'
WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name HAVING COUNT(i.indexname) <= 1;| Rule | Standard |
|---|---|
| RLS enabled | EVERY public table has RLS ON |
| SELECT policy | Exists for every table |
| INSERT policy | WITH CHECK on user ownership |
| UPDATE policy | USING + WITH CHECK on ownership |
| DELETE policy | USING on ownership |
| Service role | Bypasses RLS (never expose to client) |
| Performance | (select auth.uid()) subquery pattern |
| Indexes | On columns used in policies |
Audit queries:
-- Tables WITHOUT RLS enabled
SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND rowsecurity = false;
-- Tables WITH RLS but NO policies
SELECT t.tablename FROM pg_tables t
WHERE t.schemaname = 'public' AND t.rowsecurity = true
AND NOT EXISTS (
SELECT 1 FROM pg_policies p WHERE p.tablename = t.tablename AND p.schemaname = 'public'
);
-- Policies using auth.uid() without subquery (performance issue)
SELECT tablename, policyname, qual FROM pg_policies
WHERE schemaname = 'public'
AND qual::text LIKE '%auth.uid()%'
AND qual::text NOT LIKE '%(select auth.uid())%';| Rule | Standard |
|---|---|
| 3NF minimum | No transitive dependencies |
| Junction tables | For many-to-many (user_roles, not JSON arrays) |
| No data duplication | Normalize repeated data into lookup tables |
| Cascade rules | Defined on every FK relationship |
| Self-referencing | Use with parent_id pattern when needed |
| Polymorphic | Avoid — use junction tables or STI instead |
| Rule | Standard |
|---|---|
| Sequential numbering | Timestamps or 0001_, 0002_ prefixes |
| Descriptive names | 0003_add_user_roles.sql not 0003_update.sql |
| Idempotent | IF NOT EXISTS, IF EXISTS guards |
| No data loss | Down migrations or rollback plan |
| Atomic | One logical change per migration |
| No breaking changes | Additive first, then backfill, then cleanup |
| Rule | Standard |
|---|---|
| No plaintext secrets | Passwords hashed, tokens encrypted |
| PII protection | Sensitive columns identified and protected |
| Audit trail | created_by, updated_by on sensitive tables |
| Grants | Minimal privileges per role |
| Extensions | Only necessary extensions enabled |
| Search path | Explicit schema references |
Audit query:
-- Columns that might contain sensitive data
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public'
AND (column_name LIKE '%password%' OR column_name LIKE '%secret%'
OR column_name LIKE '%token%' OR column_name LIKE '%ssn%'
OR column_name LIKE '%credit_card%');
-- Check granted permissions
SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges
WHERE table_schema = 'public' ORDER BY grantee, table_name;Run this full health check via Supabase MCP:
CallMcpTool(server: "plugin-supabase-supabase", toolName: "execute_sql", arguments: {
"project_id": "<PROJECT_ID>",
"query": "WITH table_info AS (SELECT t.table_name, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'id') AS has_id, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'created_at') AS has_created_at, EXISTS(SELECT 1 FROM information_schema.columns c WHERE c.table_name = t.table_name AND c.column_name = 'updated_at') AS has_updated_at, (SELECT rowsecurity FROM pg_tables pt WHERE pt.tablename = t.table_name AND pt.schemaname = 'public') AS rls_enabled, (SELECT COUNT(*) FROM pg_policies p WHERE p.tablename = t.table_name AND p.schemaname = 'public') AS policy_count, (SELECT COUNT(*) FROM pg_indexes i WHERE i.tablename = t.table_name AND i.schemaname = 'public') AS index_count FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE') SELECT table_name, CASE WHEN has_id THEN 'Y' ELSE 'N' END AS id, CASE WHEN has_created_at THEN 'Y' ELSE 'N' END AS created_at, CASE WHEN has_updated_at THEN 'Y' ELSE 'N' END AS updated_at, CASE WHEN rls_enabled THEN 'Y' ELSE 'N' END AS rls, policy_count AS policies, index_count AS indexes FROM table_info ORDER BY table_name"
})~30 seconds. Free. No account. Every finding cites a rule and a line of evidence.