SOP-PHOTO-01: Dog Photo System v1.0¶

Status: Production Ready (Phase A, pre-launch)
Last Updated: 2026-04-15
Owner: Protocol Raw Operations

Overview¶

Collect, moderate, and display customer dog photos for use on the website, social channels, and paid advertising. Channel-scoped consent captured at submission, terminal revocation supported, moderation handled in the Ops Portal Dog Photos tab.

Philosophy: Real customer dogs are the strongest proof a raw diet is working. The system treats each distribution channel as a separate consent decision because legal exposure and reputational risk differ between the website (owned surface), social media (semi-public, screenshotted), and paid ads (commercial use, broadest reach).

Cross-references:

SOP-TEST-01 - Testimonials moderation follows the same queue and approval pattern.
SOP-LC-01 - Future integration: lifecycle email trigger to prompt customers to upload a portal photo once they hit a tenure milestone (Phase B).
SOP-HDI-01 - Health checkpoint submissions carry an optional photo via customer_health_checkpoints.photo_storage_path.

Collection Channels¶

The source column on raw_ops.dog_photos records how a photo arrived. Allowed values:

Source	Description	Active in
`portal`	Customer self-upload through the Customer Portal	Phase B
`founder_email`	Photo attached to a direct email reply to the founder	Phase A and Phase B
`support_reply`	Photo attached to a support ticket reply	Phase B
`checkpoint`	Photo uploaded with a health checkpoint submission (SOP-HDI-01)	Phase B
`manual`	Operator-initiated upload on behalf of a customer	Phase A and Phase B

Phase A vs Phase B¶

Phase A (now, pre-launch / beta): Only founder_email and manual are active. Volume is low. Photos are handled case-by-case: the founder forwards the message, an operator uploads the file through the Ops Portal with the appropriate consent flags captured from the email conversation.

Phase B (post-launch): The portal upload flow becomes the primary channel. support_reply and checkpoint begin collecting alongside it. A lifecycle email (SOP-LC-01, future integration) prompts customers at a tenure milestone to upload their first photo.

Moderation Workflow¶

1. Photo arrives via one of the five source channels
       |
2. dog-photo-upload Edge Function (portal) or submit_dog_photo RPC (manual)
   inserts a row into raw_ops.dog_photos with status = 'submitted'
       |
3. Photo appears in Ops Portal -> Dog Photos tab
       |
4. Operator reviews thumbnail, caption, consent flags, source
       |
5. Operator acts:
   - Approve -> status = 'approved'
   - Feature -> status = 'featured' (pinned on Dog Wall)
   - Reject  -> status = 'rejected' with rejection_reason
   - Revoke  -> status = 'revoked' (terminal, consent_revoked = TRUE)
       |
6. Public Dog Wall (v_dog_wall_display) renders approved and featured
   photos with consent_website = TRUE, ordered featured-first then by
   display_order then by created_at

State Machine¶

From	To	Trigger	Side effects
`submitted`	`approved`	Operator clicks Approve	`moderated_at`, `moderated_by` set
`submitted`	`featured`	Operator clicks Feature	`moderated_at`, `moderated_by` set; appears first on Dog Wall
`submitted`	`rejected`	Operator clicks Reject with a rejection reason	`moderated_at`, `moderated_by`, `rejection_reason` set
`approved`	`featured`	Operator clicks Feature	Same as above
`featured`	`approved`	Operator clears the feature flag	Same as above
any non-revoked	`revoked`	Operator clicks Revoke with reason	`consent_revoked = TRUE`, `consent_revoked_at = NOW()`, `consent_revoked_reason` set. Terminal.

Revoked rows cannot be re-approved. Moderation RPCs short-circuit with FALSE if the row is already revoked.

Consent is captured as three independent booleans at submission time:

Column	Channel	Typical default
`consent_website`	Public Dog Wall on protocolraw.co.uk	TRUE
`consent_social`	Organic social posts (Instagram, TikTok)	FALSE
`consent_ads`	Paid advertising creative (Meta, Google)	FALSE

consent_granted_at is recorded at insert. consent_method is derived from source:

Source	`consent_method`
`portal`	`portal_checkbox`
`founder_email`	`email_reply`
`support_reply`	`email_reply`
`checkpoint`	`portal_checkbox`
`manual`	`manual`

Why channel-scoped: A customer may be delighted to see their dog on our website, mildly uncomfortable with it being shared on Instagram, and actively uncomfortable with it appearing in a paid Meta ad seen by people they know. Treating the three as separate decisions avoids the all-or-nothing trap and matches how the legal and reputational risk profile varies across surfaces.

