4 Commits
Author SHA1 Message Date
admin e130b039d4 Merge pull request 'Chore/remove supabase legacy' (#2) from chore/remove-supabase-legacy into main
Reviewed-on: #2
2026-11-06 22:08:46 +08:00
adminandClaude Sonnet 5.5 1a2f9fbd47 docs(claude): drop removed Supabase leftovers, note ssa role
Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
2026-11-06 22:08:21 +08:00
adminandClaude Sonnet 5.5 2bda9d06a7 chore: remove legacy Supabase code and dependencies
The app no longer imports Supabase (migrated to local PostgreSQL 2026-09-30).
Remove lib/supabase/*, the supabase/ migrations folder, the @supabase/ssr and
@supabase/supabase-js dependencies, and the storage test for the removed client.
db/schema.sql and db/rls.sql remain the schema source of truth.

Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
2026-11-06 22:07:21 +08:00
admin f32c6e8c60 Merge pull request 'Feat/ssa role' (#1) from feat/ssa-role into main
Reviewed-on: #1
2026-11-06 21:58:37 +08:00
39 changed files with 3 additions and 1641 deletions
+3 -4
View File
@@ -19,7 +19,8 @@ Replaces WhatsApp/Excel safety reporting. Client: Ms. Agnes, Mr. Terence, Mr. Ya
## Current Phase ## Current Phase
**All phases (0–6) complete as of 2026-07-12.** Supabase to local PostgreSQL migration completed and live 2026-09-30. **All phases (0–6) complete as of 2026-07-12.** Supabase to local PostgreSQL migration completed and live 2026-09-30.
**UI refresh in progress:** follow `design-system/design.md` §8 build order (tokens → ui primitives → TableCard → shells → migrate Incidents list → remaining screens). **UI refresh in progress:** follow `design-system/design.md` §8 build order (tokens → ui primitives → TableCard → shells → migrate Incidents list → remaining screens).
Legacy leftovers to remove when convenient: `lib/supabase/*`, the `@supabase/*` dependencies and the `supabase/` folder (the app no longer imports them; `db/schema.sql` + `db/rls.sql` are the schema source of truth). Legacy Supabase code (`lib/supabase/*`, `@supabase/*` deps, `supabase/` folder) was removed 2026-11-06; `db/schema.sql` + `db/rls.sql` are the schema source of truth.
**Roles (since 2026-11-06):** `ssa` (Site Safety Assistant, replaces reporter + supervisor + capa_owner), `hse`, `management`, `admin`. SSA pages live under `app/(protected)/ssa/`.
See `[[phases]]` memory for full phase breakdown and deliverables. See `[[phases]]` memory for full phase breakdown and deliverables.
## Deployment (Production) ## Deployment (Production)
@@ -62,10 +63,8 @@ IMS/
│ ├── auth/ # bcryptjs passwords, jose sessions, roles │ ├── auth/ # bcryptjs passwords, jose sessions, roles
│ ├── storage/ # evidence files on disk + signed URLs │ ├── storage/ # evidence files on disk + signed URLs
│ ├── claude/ # AI client (DeepSeek) + Google embeddings │ ├── claude/ # AI client (DeepSeek) + Google embeddings
│ ├── notifications/ # Brevo email + WhatsApp senders, in-app │ └── notifications/ # Brevo email + WhatsApp senders, in-app
│ └── supabase/ # LEGACY, unused, to be removed
├── db/ # schema.sql, rls.sql, migration/ops scripts ├── db/ # schema.sql, rls.sql, migration/ops scripts
├── supabase/ # LEGACY migrations, to be removed
└── public/ └── public/
``` ```
-15
View File
@@ -1,15 +0,0 @@
import 'server-only'
import { createClient as createSupabaseClient, type SupabaseClient } from '@supabase/supabase-js'
// Service-role client — bypasses RLS. Server-side only, and only for operations
// the anon client cannot perform (auth admin user invites). Never import in client code.
export function createAdminClient(): SupabaseClient {
const url = process.env.NEXT_PUBLIC_SUPABASE_URL
const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY
if (!url || !serviceRoleKey) {
throw new Error('SUPABASE_SERVICE_ROLE_KEY not configured')
}
return createSupabaseClient(url, serviceRoleKey, {
auth: { autoRefreshToken: false, persistSession: false },
})
}
-9
View File
@@ -1,9 +0,0 @@
// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'
export function createClient() {
return createBrowserClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
)
}
-27
View File
@@ -1,27 +0,0 @@
// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'
export async function createClient() {
const cookieStore = await cookies()
return createServerClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
{
cookies: {
getAll() {
return cookieStore.getAll()
},
setAll(cookiesToSet) {
try {
cookiesToSet.forEach(({ name, value, options }) =>
cookieStore.set(name, value, options),
)
} catch {
// Server Component — cookie setting handled by middleware
}
},
},
},
)
}
-71
View File
@@ -1,71 +0,0 @@
import type { SupabaseClient } from '@supabase/supabase-js'
import { fileTypeFromBuffer } from 'file-type'
export type EvidenceStage = 'report' | 'response' | 'investigation' | 'capa' | 'verification'
const ALLOWED_MIME_TYPES = new Set([
'image/jpeg', 'image/png', 'image/gif', 'image/webp', 'image/heic', 'image/avif',
'video/mp4', 'video/quicktime', 'video/webm', 'video/x-msvideo',
'application/pdf',
'application/msword',
'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
])
export async function uploadEvidenceFile(
supabase: SupabaseClient,
file: File,
incidentId: string,
stage: EvidenceStage,
userId: string,
): Promise<{ path: string; publicUrl: string; hash: string }> {
// Server-side size limit before processing
const isVideo = file.type.startsWith('video/')
const maxBytes = isVideo ? 200 * 1024 * 1024 : 10 * 1024 * 1024
if (file.size > maxBytes) {
throw new Error(`File too large: max ${isVideo ? '200MB' : '10MB'} for this file type`)
}
// Magic-byte validation — do not trust client-supplied MIME type
const buffer = await file.arrayBuffer()
const detected = await fileTypeFromBuffer(buffer)
const detectedMime = detected?.mime ?? null
if (!detectedMime || !ALLOWED_MIME_TYPES.has(detectedMime)) {
throw new Error(`File type not allowed: ${detectedMime ?? 'unknown'}`)
}
const ext = detected?.ext ?? file.name.split('.').pop() ?? 'bin'
const filename = `${Date.now()}-${Math.random().toString(36).slice(2)}.${ext}`
const path = `${userId}/${incidentId}/${stage}/${filename}`
const hash = await computeHashFromBuffer(buffer)
const { data: uploadData, error } = await supabase.storage
.from('evidence')
.upload(path, buffer, { contentType: detectedMime, upsert: false })
if (error) throw new Error(error.message)
// Fix 1: Use createSignedUrl (private bucket) — 10-year URL for DB storage
const { data: signedData, error: signedError } = await supabase.storage
.from('evidence')
.createSignedUrl(uploadData.path, 315360000) // ~10 years
if (signedError || !signedData) throw new Error(signedError?.message ?? 'Failed to sign URL')
return { path: uploadData.path, publicUrl: signedData.signedUrl, hash }
}
// Fix 1: getEvidenceUrl is now async, uses 1-hour signed URL
export async function getEvidenceUrl(supabase: SupabaseClient, path: string): Promise<string> {
const { data, error } = await supabase.storage.from('evidence').createSignedUrl(path, 3600)
if (error || !data) throw new Error(error?.message ?? 'Failed to sign URL')
return data.signedUrl
}
async function computeHashFromBuffer(buffer: ArrayBuffer): Promise<string> {
const hashBuffer = await crypto.subtle.digest('SHA-256', buffer)
return Array.from(new Uint8Array(hashBuffer))
.map(b => b.toString(16).padStart(2, '0'))
.join('')
}
-120
View File
@@ -9,8 +9,6 @@
"version": "1.2.2", "version": "1.2.2",
"dependencies": { "dependencies": {
"@anthropic-ai/sdk": "^0.111.0", "@anthropic-ai/sdk": "^0.111.0",
"@supabase/ssr": "^0.12.0",
"@supabase/supabase-js": "^2.110.2",
"bcryptjs": "^3.0.3", "bcryptjs": "^3.0.3",
"drizzle-orm": "^0.45.2", "drizzle-orm": "^0.45.2",
"file-type": "^22.0.1", "file-type": "^22.0.1",
@@ -2639,102 +2637,6 @@
"dev": true, "dev": true,
"license": "MIT" "license": "MIT"
}, },
"node_modules/@supabase/auth-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/auth-js/-/auth-js-2.110.2.tgz",
"integrity": "sha512-Qj7a6EDP+AMMQFWqGv+qFa8r6re//dk+qQI5bA0KK+PZmnI3JPu97TDeNt6SMiQ2FkklP79hP2yDFYSnA989OA==",
"license": "MIT",
"dependencies": {
"tslib": "2.8.1"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@supabase/functions-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/functions-js/-/functions-js-2.110.2.tgz",
"integrity": "sha512-ZjjqrXpxM9/rE+eAtZxiK45EWy9EBoJQ322Q5Y75LccYQNh212neHTgXP/o4MIzmH0LNXT8UzvTZtQOfOzyoeQ==",
"license": "MIT",
"dependencies": {
"tslib": "2.8.1"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@supabase/phoenix": {
"version": "0.4.4",
"resolved": "https://registry.npmjs.org/@supabase/phoenix/-/phoenix-0.4.4.tgz",
"integrity": "sha512-Gt0pqoXuIqX/8dvG0OKp/wMCobXNH3klNbUPBNyOfN0YA1IswrM3HyWFMOPk1Jy+BRaIyDPcFx4jLBwHNmlyfQ==",
"license": "MIT"
},
"node_modules/@supabase/postgrest-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/postgrest-js/-/postgrest-js-2.110.2.tgz",
"integrity": "sha512-++LBmcIMwCtgO4tISQUmo9+2xkRwHQqS8ZKMCnhXLe9P8k8YQRXuMoh/RiSzQSoev8gqet0W7yOboW0cUxnt0Q==",
"license": "MIT",
"dependencies": {
"tslib": "2.8.1"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@supabase/realtime-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/realtime-js/-/realtime-js-2.110.2.tgz",
"integrity": "sha512-z3jTOTPgyn6E3r6dVOOQ10He4yAMB2czjFw7xVdX3s16MHElna5rY1gVaePs0NIo6xvtMYbtmOXlFaFt/ePLpg==",
"license": "MIT",
"dependencies": {
"@supabase/phoenix": "0.4.4",
"tslib": "2.8.1"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@supabase/ssr": {
"version": "0.12.0",
"resolved": "https://registry.npmjs.org/@supabase/ssr/-/ssr-0.12.0.tgz",
"integrity": "sha512-d9XV5XzJvzzZbeAIM7fWTCUYxQJZ2Ru6ny3dJHmHGp/LIrJ+o9FpD7N9Rf/UhhWEvHXSoDe8SI32Z2ouOdMjBg==",
"license": "MIT",
"dependencies": {
"cookie": "^1.0.2"
},
"peerDependencies": {
"@supabase/supabase-js": "^2.108.0"
}
},
"node_modules/@supabase/storage-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/storage-js/-/storage-js-2.110.2.tgz",
"integrity": "sha512-EhsRSwSnmQefKJsAxoRUZ0hvHr92ECM8DDGAKR5z0HdoJx4heI60PjHUTruVNZxKX6XeobLGDyLud020Bw1iwg==",
"license": "MIT",
"dependencies": {
"iceberg-js": "^0.8.1",
"tslib": "2.8.1"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@supabase/supabase-js": {
"version": "2.110.2",
"resolved": "https://registry.npmjs.org/@supabase/supabase-js/-/supabase-js-2.110.2.tgz",
"integrity": "sha512-r9q9w4ZQ6mOjh36aqUNFSisBF611vzpO8JphBESr2Q1SWvmGFQeI7Jq7Y+PaNMZ6Zszz+S2yTlJStCpnaMSnQg==",
"license": "MIT",
"dependencies": {
"@supabase/auth-js": "2.110.2",
"@supabase/functions-js": "2.110.2",
"@supabase/postgrest-js": "2.110.2",
"@supabase/realtime-js": "2.110.2",
"@supabase/storage-js": "2.110.2"
},
"engines": {
"node": ">=22.0.0"
}
},
"node_modules/@swc/helpers": { "node_modules/@swc/helpers": {
"version": "0.5.23", "version": "0.5.23",
"resolved": "https://registry.npmjs.org/@swc/helpers/-/helpers-0.5.23.tgz", "resolved": "https://registry.npmjs.org/@swc/helpers/-/helpers-0.5.23.tgz",
@@ -4629,19 +4531,6 @@
"dev": true, "dev": true,
"license": "MIT" "license": "MIT"
}, },
"node_modules/cookie": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/cookie/-/cookie-1.1.1.tgz",
"integrity": "sha512-ei8Aos7ja0weRpFzJnEA9UHJ/7XQmqglbRwnf2ATjcB9Wq874VKH9kfjjirM6UhU2/E5fFYadylyhFldcqSidQ==",
"license": "MIT",
"engines": {
"node": ">=18"
},
"funding": {
"type": "opencollective",
"url": "https://opencollective.com/express"
}
},
"node_modules/cross-spawn": { "node_modules/cross-spawn": {
"version": "7.0.6", "version": "7.0.6",
"resolved": "https://registry.npmjs.org/cross-spawn/-/cross-spawn-7.0.6.tgz", "resolved": "https://registry.npmjs.org/cross-spawn/-/cross-spawn-7.0.6.tgz",
@@ -6993,15 +6882,6 @@
"node": "^20.19.0 || ^22.12.0 || >=24.0.0" "node": "^20.19.0 || ^22.12.0 || >=24.0.0"
} }
}, },
"node_modules/iceberg-js": {
"version": "0.8.1",
"resolved": "https://registry.npmjs.org/iceberg-js/-/iceberg-js-0.8.1.tgz",
"integrity": "sha512-1dhVQZXhcHje7798IVM+xoo/1ZdVfzOMIc8/rgVSijRK38EDqOJoGula9N/8ZI5RD8QTxNQtK/Gozpr+qUqRRA==",
"license": "MIT",
"engines": {
"node": ">=20.0.0"
}
},
"node_modules/idb": { "node_modules/idb": {
"version": "8.0.3", "version": "8.0.3",
"resolved": "https://registry.npmjs.org/idb/-/idb-8.0.3.tgz", "resolved": "https://registry.npmjs.org/idb/-/idb-8.0.3.tgz",
-2
View File
@@ -14,8 +14,6 @@
}, },
"dependencies": { "dependencies": {
"@anthropic-ai/sdk": "^0.111.0", "@anthropic-ai/sdk": "^0.111.0",
"@supabase/ssr": "^0.12.0",
"@supabase/supabase-js": "^2.110.2",
"bcryptjs": "^3.0.3", "bcryptjs": "^3.0.3",
"drizzle-orm": "^0.45.2", "drizzle-orm": "^0.45.2",
"file-type": "^22.0.1", "file-type": "^22.0.1",
-8
View File
@@ -1,8 +0,0 @@
# Supabase
.branches
.temp
# dotenvx
.env.keys
.env.local
.env.*.local
-414
View File
@@ -1,414 +0,0 @@
# For detailed configuration reference documentation, visit:
# https://supabase.com/docs/guides/local-development/cli/config
# A string used to distinguish different Supabase projects on the same host. Defaults to the
# working directory name when running `supabase init`.
project_id = "IMS.nosync"
[api]
enabled = true
# Port to use for the API URL.
port = 54321
# Schemas to expose in your API. Tables, views and stored procedures in this schema will get API
# endpoints. `public` and `graphql_public` schemas are included by default.
schemas = ["public", "graphql_public"]
# Extra schemas to add to the search_path of every request.
extra_search_path = ["public", "extensions"]
# The maximum number of rows returns from a view, table, or stored procedure. Limits payload size
# for accidental or malicious requests.
max_rows = 1000
# Controls whether new tables, views, sequences and functions created in the `public` schema by
# `postgres` are reachable through the Data API roles (`anon`, `authenticated`, `service_role`)
# without explicit GRANTs. When unset, new entities are NOT auto-exposed, matching the new cloud
# default. Set to `true` to keep the legacy behaviour of auto-exposing new entities; this is
# deprecated and the field is removed on 2026-10-30 once the always-revoked behaviour is permanent.
# auto_expose_new_tables = true
[api.tls]
# Enable HTTPS endpoints locally using a self-signed certificate.
enabled = false
# Paths to self-signed certificate pair.
# cert_path = "../certs/my-cert.pem"
# key_path = "../certs/my-key.pem"
[db]
# Port to use for the local database URL.
port = 54322
# Port used by db diff command to initialize the shadow database.
shadow_port = 54320
# Maximum amount of time to wait for health check when starting the local database.
health_timeout = "2m"
# The database major version to use. This has to be the same as your remote database's. Run `SHOW
# server_version;` on the remote database to check.
major_version = 17
[db.pooler]
enabled = false
# Port to use for the local connection pooler.
port = 54329
# Specifies when a server connection can be reused by other clients.
# Configure one of the supported pooler modes: `transaction`, `session`.
pool_mode = "transaction"
# How many server connections to allow per user/database pair.
default_pool_size = 20
# Maximum number of client connections allowed.
max_client_conn = 100
# [db.vault]
# secret_key = "env(SECRET_VALUE)"
[db.migrations]
# If disabled, migrations will be skipped during a db push or reset.
enabled = true
# Specifies an ordered list of schema files, directories, or glob patterns that describe your database.
# Supports paths relative to supabase directory: "./schemas/*.sql", "./database".
schema_paths = []
[db.seed]
# If enabled, seeds the database after migrations during a db reset.
enabled = true
# Specifies an ordered list of seed files to load during db reset.
# Supports glob patterns relative to supabase directory: "./seeds/*.sql"
sql_paths = ["./seed.sql"]
[db.network_restrictions]
# Enable management of network restrictions.
enabled = false
# List of IPv4 CIDR blocks allowed to connect to the database.
# Defaults to allow all IPv4 connections. Set empty array to block all IPs.
allowed_cidrs = ["0.0.0.0/0"]
# List of IPv6 CIDR blocks allowed to connect to the database.
# Defaults to allow all IPv6 connections. Set empty array to block all IPs.
allowed_cidrs_v6 = ["::/0"]
# Uncomment to reject non-secure connections to the database.
# [db.ssl_enforcement]
# enabled = true
[realtime]
enabled = true
# Bind realtime via either IPv4 or IPv6. (default: IPv4)
# ip_version = "IPv6"
# The maximum length in bytes of HTTP request headers. (default: 4096)
# max_header_length = 4096
[studio]
enabled = true
# Port to use for Supabase Studio.
port = 54323
# External URL of the API server that frontend connects to.
api_url = "http://127.0.0.1"
# OpenAI API Key to use for Supabase AI in the Supabase Studio.
openai_api_key = "env(OPENAI_API_KEY)"
# Email testing server. Emails sent with the local dev setup are not actually sent - rather, they
# are monitored, and you can view the emails that would have been sent from the web interface.
[local_smtp]
enabled = true
# Port to use for the email testing server web interface.
port = 54324
# Uncomment to expose additional ports for testing user applications that send emails.
# smtp_port = 54325
# pop3_port = 54326
# admin_email = "admin@email.com"
# sender_name = "Admin"
[storage]
enabled = true
# The maximum file size allowed (e.g. "5MB", "500KB").
file_size_limit = "50MiB"
# Uncomment to configure local storage buckets
# [storage.buckets.images]
# public = false
# file_size_limit = "50MiB"
# allowed_mime_types = ["image/png", "image/jpeg"]
# objects_path = "./images"
# Allow connections via S3 compatible clients
[storage.s3_protocol]
enabled = true
# Image transformation API is available to Supabase Pro plan.
# [storage.image_transformation]
# enabled = true
# Store analytical data in S3 for running ETL jobs over Iceberg Catalog
# This feature is only available on the hosted platform.
[storage.analytics]
enabled = false
max_namespaces = 5
max_tables = 10
max_catalogs = 2
# Analytics Buckets is available to Supabase Pro plan.
# [storage.analytics.buckets.my-warehouse]
# Store vector embeddings in S3 for large and durable datasets
[storage.vector]
enabled = true
max_buckets = 10
max_indexes = 5
# Vector Buckets is available to Supabase Pro plan.
# [storage.vector.buckets.documents-openai]
[auth]
enabled = true
# The base URL of your website. Used as an allow-list for redirects and for constructing URLs used
# in emails.
site_url = "http://127.0.0.1:3000"
# The public URL that Auth serves on. Defaults to the API external URL with `/auth/v1` appended.
# external_url = ""
# A list of *exact* URLs that auth providers are permitted to redirect to post authentication.
additional_redirect_urls = ["https://127.0.0.1:3000"]
# How long tokens are valid for, in seconds. Defaults to 3600 (1 hour), maximum 604,800 (1 week).
jwt_expiry = 3600
# JWT issuer URL. If not set, defaults to auth.external_url.
# jwt_issuer = ""
# Path to JWT signing key. DO NOT commit your signing keys file to git.
# signing_keys_path = "./signing_keys.json"
# If disabled, the refresh token will never expire.
enable_refresh_token_rotation = true
# Allows refresh tokens to be reused after expiry, up to the specified interval in seconds.
# Requires enable_refresh_token_rotation = true.
refresh_token_reuse_interval = 10
# Allow/disallow new user signups to your project.
enable_signup = true
# Allow/disallow anonymous sign-ins to your project.
enable_anonymous_sign_ins = false
# Allow/disallow testing manual linking of accounts
enable_manual_linking = false
# Passwords shorter than this value will be rejected as weak. Minimum 6, recommended 8 or more.
minimum_password_length = 6
# Passwords that do not meet the following requirements will be rejected as weak. Supported values
# are: `letters_digits`, `lower_upper_letters_digits`, `lower_upper_letters_digits_symbols`
password_requirements = ""
# Configure passkey sign-ins.
# [auth.passkey]
# enabled = false
# Configure WebAuthn relying party settings (required when passkey is enabled).
# [auth.webauthn]
# rp_display_name = "Supabase"
# rp_id = "localhost"
# rp_origins = ["http://127.0.0.1:3000"]
[auth.rate_limit]
# Number of emails that can be sent per hour. Requires auth.email.smtp to be enabled.
email_sent = 2
# Number of SMS messages that can be sent per hour. Requires auth.sms to be enabled.
sms_sent = 30
# Number of anonymous sign-ins that can be made per hour per IP address. Requires enable_anonymous_sign_ins = true.
anonymous_users = 30
# Number of sessions that can be refreshed in a 5 minute interval per IP address.
token_refresh = 150
# Number of sign up and sign-in requests that can be made in a 5 minute interval per IP address (excludes anonymous users).
sign_in_sign_ups = 30
# Number of OTP / Magic link verifications that can be made in a 5 minute interval per IP address.
token_verifications = 30
# Number of Web3 logins that can be made in a 5 minute interval per IP address.
web3 = 30
# Configure one of the supported captcha providers: `hcaptcha`, `turnstile`.
# [auth.captcha]
# enabled = true
# provider = "hcaptcha"
# secret = ""
[auth.email]
# Allow/disallow new user signups via email to your project.
enable_signup = true
# If enabled, a user will be required to confirm any email change on both the old, and new email
# addresses. If disabled, only the new email is required to confirm.
double_confirm_changes = true
# If enabled, users need to confirm their email address before signing in.
enable_confirmations = false
# If enabled, users will need to reauthenticate or have logged in recently to change their password.
secure_password_change = false
# Controls the minimum amount of time that must pass before sending another signup confirmation or password reset email.
max_frequency = "1s"
# Number of characters used in the email OTP.
otp_length = 6
# Number of seconds before the email OTP expires (defaults to 1 hour).
otp_expiry = 3600
# Use a production-ready SMTP server
# [auth.email.smtp]
# enabled = true
# host = "smtp.sendgrid.net"
# port = 587
# user = "apikey"
# pass = "env(SENDGRID_API_KEY)"
# admin_email = "admin@email.com"
# sender_name = "Admin"
# Uncomment to customize email template
# [auth.email.template.invite]
# subject = "You have been invited"
# content_path = "./supabase/templates/invite.html"
# Uncomment to customize notification email template
# [auth.email.notification.password_changed]
# enabled = true
# subject = "Your password has been changed"
# content_path = "./templates/password_changed_notification.html"
[auth.sms]
# Allow/disallow new user signups via SMS to your project.
enable_signup = false
# If enabled, users need to confirm their phone number before signing in.
enable_confirmations = false
# Template for sending OTP to users
template = "Your code is {{ `{{ .Code }}` }}"
# Controls the minimum amount of time that must pass before sending another sms otp.
max_frequency = "5s"
# Use pre-defined map of phone number to OTP for testing.
# [auth.sms.test_otp]
# 4152127777 = "123456"
# Configure logged in session timeouts.
# [auth.sessions]
# Force log out after the specified duration.
# timebox = "24h"
# Force log out if the user has been inactive longer than the specified duration.
# inactivity_timeout = "8h"
# This hook runs before a new user is created and allows developers to reject the request based on the incoming user object.
# [auth.hook.before_user_created]
# enabled = true
# uri = "pg-functions://postgres/auth/before-user-created-hook"
# This hook runs before a token is issued and allows you to add additional claims based on the authentication method used.
# [auth.hook.custom_access_token]
# enabled = true
# uri = "pg-functions://<database>/<schema>/<hook_name>"
# Configure one of the supported SMS providers: `twilio`, `twilio_verify`, `messagebird`, `textlocal`, `vonage`.
[auth.sms.twilio]
enabled = false
account_sid = ""
message_service_sid = ""
# DO NOT commit your Twilio auth token to git. Use environment variable substitution instead:
auth_token = "env(SUPABASE_AUTH_SMS_TWILIO_AUTH_TOKEN)"
# Multi-factor-authentication is available to Supabase Pro plan.
[auth.mfa]
# Control how many MFA factors can be enrolled at once per user.
max_enrolled_factors = 10
# Control MFA via App Authenticator (TOTP)
[auth.mfa.totp]
enroll_enabled = false
verify_enabled = false
# Configure MFA via Phone Messaging
[auth.mfa.phone]
enroll_enabled = false
verify_enabled = false
otp_length = 6
template = "Your code is {{ `{{ .Code }}` }}"
max_frequency = "5s"
# Configure MFA via WebAuthn
# [auth.mfa.web_authn]
# enroll_enabled = true
# verify_enabled = true
# Use an external OAuth provider. The full list of providers are: `apple`, `azure`, `bitbucket`,
# `discord`, `facebook`, `github`, `gitlab`, `google`, `keycloak`, `linkedin_oidc`, `notion`, `twitch`,
# `twitter`, `x`, `slack`, `spotify`, `workos`, `zoom`.
[auth.external.apple]
enabled = false
client_id = ""
# DO NOT commit your OAuth provider secret to git. Use environment variable substitution instead:
secret = "env(SUPABASE_AUTH_EXTERNAL_APPLE_SECRET)"
# Overrides the default auth callback URL derived from auth.external_url.
redirect_uri = ""
# Overrides the default auth provider URL. Used to support self-hosted gitlab, single-tenant Azure,
# or any other third-party OIDC providers.
url = ""
# If enabled, the nonce check will be skipped. Required for local sign in with Google auth.
skip_nonce_check = false
# If enabled, it will allow the user to successfully authenticate when the provider does not return an email address.
email_optional = false
# Allow Solana wallet holders to sign in to your project via the Sign in with Solana (SIWS, EIP-4361) standard.
# You can configure "web3" rate limit in the [auth.rate_limit] section and set up [auth.captcha] if self-hosting.
[auth.web3.solana]
enabled = false
# Use Firebase Auth as a third-party provider alongside Supabase Auth.
[auth.third_party.firebase]
enabled = false
# project_id = "my-firebase-project"
# Use Auth0 as a third-party provider alongside Supabase Auth.
[auth.third_party.auth0]
enabled = false
# tenant = "my-auth0-tenant"
# tenant_region = "us"
# Use AWS Cognito (Amplify) as a third-party provider alongside Supabase Auth.
[auth.third_party.aws_cognito]
enabled = false
# user_pool_id = "my-user-pool-id"
# user_pool_region = "us-east-1"
# Use Clerk as a third-party provider alongside Supabase Auth.
[auth.third_party.clerk]
enabled = false
# Obtain from https://clerk.com/setup/supabase
# domain = "example.clerk.accounts.dev"
# OAuth server configuration
[auth.oauth_server]
# Enable OAuth server functionality
enabled = false
# Path for OAuth consent flow UI
authorization_url_path = "/oauth/consent"
# Allow dynamic client registration
allow_dynamic_registration = false
[edge_runtime]
enabled = true
# Supported request policies: `oneshot`, `per_worker`.
# `per_worker` (default) — enables hot reload during local development.
# `oneshot` — fallback mode if hot reload causes issues (e.g. in large repos or with symlinks).
policy = "per_worker"
# Port to attach the Chrome inspector for debugging edge functions.
inspector_port = 8083
# The Deno major version to use.
deno_version = 2
# [edge_runtime.secrets]
# secret_key = "env(SECRET_VALUE)"
[analytics]
enabled = true
port = 54327
# Configure one of the supported backends: `postgres`, `bigquery`.
backend = "postgres"
# Experimental features may be deprecated any time
[experimental]
# Configures Postgres storage engine to use OrioleDB (S3)
orioledb_version = ""
# Configures S3 bucket URL, eg. <bucket_name>.s3-<region>.amazonaws.com
s3_host = "env(S3_HOST)"
# Configures S3 bucket region, eg. us-east-1
s3_region = "env(S3_REGION)"
# Configures AWS_ACCESS_KEY_ID for S3 bucket
s3_access_key = "env(S3_ACCESS_KEY)"
# Configures AWS_SECRET_ACCESS_KEY for S3 bucket
s3_secret_key = "env(S3_SECRET_KEY)"
# pg-delta is the schema diff engine for db diff / db pull / db remote commit.
# Set enabled = false to fall back to the legacy migra engine.
[experimental.pgdelta]
enabled = true
# Directory under `supabase/` where declarative files are written.
# declarative_schema_path = "./database"
# JSON string passed through to pg-delta SQL formatting.
# format_options = "{\"keywordCase\":\"upper\",\"indent\":2,\"maxWidth\":80,\"commaStyle\":\"trailing\"}"
@@ -1,19 +0,0 @@
-- supabase/migrations/20260709000001_sites_zones.sql
CREATE TABLE sites (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
address TEXT,
region TEXT,
active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE zones (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
name TEXT NOT NULL,
qr_code_token TEXT NOT NULL UNIQUE DEFAULT gen_random_uuid()::TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX zones_site_id_idx ON zones(site_id);
@@ -1,40 +0,0 @@
-- supabase/migrations/20260709000002_users.sql
CREATE TYPE user_role AS ENUM (
'reporter', 'supervisor', 'hse', 'capa_owner', 'management', 'admin'
);
CREATE TABLE users (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
name TEXT NOT NULL DEFAULT '',
email TEXT NOT NULL DEFAULT '',
phone TEXT,
role user_role NOT NULL DEFAULT 'reporter',
department TEXT,
site_id UUID REFERENCES sites(id),
active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Auto-create users row when auth user signs up
CREATE OR REPLACE FUNCTION public.handle_new_auth_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
INSERT INTO public.users (id, email, name)
VALUES (
NEW.id,
NEW.email,
COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email)
)
ON CONFLICT (id) DO NOTHING;
RETURN NEW;
END;
$$;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_auth_user();
@@ -1,69 +0,0 @@
-- supabase/migrations/20260709000003_incidents.sql
CREATE TYPE incident_type AS ENUM (
'injury', 'near_miss', 'hazard', 'asset_damage', 'environmental', 'security', 'fire'
);
CREATE TYPE incident_status AS ENUM (
'reported', 'triaged', 'investigating', 'capa_pending', 'verification', 'closed'
);
CREATE TYPE medical_status AS ENUM (
'none', 'first_aid', 'medical_treatment', 'lti'
);
CREATE TABLE incidents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
reference_no TEXT UNIQUE,
incident_type incident_type NOT NULL,
site_id UUID NOT NULL REFERENCES sites(id),
zone_id UUID REFERENCES zones(id),
reported_by UUID NOT NULL REFERENCES users(id),
reported_at TIMESTAMPTZ NOT NULL DEFAULT now(),
description TEXT NOT NULL,
severity SMALLINT CHECK (severity BETWEEN 1 AND 5),
status incident_status NOT NULL DEFAULT 'reported',
injury_involved BOOLEAN NOT NULL DEFAULT false,
asset_involved BOOLEAN NOT NULL DEFAULT false,
medical_status medical_status,
lost_days INT CHECK (lost_days >= 0),
closed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Auto-generate reference_no: SITECODE-YYYYMM-#### (e.g. SCW1-202607-0001)
CREATE OR REPLACE FUNCTION public.generate_incident_reference()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_site_code TEXT;
v_month TEXT;
v_seq INT;
BEGIN
SELECT UPPER(REGEXP_REPLACE(SUBSTRING(name, 1, 6), '[^A-Za-z0-9]', '', 'g'))
INTO v_site_code
FROM sites
WHERE id = NEW.site_id;
v_month := TO_CHAR(NEW.reported_at, 'YYYYMM');
SELECT COUNT(*) + 1
INTO v_seq
FROM incidents
WHERE site_id = NEW.site_id
AND TO_CHAR(reported_at, 'YYYYMM') = v_month;
NEW.reference_no := v_site_code || '-' || v_month || '-' || LPAD(v_seq::TEXT, 4, '0');
RETURN NEW;
END;
$$;
CREATE TRIGGER set_incident_reference
BEFORE INSERT ON incidents
FOR EACH ROW
WHEN (NEW.reference_no IS NULL)
EXECUTE FUNCTION public.generate_incident_reference();
CREATE INDEX incidents_site_id_idx ON incidents(site_id);
CREATE INDEX incidents_reported_by_idx ON incidents(reported_by);
CREATE INDEX incidents_status_idx ON incidents(status);
@@ -1,34 +0,0 @@
-- supabase/migrations/20260709000004_evidence_investigations.sql
CREATE TYPE evidence_stage AS ENUM (
'report', 'response', 'investigation', 'capa', 'verification'
);
CREATE TABLE evidence_files (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
stage evidence_stage NOT NULL,
file_url TEXT NOT NULL,
file_type TEXT NOT NULL,
file_hash TEXT NOT NULL, -- immutable after upload (DOSH audit integrity)
uploaded_by UUID NOT NULL REFERENCES users(id),
uploaded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted BOOLEAN NOT NULL DEFAULT false -- NEVER hard-delete; use this flag
);
CREATE TYPE rca_method AS ENUM ('five_why', 'fishbone', 'other');
CREATE TABLE investigations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
investigator_id UUID NOT NULL REFERENCES users(id),
method rca_method NOT NULL DEFAULT 'five_why',
findings_text TEXT,
root_cause_summary TEXT,
alcohol_test_result TEXT,
witness_statement_refs TEXT[],
completed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX evidence_files_incident_idx ON evidence_files(incident_id);
CREATE INDEX investigations_incident_idx ON investigations(incident_id);
@@ -1,43 +0,0 @@
-- supabase/migrations/20260709000005_capa_dosh.sql
CREATE TYPE capa_priority AS ENUM ('low', 'med', 'high');
CREATE TYPE capa_status AS ENUM (
'open', 'in_progress', 'overdue', 'pending_verification',
'verified', 'reopened', 'closed'
);
CREATE TABLE capa_actions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
root_cause_ref TEXT,
description TEXT NOT NULL,
owner_user_id UUID NOT NULL REFERENCES users(id),
department TEXT NOT NULL,
due_date DATE NOT NULL,
priority capa_priority NOT NULL DEFAULT 'med',
status capa_status NOT NULL DEFAULT 'open',
completed_at TIMESTAMPTZ,
verified_by UUID REFERENCES users(id),
verified_at TIMESTAMPTZ,
effectiveness_recheck_date DATE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TYPE dosh_form_type AS ENUM ('jkkp6', 'jkkp7', 'jkkp8');
CREATE TYPE dosh_status AS ENUM ('not_required', 'pending', 'submitted');
CREATE TABLE dosh_reports (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID NOT NULL REFERENCES incidents(id) ON DELETE RESTRICT,
form_type dosh_form_type NOT NULL,
status dosh_status NOT NULL DEFAULT 'not_required',
submitted_at TIMESTAMPTZ,
submitted_by UUID REFERENCES users(id),
file_url TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX capa_actions_incident_idx ON capa_actions(incident_id);
CREATE INDEX capa_actions_owner_idx ON capa_actions(owner_user_id);
CREATE INDEX capa_actions_status_idx ON capa_actions(status);
CREATE INDEX capa_actions_due_date_idx ON capa_actions(due_date);
@@ -1,26 +0,0 @@
-- supabase/migrations/20260709000006_notifications_audit.sql
CREATE TYPE notification_channel AS ENUM ('email', 'whatsapp', 'in_app');
CREATE TABLE notifications_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID REFERENCES incidents(id),
capa_id UUID REFERENCES capa_actions(id),
channel notification_channel NOT NULL,
recipient TEXT NOT NULL,
sent_at TIMESTAMPTZ NOT NULL DEFAULT now(),
status TEXT NOT NULL DEFAULT 'sent'
);
CREATE TABLE audit_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
table_name TEXT NOT NULL,
record_id UUID NOT NULL,
action TEXT NOT NULL,
changed_by UUID REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
old_value JSONB,
new_value JSONB
);
CREATE INDEX audit_log_table_record_idx ON audit_log(table_name, record_id);
CREATE INDEX audit_log_changed_by_idx ON audit_log(changed_by);
@@ -1,99 +0,0 @@
-- supabase/migrations/20260709000007_rls_policies.sql
-- Enable RLS on all application tables
ALTER TABLE sites ENABLE ROW LEVEL SECURITY;
ALTER TABLE zones ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE incidents ENABLE ROW LEVEL SECURITY;
ALTER TABLE evidence_files ENABLE ROW LEVEL SECURITY;
ALTER TABLE investigations ENABLE ROW LEVEL SECURITY;
ALTER TABLE capa_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE dosh_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
-- Helper functions (SECURITY DEFINER avoids recursion through RLS)
CREATE OR REPLACE FUNCTION public.auth_user_role()
RETURNS user_role
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$ SELECT role FROM public.users WHERE id = auth.uid() $$;
CREATE OR REPLACE FUNCTION public.auth_user_site_id()
RETURNS UUID
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$ SELECT site_id FROM public.users WHERE id = auth.uid() $$;
CREATE OR REPLACE FUNCTION public.auth_user_department()
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$ SELECT department FROM public.users WHERE id = auth.uid() $$;
-- sites: everyone reads active sites; only admin manages
CREATE POLICY "sites_read" ON sites FOR SELECT USING (active = true);
CREATE POLICY "sites_admin_all" ON sites FOR ALL USING (auth_user_role() = 'admin');
-- zones: everyone reads; only admin manages
CREATE POLICY "zones_read" ON zones FOR SELECT USING (true);
CREATE POLICY "zones_admin_all" ON zones FOR ALL USING (auth_user_role() = 'admin');
-- users: own profile always readable; elevated roles see all; admin manages
CREATE POLICY "users_read_own" ON users FOR SELECT USING (id = auth.uid());
CREATE POLICY "users_read_elevated" ON users FOR SELECT
USING (auth_user_role() IN ('hse', 'admin', 'management', 'supervisor'));
CREATE POLICY "users_update_own" ON users FOR UPDATE USING (id = auth.uid());
CREATE POLICY "users_admin_all" ON users FOR ALL USING (auth_user_role() = 'admin');
-- incidents: reporter sees own; supervisor sees own site; elevated sees all
CREATE POLICY "incidents_insert" ON incidents FOR INSERT
WITH CHECK (reported_by = auth.uid());
CREATE POLICY "incidents_read_reporter" ON incidents FOR SELECT
USING (reported_by = auth.uid());
CREATE POLICY "incidents_read_supervisor" ON incidents FOR SELECT
USING (auth_user_role() = 'supervisor' AND site_id = auth_user_site_id());
CREATE POLICY "incidents_read_elevated" ON incidents FOR SELECT
USING (auth_user_role() IN ('hse', 'admin', 'management'));
CREATE POLICY "incidents_update_elevated" ON incidents FOR UPDATE
USING (auth_user_role() IN ('hse', 'admin', 'supervisor'));
-- evidence_files: inserter or incident owner sees own; elevated sees all; no delete
CREATE POLICY "evidence_insert" ON evidence_files FOR INSERT
WITH CHECK (uploaded_by = auth.uid());
CREATE POLICY "evidence_read_uploader" ON evidence_files FOR SELECT
USING (uploaded_by = auth.uid());
CREATE POLICY "evidence_read_elevated" ON evidence_files FOR SELECT
USING (auth_user_role() IN ('hse', 'admin', 'management', 'supervisor'));
-- No DELETE policy -- files are never deleted (soft-delete only via `deleted` flag)
-- investigations: hse/admin full access; supervisor read-only
CREATE POLICY "investigations_hse_admin" ON investigations FOR ALL
USING (auth_user_role() IN ('hse', 'admin'));
CREATE POLICY "investigations_read_supervisor" ON investigations FOR SELECT
USING (auth_user_role() = 'supervisor');
-- capa_actions: owner/dept reads and updates own; elevated reads all; hse/admin manages
CREATE POLICY "capa_read_owner" ON capa_actions FOR SELECT
USING (owner_user_id = auth.uid() OR department = auth_user_department());
CREATE POLICY "capa_update_owner" ON capa_actions FOR UPDATE
USING (owner_user_id = auth.uid() OR department = auth_user_department());
CREATE POLICY "capa_read_elevated" ON capa_actions FOR SELECT
USING (auth_user_role() IN ('hse', 'admin', 'management', 'supervisor'));
CREATE POLICY "capa_hse_admin_all" ON capa_actions FOR ALL
USING (auth_user_role() IN ('hse', 'admin'));
-- dosh_reports: hse/admin only
CREATE POLICY "dosh_hse_admin" ON dosh_reports FOR ALL
USING (auth_user_role() IN ('hse', 'admin'));
-- notifications_log: hse/admin read
CREATE POLICY "notifications_read_elevated" ON notifications_log FOR SELECT
USING (auth_user_role() IN ('hse', 'admin'));
-- audit_log: hse/admin read only -- no writes from app (writes via SECURITY DEFINER functions)
CREATE POLICY "audit_read_elevated" ON audit_log FOR SELECT
USING (auth_user_role() IN ('hse', 'admin'));
@@ -1,34 +0,0 @@
-- supabase/migrations/20260709000008_seed.sql
-- Test site: Setia Corp Warehouse 1
INSERT INTO sites (id, name, address, region, active)
VALUES (
'00000000-0000-0000-0000-000000000001'::UUID,
'SCW1',
'No. 1, Jalan Industri 1, Shah Alam, Selangor',
'Central',
true
)
ON CONFLICT (id) DO NOTHING;
-- Zones for SCW1
INSERT INTO zones (id, site_id, name, qr_code_token)
VALUES
(
'00000000-0000-0000-0000-000000000010'::UUID,
'00000000-0000-0000-0000-000000000001'::UUID,
'Dock A',
'scw1-dock-a-qr-2026'
),
(
'00000000-0000-0000-0000-000000000011'::UUID,
'00000000-0000-0000-0000-000000000001'::UUID,
'Cold Storage',
'scw1-cold-storage-qr-2026'
),
(
'00000000-0000-0000-0000-000000000012'::UUID,
'00000000-0000-0000-0000-000000000001'::UUID,
'Loading Bay',
'scw1-loading-bay-qr-2026'
)
ON CONFLICT (id) DO NOTHING;
@@ -1,113 +0,0 @@
-- supabase/migrations/20260709000009_security_fixes.sql
-- Security fixes from IMS Phase 0 final code review
-- =============================================================================
-- 1. CRITICAL: Drop and recreate users_update_own with WITH CHECK to prevent
-- role self-escalation (any user setting their own role to admin)
-- =============================================================================
DROP POLICY IF EXISTS "users_update_own" ON users;
CREATE POLICY "users_update_own" ON users
FOR UPDATE
USING (id = auth.uid())
WITH CHECK (
id = auth.uid()
AND role = (SELECT role FROM public.users WHERE id = auth.uid())
);
-- =============================================================================
-- 2. IMPORTANT: SECURITY DEFINER audit writer function — app code calls this
-- instead of direct INSERT on audit_log
-- =============================================================================
CREATE OR REPLACE FUNCTION public.write_audit_log(
p_table_name TEXT,
p_record_id UUID,
p_action TEXT,
p_old_value JSONB DEFAULT NULL,
p_new_value JSONB DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
INSERT INTO public.audit_log (table_name, record_id, action, changed_by, old_value, new_value)
VALUES (p_table_name, p_record_id, p_action, auth.uid(), p_old_value, p_new_value);
END;
$$;
-- =============================================================================
-- 3. IMPORTANT: notifications_log INSERT policy for elevated roles
-- =============================================================================
CREATE POLICY "notifications_insert_elevated" ON notifications_log
FOR INSERT
WITH CHECK (auth_user_role() IN ('hse', 'admin', 'supervisor'));
-- =============================================================================
-- 4. IMPORTANT: Recreate generate_incident_reference trigger function with
-- SECURITY DEFINER and SET search_path to prevent search_path injection
-- =============================================================================
CREATE OR REPLACE FUNCTION public.generate_incident_reference()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
v_site_code TEXT;
v_month TEXT;
v_seq INT;
BEGIN
SELECT UPPER(REGEXP_REPLACE(SUBSTRING(name, 1, 6), '[^A-Za-z0-9]', '', 'g'))
INTO v_site_code
FROM sites
WHERE id = NEW.site_id;
v_month := TO_CHAR(NEW.reported_at, 'YYYYMM');
SELECT COUNT(*) + 1
INTO v_seq
FROM incidents
WHERE site_id = NEW.site_id
AND TO_CHAR(reported_at, 'YYYYMM') = v_month;
NEW.reference_no := COALESCE(v_site_code, 'UNK') || '-' || v_month || '-' || LPAD(v_seq::TEXT, 4, '0');
RETURN NEW;
END;
$$;
-- =============================================================================
-- 5. IMPORTANT: Recreate helper functions with SET search_path to prevent
-- search_path hijacking attacks
-- =============================================================================
CREATE OR REPLACE FUNCTION public.auth_user_role()
RETURNS user_role
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$ SELECT role FROM public.users WHERE id = auth.uid() $$;
CREATE OR REPLACE FUNCTION public.auth_user_site_id()
RETURNS UUID
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$ SELECT site_id FROM public.users WHERE id = auth.uid() $$;
CREATE OR REPLACE FUNCTION public.auth_user_department()
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$ SELECT department FROM public.users WHERE id = auth.uid() $$;
-- =============================================================================
-- 6. IMPORTANT: Add UNIQUE constraint to dosh_reports to prevent duplicate
-- form submissions for the same incident
-- =============================================================================
ALTER TABLE dosh_reports
ADD CONSTRAINT dosh_reports_incident_form_uq UNIQUE (incident_id, form_type);
@@ -1,37 +0,0 @@
-- Create evidence storage bucket (private)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
'evidence',
'evidence',
false,
209715200, -- 200MB
ARRAY[
'image/jpeg', 'image/png', 'image/heic', 'image/webp',
'video/mp4', 'video/quicktime',
'application/pdf',
'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
]
)
ON CONFLICT (id) DO NOTHING;
-- RLS on storage.objects for the evidence bucket
CREATE POLICY "evidence_upload_authenticated"
ON storage.objects FOR INSERT
TO authenticated
WITH CHECK (bucket_id = 'evidence');
CREATE POLICY "evidence_read_uploader"
ON storage.objects FOR SELECT
TO authenticated
USING (
bucket_id = 'evidence'
AND auth.uid()::text = (storage.foldername(name))[1]
);
CREATE POLICY "evidence_read_elevated"
ON storage.objects FOR SELECT
TO authenticated
USING (
bucket_id = 'evidence'
AND public.auth_user_role() IN ('hse', 'admin', 'management', 'supervisor')
);
@@ -1,33 +0,0 @@
-- Triage classification fields on incidents
ALTER TABLE incidents
ADD COLUMN IF NOT EXISTS is_fatality BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS is_serious_bodily_injury BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS is_dangerous_occurrence BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS is_occupational_disease BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS triage_notes TEXT,
ADD COLUMN IF NOT EXISTS triaged_by UUID REFERENCES users(id),
ADD COLUMN IF NOT EXISTS triaged_at TIMESTAMPTZ;
-- Structured RCA data on investigations
ALTER TABLE investigations
ADD COLUMN IF NOT EXISTS five_why_steps JSONB,
ADD COLUMN IF NOT EXISTS fishbone_categories JSONB;
-- Audit log write helper — SECURITY DEFINER so callers cannot bypass RLS
DROP FUNCTION IF EXISTS public.write_audit_log CASCADE;
CREATE OR REPLACE FUNCTION public.write_audit_log(
p_table_name TEXT,
p_record_id UUID,
p_action TEXT,
p_new_value JSONB DEFAULT NULL,
p_old_value JSONB DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (table_name, record_id, action, changed_by, new_value, old_value)
VALUES (p_table_name, p_record_id, p_action, auth.uid(), p_new_value, p_old_value);
END;
$$;
@@ -1,42 +0,0 @@
-- Enable pgvector
create extension if not exists vector;
-- Add embedding column to incidents (nullable — populated async after creation)
alter table incidents
add column if not exists embedding vector(1024);
-- IVFFlat index for cosine similarity (10 lists sufficient for <10k rows)
create index if not exists incidents_embedding_idx
on incidents using ivfflat (embedding vector_cosine_ops)
with (lists = 10);
-- Similarity search — returns top-N most similar incidents (cosine)
create or replace function match_incidents(
query_embedding vector(1024),
exclude_id uuid,
match_count int default 5
)
returns table (
id uuid,
reference_no text,
incident_type text,
description text,
severity int,
similarity float
)
language sql
security definer
as $$
select
i.id,
i.reference_no,
i.incident_type,
i.description,
i.severity,
1 - (i.embedding <=> query_embedding) as similarity
from incidents i
where i.id != exclude_id
and i.embedding is not null
order by i.embedding <=> query_embedding
limit match_count;
$$;
@@ -1,43 +0,0 @@
-- Add authorization guard to match_incidents to enforce DB-level access control.
-- Without this, SECURITY DEFINER bypasses RLS for any direct caller.
create or replace function match_incidents(
query_embedding vector(1024),
exclude_id uuid,
match_count int default 5
)
returns table (
id uuid,
reference_no text,
incident_type text,
description text,
severity int,
similarity float
)
language plpgsql
security definer
as $$
begin
-- Enforce that only hse/admin roles can call this function directly
if not exists (
select 1 from public.users
where id = auth.uid()
and role in ('hse', 'admin')
) then
raise exception 'Forbidden' using errcode = 'PGRST301';
end if;
return query
select
i.id,
i.reference_no,
i.incident_type,
i.description,
i.severity,
1 - (i.embedding <=> query_embedding) as similarity
from incidents i
where i.id != exclude_id
and i.embedding is not null
order by i.embedding <=> query_embedding
limit match_count;
end;
$$;
@@ -1,25 +0,0 @@
create table if not exists app_settings (
key text primary key,
value text not null,
updated_at timestamptz not null default now(),
updated_by uuid references users(id)
);
-- Only admins can read or write settings
alter table app_settings enable row level security;
create policy "admin_select_settings" on app_settings
for select using (
exists (select 1 from users where id = auth.uid() and role = 'admin')
);
create policy "admin_update_settings" on app_settings
for all using (
exists (select 1 from users where id = auth.uid() and role = 'admin')
);
-- Placeholder rows (empty value means "use env var")
insert into app_settings (key, value) values
('ANTHROPIC_API_KEY', ''),
('VOYAGE_API_KEY', '')
on conflict (key) do nothing;
@@ -1,10 +0,0 @@
-- Add effectiveness recheck round tracking to capa_actions
ALTER TABLE capa_actions
ADD COLUMN IF NOT EXISTS effectiveness_recheck_round INT NOT NULL DEFAULT 0;
-- WhatsApp credentials for Settings UI
INSERT INTO app_settings (key, value, updated_at, updated_by)
VALUES
('META_WHATSAPP_PHONE_NUMBER_ID', '', now(), NULL),
('META_WHATSAPP_ACCESS_TOKEN', '', now(), NULL)
ON CONFLICT (key) DO NOTHING;
@@ -1,50 +0,0 @@
-- supabase/migrations/20260712000016_in_app_notifications.sql
-- Phase 5: in-app notification bell — read state, content columns, per-user RLS,
-- and a SECURITY DEFINER writer so non-elevated reporters can trigger alerts.
ALTER TABLE notifications_log
ADD COLUMN recipient_user_id UUID REFERENCES users(id),
ADD COLUMN read_at TIMESTAMPTZ,
ADD COLUMN title TEXT,
ADD COLUMN link TEXT;
CREATE INDEX notifications_in_app_unread_idx
ON notifications_log (recipient_user_id, sent_at DESC)
WHERE channel = 'in_app' AND read_at IS NULL;
-- Users see their own in-app notifications (elevated read policy already exists)
CREATE POLICY "notifications_read_own" ON notifications_log
FOR SELECT
USING (recipient_user_id = auth.uid());
-- Users may only mark their own notifications read
CREATE POLICY "notifications_update_own" ON notifications_log
FOR UPDATE
USING (recipient_user_id = auth.uid())
WITH CHECK (recipient_user_id = auth.uid());
-- Writer RPC: INSERT policy on notifications_log is elevated-roles-only, but a
-- reporter submitting an incident must notify supervisors/HSE. Mirrors write_audit_log.
CREATE OR REPLACE FUNCTION public.create_in_app_notification(
p_recipient UUID,
p_title TEXT,
p_link TEXT DEFAULT NULL,
p_incident_id UUID DEFAULT NULL,
p_capa_id UUID DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
IF auth.uid() IS NULL THEN
RAISE EXCEPTION 'authentication required';
END IF;
INSERT INTO public.notifications_log
(channel, recipient, recipient_user_id, title, link, incident_id, capa_id)
VALUES
('in_app', p_recipient::text, p_recipient, p_title, p_link, p_incident_id, p_capa_id);
END;
$$;
@@ -1,57 +0,0 @@
-- supabase/migrations/20260712000017_closure_lock_addenda.sql
-- Phase 5: closed incidents become immutable (addenda-only), per PRD §4 Phase 4.
CREATE TABLE incident_addenda (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
incident_id UUID NOT NULL REFERENCES incidents(id),
author UUID NOT NULL REFERENCES users(id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX incident_addenda_incident_idx ON incident_addenda(incident_id);
ALTER TABLE incident_addenda ENABLE ROW LEVEL SECURITY;
CREATE POLICY "addenda_read" ON incident_addenda
FOR SELECT
USING (
auth_user_role() IN ('hse', 'admin', 'supervisor', 'management')
OR EXISTS (
SELECT 1 FROM incidents i
WHERE i.id = incident_id AND i.reported_by = auth.uid()
)
);
CREATE POLICY "addenda_insert" ON incident_addenda
FOR INSERT
WITH CHECK (
author = auth.uid()
AND auth_user_role() IN ('hse', 'admin', 'supervisor')
);
-- No UPDATE/DELETE policies: addenda are append-only.
-- DB-level lock: once closed, the incident row cannot change or be deleted.
CREATE OR REPLACE FUNCTION public.prevent_closed_incident_change()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
RAISE EXCEPTION 'incident % is closed and locked — add an addendum instead', OLD.id;
END;
$$;
CREATE TRIGGER incidents_closed_lock_update
BEFORE UPDATE ON incidents
FOR EACH ROW
WHEN (OLD.status = 'closed')
EXECUTE FUNCTION prevent_closed_incident_change();
CREATE TRIGGER incidents_closed_lock_delete
BEFORE DELETE ON incidents
FOR EACH ROW
WHEN (OLD.status = 'closed')
EXECUTE FUNCTION prevent_closed_incident_change();
@@ -1,5 +0,0 @@
-- supabase/migrations/20260712000018_type_details.sql
-- Phase 5: type-specific intake fields (PRD §3 — each incident type has its own
-- lightweight form). Stored as validated JSONB; keys whitelisted in app code.
ALTER TABLE incidents ADD COLUMN type_details JSONB;
@@ -1,12 +0,0 @@
-- Fix: notifications_log RLS was allowing any hse/admin to read ALL notifications.
-- Replace with per-user policy (everyone reads own) + admin-only policy for oversight.
DROP POLICY IF EXISTS "notifications_read_elevated" ON notifications_log;
DROP POLICY IF EXISTS "notifications_read_own" ON notifications_log;
DROP POLICY IF EXISTS "notifications_read_admin" ON notifications_log;
CREATE POLICY "notifications_read_own" ON notifications_log
FOR SELECT USING (recipient_user_id = auth.uid());
CREATE POLICY "notifications_read_admin" ON notifications_log
FOR SELECT USING (auth_user_role() = 'admin');
@@ -1,4 +0,0 @@
-- Add DEEPSEEK_API_KEY placeholder to app_settings
insert into app_settings (key, value) values
('DEEPSEEK_API_KEY', '')
on conflict (key) do nothing;
@@ -1,49 +0,0 @@
-- Migrate embeddings from Voyage AI (1024-dim) to Gemini text-embedding-004 (768-dim)
-- Drop dependent objects first
drop index if exists incidents_embedding_idx;
drop function if exists match_incidents;
-- Replace column (dimension change requires drop+add)
alter table incidents drop column if exists embedding;
alter table incidents add column embedding vector(768);
-- Recreate index
create index incidents_embedding_idx
on incidents using ivfflat (embedding vector_cosine_ops)
with (lists = 10);
-- Recreate similarity function at 768-dim
create or replace function match_incidents(
query_embedding vector(768),
exclude_id uuid,
match_count int default 5
)
returns table (
id uuid,
reference_no text,
incident_type text,
description text,
severity int,
similarity float
)
language sql
security definer
as $$
select
i.id,
i.reference_no,
i.incident_type,
i.description,
i.severity,
1 - (i.embedding <=> query_embedding) as similarity
from incidents i
where i.id != exclude_id
and i.embedding is not null
order by i.embedding <=> query_embedding
limit match_count;
$$;
-- Register Gemini key slot, remove Voyage slot
insert into app_settings (key, value) values ('GOOGLE_AI_API_KEY', '') on conflict (key) do nothing;
delete from app_settings where key = 'VOYAGE_API_KEY';
@@ -1,23 +0,0 @@
-- Reconcile app_settings after provider switches:
-- DeepSeek key was saved under ANTHROPIC_API_KEY slot (before rename) — recover it.
-- Remove retired ANTHROPIC_API_KEY and VOYAGE_API_KEY slots.
-- Ensure DeepSeek slot exists
insert into app_settings (key, value) values ('DEEPSEEK_API_KEY', '')
on conflict (key) do nothing;
-- Copy orphaned value: ANTHROPIC_API_KEY → DEEPSEEK_API_KEY (only when DEEPSEEK_API_KEY is empty and ANTHROPIC has a value)
update app_settings dk
set value = ak.value, updated_at = now()
from app_settings ak
where dk.key = 'DEEPSEEK_API_KEY'
and ak.key = 'ANTHROPIC_API_KEY'
and coalesce(dk.value, '') = ''
and coalesce(ak.value, '') <> '';
-- Ensure Gemini slot exists
insert into app_settings (key, value) values ('GOOGLE_AI_API_KEY', '')
on conflict (key) do nothing;
-- Remove retired provider slots
delete from app_settings where key in ('ANTHROPIC_API_KEY', 'VOYAGE_API_KEY');
@@ -1,3 +0,0 @@
-- Add 'transport' to the incident_type enum. Isolated so the new value is
-- committed before any later migration references it.
ALTER TYPE incident_type ADD VALUE IF NOT EXISTS 'transport';
@@ -1,14 +0,0 @@
CREATE TABLE trucks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
truck_no TEXT NOT NULL UNIQUE,
carrier TEXT,
active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE incidents ADD COLUMN truck_id UUID REFERENCES trucks(id);
CREATE INDEX incidents_truck_id_idx ON incidents(truck_id);
ALTER TABLE trucks ENABLE ROW LEVEL SECURITY;
CREATE POLICY "trucks_read" ON trucks FOR SELECT USING (true);
CREATE POLICY "trucks_admin_all" ON trucks FOR ALL USING (auth_user_role() = 'admin');
@@ -1,4 +0,0 @@
-- Add soft-delete support to zones (sites and trucks already have this column).
-- Leave zones_read as USING (true) so deactivated zones still render on historical incidents.
-- Deactivation is enforced for new reports in the create-incident API, not via RLS.
ALTER TABLE zones ADD COLUMN active BOOLEAN NOT NULL DEFAULT true;
@@ -1,3 +0,0 @@
ALTER TABLE investigations
ADD COLUMN IF NOT EXISTS urine_test_result text
CHECK (urine_test_result IN ('negative','positive','refused','pending'));
@@ -1 +0,0 @@
ALTER TABLE capa_actions ADD COLUMN owner_notes TEXT;
@@ -1,6 +0,0 @@
-- Tighten capa_update_owner RLS policy: remove department clause so only the
-- assigned owner can UPDATE their own CAPA row directly. Route-level auth
-- enforces field-level restrictions; this ensures DB-level enforcement matches.
DROP POLICY IF EXISTS "capa_update_owner" ON capa_actions;
CREATE POLICY "capa_update_owner" ON capa_actions FOR UPDATE
USING (owner_user_id = auth.uid());
@@ -1,14 +0,0 @@
CREATE TABLE password_reset_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash TEXT NOT NULL UNIQUE,
expires_at TIMESTAMPTZ NOT NULL,
used_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX password_reset_tokens_user_idx ON password_reset_tokens(user_id);
CREATE INDEX password_reset_tokens_hash_idx ON password_reset_tokens(token_hash);
-- password_reset_tokens: accessed via app_admin only (BYPASSRLS); no user-facing policies needed
-- GRANT already covered by: GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin
-59
View File
@@ -1,59 +0,0 @@
import { describe, it, expect, vi, beforeEach } from 'vitest'
import { uploadEvidenceFile, getEvidenceUrl } from '@/lib/supabase/storage'
vi.mock('file-type', () => ({
fileTypeFromBuffer: vi.fn().mockResolvedValue({ mime: 'image/jpeg', ext: 'jpg' }),
}))
const mockUpload = vi.fn()
const mockCreateSignedUrl = vi.fn()
const mockSupabase = {
storage: {
from: vi.fn(() => ({
upload: mockUpload,
createSignedUrl: mockCreateSignedUrl,
})),
},
} as any
beforeEach(() => {
vi.clearAllMocks()
mockUpload.mockResolvedValue({ data: { path: 'user-123/incident-abc/report/photo.jpg' }, error: null })
mockCreateSignedUrl.mockResolvedValue({ data: { signedUrl: 'https://example.com/photo.jpg' }, error: null })
})
describe('uploadEvidenceFile', () => {
it('uploads to path user-id/incident-id/stage/filename', async () => {
const file = new File(['content'], 'photo.jpg', { type: 'image/jpeg' })
const result = await uploadEvidenceFile(mockSupabase, file, 'incident-abc', 'report', 'user-123')
expect(mockSupabase.storage.from).toHaveBeenCalledWith('evidence')
expect(mockUpload).toHaveBeenCalledWith(
expect.stringContaining('user-123/incident-abc/report/'),
expect.any(ArrayBuffer),
expect.objectContaining({ contentType: 'image/jpeg', upsert: false })
)
expect(mockCreateSignedUrl).toHaveBeenCalledWith(expect.stringContaining('user-123/incident-abc/report/'), 315360000)
expect(result.path).toContain('user-123/incident-abc/report/')
expect(result.hash).toBeTruthy()
})
it('throws on upload error', async () => {
mockUpload.mockResolvedValue({ data: null, error: { message: 'Bucket not found' } })
const file = new File(['x'], 'f.jpg', { type: 'image/jpeg' })
await expect(uploadEvidenceFile(mockSupabase, file, 'inc', 'report', 'user-123')).rejects.toThrow('Bucket not found')
})
it('throws when photo exceeds 10MB', async () => {
const bigFile = new File([new Uint8Array(11 * 1024 * 1024)], 'big.jpg', { type: 'image/jpeg' })
await expect(uploadEvidenceFile(mockSupabase, bigFile, 'inc', 'report', 'user-123')).rejects.toThrow('File too large')
})
})
describe('getEvidenceUrl', () => {
it('returns signed URL for path', async () => {
const url = await getEvidenceUrl(mockSupabase, 'user-123/incident-abc/report/photo.jpg')
expect(mockSupabase.storage.from).toHaveBeenCalledWith('evidence')
expect(mockCreateSignedUrl).toHaveBeenCalledWith('user-123/incident-abc/report/photo.jpg', 3600)
expect(url).toBe('https://example.com/photo.jpg')
})
})