Revocation¶

Revocation is terminal. The operator is prompted for a reason, confirms the destructive action, and the row moves to status = 'revoked' with consent_revoked = TRUE. There is no un-revoke path. The revoke_dog_photo_consent RPC is scoped by WHERE consent_revoked = FALSE so repeated calls are no-ops.

Every revocation logs a DOG_PHOTO event to raw_ops.ops_events with the reason and operator identity captured in meta.

Database Schema¶

Table: `raw_ops.dog_photos`¶

CREATE TABLE raw_ops.dog_photos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES raw_ops.customers(id),
  dog_id UUID NOT NULL,

  -- Storage
  storage_bucket TEXT NOT NULL DEFAULT 'dog-photos',
  storage_path TEXT NOT NULL,
  original_filename TEXT,
  file_size_bytes INTEGER,
  mime_type TEXT NOT NULL CHECK (mime_type IN (
    'image/jpeg', 'image/png', 'image/webp', 'image/heic'
  )),

  -- Context
  dog_name TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'portal' CHECK (source IN (
    'portal', 'founder_email', 'support_reply', 'checkpoint', 'manual'
  )),
  human_in_frame BOOLEAN,
  contains_health_claim BOOLEAN NOT NULL DEFAULT FALSE,

  -- Consent (channel-scoped)
  consent_website BOOLEAN NOT NULL DEFAULT FALSE,
  consent_social BOOLEAN NOT NULL DEFAULT FALSE,
  consent_ads BOOLEAN NOT NULL DEFAULT FALSE,
  consent_granted_at TIMESTAMPTZ,
  consent_method TEXT CHECK (consent_method IN (
    'portal_checkbox', 'email_reply', 'manual'
  )),
  consent_revoked BOOLEAN NOT NULL DEFAULT FALSE,
  consent_revoked_at TIMESTAMPTZ,
  consent_revoked_reason TEXT,

  -- Moderation
  status TEXT NOT NULL DEFAULT 'submitted' CHECK (status IN (
    'submitted', 'approved', 'featured', 'rejected', 'revoked'
  )),
  moderated_at TIMESTAMPTZ,
  moderated_by TEXT,
  rejection_reason TEXT CHECK (rejection_reason IN (
    'low_quality', 'not_a_dog', 'inappropriate',
    'copyright_concern', 'human_consent', 'duplicate', 'other'
  )),

  -- Display
  display_order INTEGER,
  caption TEXT,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_dog_photos_customer ON raw_ops.dog_photos(customer_id);
CREATE INDEX idx_dog_photos_dog ON raw_ops.dog_photos(dog_id);
CREATE INDEX idx_dog_photos_status ON raw_ops.dog_photos(status);
CREATE INDEX idx_dog_photos_featured ON raw_ops.dog_photos(status)
  WHERE status = 'featured';
CREATE INDEX idx_dog_photos_approved ON raw_ops.dog_photos(status)
  WHERE status IN ('approved', 'featured') AND consent_revoked = FALSE;
CREATE INDEX idx_dog_photos_moderation_queue ON raw_ops.dog_photos(created_at)
  WHERE status = 'submitted';

ALTER TABLE raw_ops.dog_photos ENABLE ROW LEVEL SECURITY;

Additional column: `raw_ops.customer_health_checkpoints.photo_storage_path`¶

ALTER TABLE raw_ops.customer_health_checkpoints
  ADD COLUMN photo_storage_path TEXT;

Optional path to a photo uploaded with a health checkpoint submission. See SOP-HDI-01.

View: `public.v_dog_wall_display`¶

CREATE OR REPLACE VIEW public.v_dog_wall_display AS
SELECT
  p.id,
  p.dog_name,
  p.caption,
  c.first_name,
  TO_CHAR(c.created_at, 'Month YYYY') AS customer_since,
  COUNT(o.id) AS order_count,
  p.display_order,
  p.status
FROM raw_ops.dog_photos p
JOIN raw_ops.customers c ON c.id = p.customer_id
LEFT JOIN raw_ops.orders o ON o.customer_id = c.id
  AND o.export_state = 'EXPORTED'
WHERE p.status IN ('approved', 'featured')
  AND p.consent_revoked = FALSE
  AND p.consent_website = TRUE
GROUP BY p.id, p.dog_name, p.caption, c.first_name, c.created_at,
         p.display_order, p.status, p.created_at
ORDER BY (p.status = 'featured') DESC, p.display_order ASC NULLS LAST, p.created_at DESC;

GRANT SELECT ON public.v_dog_wall_display TO anon;

order_count updates automatically as the customer continues ordering, the same live-data pattern used for testimonials.

RPC Functions¶

All four functions live in the public schema and are SECURITY DEFINER.

`public.submit_dog_photo`¶

Inserts a new photo row. Derives consent_method from p_source. Logs a DOG_PHOTO INFO event to raw_ops.ops_events.

submit_dog_photo(
  p_customer_id UUID,
  p_dog_id UUID,
  p_dog_name TEXT,
  p_storage_path TEXT,
  p_original_filename TEXT DEFAULT NULL,
  p_file_size_bytes INTEGER DEFAULT NULL,
  p_mime_type TEXT DEFAULT 'image/jpeg',
  p_source TEXT DEFAULT 'portal',
  p_consent_website BOOLEAN DEFAULT TRUE,
  p_consent_social BOOLEAN DEFAULT FALSE,
  p_consent_ads BOOLEAN DEFAULT FALSE,
  p_caption TEXT DEFAULT NULL
) RETURNS UUID

`public.get_dog_photos_queue`¶

Returns the moderation queue for the Ops Portal. Filters consent_revoked = FALSE and status in (submitted, approved, featured, rejected). Submitted-first, LIMIT 100. Casts customers.email (USER-DEFINED domain) to text.

get_dog_photos_queue() RETURNS TABLE (
  id UUID,
  customer_name TEXT,
  customer_email TEXT,
  dog_name TEXT,
  storage_path TEXT,
  caption TEXT,
  source TEXT,
  status TEXT,
  human_in_frame BOOLEAN,
  contains_health_claim BOOLEAN,
  consent_website BOOLEAN,
  consent_social BOOLEAN,
  consent_ads BOOLEAN,
  rejection_reason TEXT,
  created_at TIMESTAMPTZ,
  moderated_at TIMESTAMPTZ,
  moderated_by TEXT
)

`public.moderate_dog_photo`¶

Updates moderation fields. All arguments except p_moderated_by default to NULL. COALESCE preserves existing values for NULL arguments. moderated_at and moderated_by are set only when p_status is non-null. Returns FALSE if the row is not found or is already revoked.

moderate_dog_photo(
  p_photo_id UUID,
  p_status TEXT,
  p_human_in_frame BOOLEAN,
  p_contains_health_claim BOOLEAN,
  p_rejection_reason TEXT,
  p_caption TEXT,
  p_display_order INTEGER,
  p_moderated_by TEXT DEFAULT 'ops'
) RETURNS BOOLEAN

`public.revoke_dog_photo_consent`¶

Terminal. Sets status = 'revoked', consent_revoked = TRUE, consent_revoked_at = NOW(), stores the reason. Scoped by WHERE consent_revoked = FALSE so repeated calls are safe no-ops. Logs a DOG_PHOTO event to ops_events.

revoke_dog_photo_consent(
  p_photo_id UUID,
  p_reason TEXT DEFAULT NULL
) RETURNS BOOLEAN

Edge Function: `dog-photo-upload`¶

Location: supabase/functions/dog-photo-upload/index.ts
Deployment: supabase functions deploy dog-photo-upload --no-verify-jwt
Endpoint: POST /functions/v1/dog-photo-upload
Content type: multipart/form-data

Fields¶

Field	Type	Required	Default
`photo`	File	yes	-
`customer_id`	UUID	yes	-
`dog_id`	UUID	yes	-
`dog_name`	text	yes	-
`consent_website`	bool	no	true
`consent_social`	bool	no	false
`consent_ads`	bool	no	false
`caption`	text (<=100 chars)	no	null

Validation¶

All required fields present
customer_id and dog_id parse as UUIDs
photo.type in (image/jpeg, image/png, image/webp, image/heic)
photo.size at most 10 MB

Storage¶

Bucket: dog-photos
Path: uploads/{customer_id}/{crypto.randomUUID()}.{ext}
upsert: false

Failure handling¶

If the upload succeeds but the submit_dog_photo RPC call fails, the uploaded storage object is removed to avoid orphans. The client is created with the service role key and no schema override so the public RPC is reachable.

Responses¶

Status	Body
200/201	`{ success: true, photo_id }`
400	`{ success: false, error }` for validation errors
500	`{ success: false, error }` for storage or RPC failure

Storage Bucket: `dog-photos`¶

Setting	Value
`public`	false
`file_size_limit`	10485760 (10 MB)
`allowed_mime_types`	image/jpeg, image/png, image/webp, image/heic

The Ops Portal reads thumbnails via supabase.storage.from('dog-photos').createSignedUrls(paths, 3600).

Ops Portal Integration¶

Dog Photos Tab¶

Location: Ops Portal -> Dog Photos (tab button placed after Testimonials)

Files:

Component	Location
JavaScript	`js/modules/dog-photos.js`
CSS	`css/dog-photos.css`
HTML section	`index.html` -> `#dogPhotosContent`
Tab button	`index.html` -> `data-tab="dogPhotos"`
Badge	`#dog-photos-badge` (count of `status='submitted'`)
App wiring	`js/app.js` import, modules and tabModuleMap registration, `window.opsPortal.dogPhotos`
Version	`js/config.js` bumped to 4.4

Features¶

Moderation queue sorted submitted-first, LIMIT 100
200x200 thumbnail, object-fit cover, 8px border radius
Status badge colour-coded: submitted (yellow), approved (green), featured (blue), rejected (red), revoked (grey)
Three consent pills per card (Website, Social, Ads) showing on / off state captured at submission
Action buttons: Approve, Feature, Reject (reason dropdown required), Revoke (reason prompt and terminal confirmation)
Refresh button reloads queue and updates badge

Key JavaScript Functions¶

Function	Description
`loadQueue()`	Fetch rows via `get_dog_photos_queue`
`resolveSignedUrls(paths)`	Batch-resolve storage paths to signed URLs
`renderQueue()`	Render cards for the current queue
`renderCard(row)`	Render one moderation card
`readCardFields(cardEl)`	Read caption, display_order, flags from the DOM
`moderatePhoto(id, patch)`	Call `moderate_dog_photo`
`approve(id)`	Shortcut - status `approved`
`feature(id)`	Shortcut - status `featured`
`reject(id)`	Prompt for rejection reason, call moderate with `rejected`
`revoke(id)`	Prompt for reason, confirm terminal action, call `revoke_dog_photo_consent`
`refresh()`	Reload queue and update badge
`updateBadge()`	Count submitted rows, update `#dog-photos-badge`

Rejection Reasons¶

Value	Meaning
`low_quality`	Blurry, dark, or otherwise unusable
`not_a_dog`	Subject is not a dog
`inappropriate`	Unsuitable for brand surfaces
`copyright_concern`	Likely not owned by submitter
`human_consent`	Human in frame without captured consent
`duplicate`	Already submitted
`other`	Free-form

Display Threshold (hard gate)¶

The public Dog Wall surface on the website must not render publicly until a minimum bank of approved photos exists.

Threshold: 20 approved photos.

Until the threshold is met, continue collecting and moderating, but do not publish the Dog Wall surface. A half-empty wall damages the credibility the wall exists to build.

File Locations¶

Component	Location
Database table	`raw_ops.dog_photos`
Additional column	`raw_ops.customer_health_checkpoints.photo_storage_path`
Display view	`public.v_dog_wall_display`
RPC functions	`public.submit_dog_photo`, `public.get_dog_photos_queue`, `public.moderate_dog_photo`, `public.revoke_dog_photo_consent`
Edge Function	`supabase/functions/dog-photo-upload/index.ts`
Storage bucket	`dog-photos` (private, 10 MB, image/jpeg\|png\|webp\|heic)
Ops Portal module	`js/modules/dog-photos.js`
Ops Portal styles	`css/dog-photos.css`
HTML tab	`index.html` -> `#dogPhotosContent`, `#dog-photos-badge`
App wiring	`js/app.js` (import, registration, global)
Version	`js/config.js` v4.4

Outstanding Items¶

Item	Status	Priority
Portal upload UI in Customer Portal	Not built	Phase B
Lifecycle email prompt to request portal photo (SOP-LC-01)	Not built	Phase B
Public Dog Wall surface on protocolraw.co.uk	Not built	Phase B, gated by 20-photo threshold
Support ticket photo attachment flow	Not built	Phase B
Checkpoint photo display in Ops Portal health view	Column exists, UI pending	Phase B

Version History¶

Version	Date	Changes
1.0	2026-04-15	Initial release - table, view, four RPC functions, `dog-photo-upload` Edge Function, private `dog-photos` storage bucket, Ops Portal v4.4 Dog Photos tab.

Document Owner: Protocol Raw Operations
Next Review: When Phase B portal upload flow goes live