NDARecord Django Model Design

Executive Summary

This document specifies the comprehensive technical design for the NDARecord Django model, the core data structure for managing Non-Disclosure Agreement (NDA) lifecycle at auth.coditect.ai for the BIO-QMS (Biosciences QMS Platform) NDA-gated access control system.

The NDARecord model provides:

• Immutable audit trail of all NDA signings
• Version tracking for NDA document evolution
• Cryptographic verification via document hashing
• Lifecycle management (active, revoked, expired, renewal)
• Compliance alignment with FDA 21 CFR Part 11 electronic records requirements

---

Table of Contents

1. Model Definition
2. Model Methods
3. Django Admin Configuration
4. Database Schema
5. Manager Class
6. Signal Handlers
7. Serializers
8. Testing Strategy
9. Compliance Considerations
10. Implementation Checklist

---

1. Model Definition

1.1 Core Model

File: auth_service/ndas/models.py

"""
NDA Record Model for BIO-QMS Access Control
Implements ADR-196: NDA-Gated Conditional Access
"""
import hashlib
import uuid
from datetime import timedelta
from typing import Optional

from django.conf import settings
from django.contrib.auth import get_user_model
from django.core.exceptions import ValidationError
from django.core.validators import EmailValidator
from django.db import models
from django.db.models import Q, QuerySet
from django.utils import timezone
from django.utils.translation import gettext_lazy as _

User = get_user_model()


class NDARecordManager(models.Manager):
    """Custom manager for NDARecord with business logic querysets."""

    def active(self) -> QuerySet:
        """Return all active (non-revoked, non-expired) NDAs."""
        return self.filter(
            revoked_at__isnull=True,
            signed_at__gte=timezone.now() - timedelta(days=365)
        )

    def expired(self) -> QuerySet:
        """Return NDAs that have exceeded validity period (365 days)."""
        cutoff = timezone.now() - timedelta(days=365)
        return self.filter(
            revoked_at__isnull=True,
            signed_at__lt=cutoff
        )

    def revoked(self) -> QuerySet:
        """Return all revoked NDAs."""
        return self.filter(revoked_at__isnull=False)

    def needing_renewal(self, warning_days: int = 30) -> QuerySet:
        """Return NDAs approaching expiry within warning_days."""
        cutoff = timezone.now() - timedelta(days=(365 - warning_days))
        return self.filter(
            revoked_at__isnull=True,
            signed_at__lt=cutoff,
            signed_at__gte=timezone.now() - timedelta(days=365)
        )

    def for_email(self, email: str) -> QuerySet:
        """Return all NDA records for a given email (active and historical)."""
        return self.filter(signer_email__iexact=email)

    def active_for_email(self, email: str) -> Optional['NDARecord']:
        """Return the most recent active NDA for an email, or None."""
        return self.active().filter(
            signer_email__iexact=email
        ).order_by('-signed_at').first()

    def bulk_revoke_by_company(self, company: str, reason: str, revoked_by: User) -> int:
        """Revoke all active NDAs for a company."""
        records = self.active().filter(company__iexact=company)
        count = 0
        for record in records:
            record.revoke(reason=reason, revoked_by=revoked_by)
            count += 1
        return count

    def bulk_check_renewal(self) -> dict:
        """Check renewal status across all active NDAs."""
        return {
            'active_count': self.active().count(),
            'expired_count': self.expired().count(),
            'needing_renewal_30d': self.needing_renewal(30).count(),
            'needing_renewal_60d': self.needing_renewal(60).count(),
            'needing_renewal_90d': self.needing_renewal(90).count(),
        }


class NDARecord(models.Model):
    """
    Immutable record of NDA signature for BIO-QMS access control.

    This model implements FDA 21 CFR Part 11 electronic records requirements:
    - Immutable audit trail (no UPDATE operations after creation)
    - Cryptographic document verification via SHA-256 hash
    - Detailed signing metadata (IP, user agent, timestamp)
    - E-signature data capture

    Lifecycle:
        1. CREATE: User signs NDA via frontend form
        2. ACTIVE: NDA is valid (not revoked, within 365 days)
        3. REVOKED: Admin/system revokes NDA (soft delete via revoked_at)
        4. EXPIRED: NDA exceeds 365-day validity period
        5. RENEWAL: User signs new NDA (new record created)

    Design Constraints:
        - No DELETE operations (ProtectedError via signal)
        - No UPDATE operations after creation (immutability via signals)
        - One active NDA per email at any time (enforced via custom validation)
    """

    # Primary key
    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False,
        help_text=_("Unique identifier for this NDA record")
    )

    # Signer identification
    signer_email = models.EmailField(
        max_length=255,
        db_index=True,
        validators=[EmailValidator()],
        help_text=_("Email address of the NDA signer (used for authentication)")
    )

    signer_name = models.CharField(
        max_length=255,
        help_text=_("Full name of the NDA signer")
    )

    company = models.CharField(
        max_length=255,
        blank=True,
        null=True,
        db_index=True,
        help_text=_("Company/organization name (nullable for individual signers)")
    )

    # Lifecycle timestamps
    signed_at = models.DateTimeField(
        auto_now_add=True,
        db_index=True,
        help_text=_("UTC timestamp when NDA was signed")
    )

    revoked_at = models.DateTimeField(
        null=True,
        blank=True,
        db_index=True,
        help_text=_("UTC timestamp when NDA was revoked (null if active)")
    )

    revoked_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='ndas_revoked',
        help_text=_("Admin user who revoked this NDA")
    )

    revocation_reason = models.TextField(
        blank=True,
        help_text=_("Reason for NDA revocation (required if revoked)")
    )

    # Document version tracking
    document_version = models.CharField(
        max_length=50,
        db_index=True,
        help_text=_("NDA document version (e.g., 'v1.2.0', '2024-01-15')")
    )

    nda_document_hash = models.CharField(
        max_length=64,
        help_text=_("SHA-256 hash of the NDA PDF at signing time (for verification)")
    )

    # Signing context metadata
    ip_address = models.GenericIPAddressField(
        help_text=_("IP address from which NDA was signed")
    )

    user_agent = models.TextField(
        help_text=_("Browser user agent string at signing time")
    )

    signature_data = models.JSONField(
        help_text=_(
            "E-signature capture data (coordinates, timestamp, method). "
            "Example: {'method': 'click-wrap', 'coordinates': [x, y], "
            "'timestamp': '2024-01-15T10:30:00Z', 'signature_image_url': '...'}"
        )
    )

    # Metadata
    created_at = models.DateTimeField(
        auto_now_add=True,
        help_text=_("Internal: record creation timestamp")
    )

    updated_at = models.DateTimeField(
        auto_now=True,
        help_text=_("Internal: last modification timestamp (should never change after creation)")
    )

    objects = NDARecordManager()

    class Meta:
        db_table = 'nda_records'
        verbose_name = _('NDA Record')
        verbose_name_plural = _('NDA Records')
        ordering = ['-signed_at']
        indexes = [
            models.Index(fields=['signer_email', 'signed_at']),
            models.Index(fields=['company', 'signed_at']),
            models.Index(fields=['document_version', 'signed_at']),
            models.Index(fields=['revoked_at']),
        ]
        # Enforce one active NDA per email + document version
        constraints = [
            models.UniqueConstraint(
                fields=['signer_email', 'document_version'],
                condition=Q(revoked_at__isnull=True),
                name='unique_active_nda_per_email_version'
            ),
        ]

    def __str__(self) -> str:
        status = "ACTIVE" if self.is_active() else "REVOKED" if self.revoked_at else "EXPIRED"
        return f"{self.signer_email} - {self.document_version} ({status})"

    def __repr__(self) -> str:
        return (
            f"<NDARecord id={self.id} email={self.signer_email} "
            f"version={self.document_version} signed={self.signed_at.isoformat()}>"
        )

    def clean(self):
        """Validate business rules before saving."""
        super().clean()

        # Validate signature_data structure
        if self.signature_data:
            required_keys = {'method', 'timestamp'}
            if not required_keys.issubset(self.signature_data.keys()):
                raise ValidationError({
                    'signature_data': _("Must contain 'method' and 'timestamp' keys")
                })

            valid_methods = ['click-wrap', 'typed-signature', 'drawn-signature', 'uploaded-signature']
            if self.signature_data.get('method') not in valid_methods:
                raise ValidationError({
                    'signature_data': _(f"method must be one of: {', '.join(valid_methods)}")
                })

        # Validate revocation
        if self.revoked_at and not self.revocation_reason:
            raise ValidationError({
                'revocation_reason': _("Revocation reason is required when NDA is revoked")
            })

        if self.revocation_reason and not self.revoked_at:
            raise ValidationError({
                'revoked_at': _("revoked_at timestamp is required when revocation_reason is set")
            })

    def save(self, *args, **kwargs):
        """Override save to enforce immutability after creation."""
        # If this is an update (pk exists), only allow revocation fields to change
        if self.pk:
            old_instance = NDARecord.objects.get(pk=self.pk)
            immutable_fields = [
                'signer_email', 'signer_name', 'company', 'signed_at',
                'document_version', 'nda_document_hash', 'ip_address',
                'user_agent', 'signature_data'
            ]
            for field in immutable_fields:
                if getattr(self, field) != getattr(old_instance, field):
                    raise ValidationError(
                        f"Cannot modify '{field}' after NDA record creation. "
                        "NDA records are immutable audit trail entries."
                    )

        self.full_clean()
        super().save(*args, **kwargs)

    # -------------------------------------------------------------------------
    # Model Methods (see section 2)
    # -------------------------------------------------------------------------

    def is_active(self) -> bool:
        """
        Check if NDA is currently active.

        Returns:
            True if NDA is not revoked and within 365-day validity period.
        """
        if self.revoked_at is not None:
            return False

        validity_period = timedelta(days=365)
        age = timezone.now() - self.signed_at
        return age <= validity_period

    def revoke(self, reason: str, revoked_by: User) -> None:
        """
        Revoke this NDA record.

        Args:
            reason: Human-readable explanation for revocation
            revoked_by: Admin user performing the revocation

        Raises:
            ValidationError: If NDA is already revoked
        """
        if self.revoked_at is not None:
            raise ValidationError("NDA is already revoked")

        self.revoked_at = timezone.now()
        self.revoked_by = revoked_by
        self.revocation_reason = reason
        self.save(update_fields=['revoked_at', 'revoked_by', 'revocation_reason', 'updated_at'])

        # Send signal for downstream processing
        from .signals import nda_revoked
        nda_revoked.send(sender=self.__class__, instance=self, reason=reason)

    def renewal_needed(self, warning_days: int = 30) -> bool:
        """
        Check if NDA is approaching expiry and needs renewal.

        Args:
            warning_days: Number of days before expiry to trigger renewal warning

        Returns:
            True if NDA is within warning_days of expiration
        """
        if not self.is_active():
            return False

        validity_period = timedelta(days=365)
        warning_period = validity_period - timedelta(days=warning_days)
        age = timezone.now() - self.signed_at

        return age >= warning_period

    def days_until_expiry(self) -> int:
        """
        Calculate days remaining until NDA expires.

        Returns:
            Positive integer for days remaining, negative for expired NDAs
        """
        validity_period = timedelta(days=365)
        expiry_date = self.signed_at + validity_period
        delta = expiry_date - timezone.now()
        return delta.days

    def get_audit_entries(self) -> QuerySet:
        """
        Retrieve all audit log entries for this NDA.

        Returns:
            QuerySet of NDAuditLog entries (see section 1.3)
        """
        return NDAAuditLog.objects.filter(nda_record=self).order_by('-timestamp')

    def verify_document_hash(self, pdf_bytes: bytes) -> bool:
        """
        Verify that a PDF matches the signed document hash.

        Args:
            pdf_bytes: Raw bytes of the NDA PDF

        Returns:
            True if SHA-256 hash matches stored hash
        """
        computed_hash = hashlib.sha256(pdf_bytes).hexdigest()
        return computed_hash == self.nda_document_hash

    @classmethod
    def create_from_signature(
        cls,
        signer_email: str,
        signer_name: str,
        company: Optional[str],
        document_version: str,
        nda_pdf_bytes: bytes,
        ip_address: str,
        user_agent: str,
        signature_data: dict,
    ) -> 'NDARecord':
        """
        Factory method to create NDA record from signature flow.

        Args:
            signer_email: Email of the signer
            signer_name: Full name of signer
            company: Company name (optional)
            document_version: NDA version identifier
            nda_pdf_bytes: Raw PDF bytes for hash computation
            ip_address: IP address of signer
            user_agent: Browser user agent
            signature_data: E-signature capture data

        Returns:
            Created NDARecord instance

        Raises:
            ValidationError: If active NDA already exists for this email+version
        """
        # Check for existing active NDA
        existing = cls.objects.active_for_email(signer_email)
        if existing and existing.document_version == document_version:
            raise ValidationError(
                f"Active NDA already exists for {signer_email} (version {document_version})"
            )

        # Compute document hash
        document_hash = hashlib.sha256(nda_pdf_bytes).hexdigest()

        # Create record
        record = cls.objects.create(
            signer_email=signer_email.lower().strip(),
            signer_name=signer_name.strip(),
            company=company.strip() if company else None,
            document_version=document_version,
            nda_document_hash=document_hash,
            ip_address=ip_address,
            user_agent=user_agent,
            signature_data=signature_data,
        )

        return record

    def to_dict(self) -> dict:
        """Serialize to dictionary for API responses."""
        return {
            'id': str(self.id),
            'signer_email': self.signer_email,
            'signer_name': self.signer_name,
            'company': self.company,
            'signed_at': self.signed_at.isoformat(),
            'revoked_at': self.revoked_at.isoformat() if self.revoked_at else None,
            'document_version': self.document_version,
            'is_active': self.is_active(),
            'days_until_expiry': self.days_until_expiry(),
            'renewal_needed': self.renewal_needed(),
        }

1.2 Related Model: NDADocument

File: auth_service/ndas/models.py (continued)

class NDADocument(models.Model):
    """
    Master record of NDA document versions.

    This model tracks the canonical NDA document versions available for signing.
    Each NDARecord references a document_version that should correspond to an
    NDADocument.version value.
    """

    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False
    )

    version = models.CharField(
        max_length=50,
        unique=True,
        db_index=True,
        help_text=_("Version identifier (e.g., 'v1.2.0', '2024-01-15')")
    )

    title = models.CharField(
        max_length=255,
        help_text=_("Human-readable title (e.g., 'Standard NDA for BIO-QMS')")
    )

    pdf_file = models.FileField(
        upload_to='nda_documents/',
        help_text=_("NDA PDF file")
    )

    pdf_hash = models.CharField(
        max_length=64,
        help_text=_("SHA-256 hash of the PDF file")
    )

    effective_date = models.DateField(
        help_text=_("Date from which this version is effective")
    )

    superseded_date = models.DateField(
        null=True,
        blank=True,
        help_text=_("Date when this version was superseded by a newer version")
    )

    is_active = models.BooleanField(
        default=True,
        db_index=True,
        help_text=_("Whether this version is currently available for signing")
    )

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = 'nda_documents'
        verbose_name = _('NDA Document')
        verbose_name_plural = _('NDA Documents')
        ordering = ['-effective_date']

    def __str__(self) -> str:
        status = "ACTIVE" if self.is_active else "SUPERSEDED"
        return f"NDA {self.version} - {self.title} ({status})"

    def save(self, *args, **kwargs):
        """Compute PDF hash on save if file is present."""
        if self.pdf_file:
            self.pdf_file.seek(0)
            pdf_bytes = self.pdf_file.read()
            self.pdf_hash = hashlib.sha256(pdf_bytes).hexdigest()
            self.pdf_file.seek(0)
        super().save(*args, **kwargs)

    def get_signature_count(self) -> int:
        """Return count of NDARecords signed with this version."""
        return NDARecord.objects.filter(document_version=self.version).count()

    def get_active_signature_count(self) -> int:
        """Return count of active NDARecords for this version."""
        return NDARecord.objects.active().filter(document_version=self.version).count()

1.3 Related Model: NDAAuditLog

File: auth_service/ndas/models.py (continued)

class NDAAuditLog(models.Model):
    """
    Audit log for NDA-related events.

    Provides immutable audit trail for:
    - NDA signature events
    - Revocation events
    - Renewal reminders sent
    - Access attempts with NDA check
    """

    EVENT_TYPES = [
        ('signed', 'NDA Signed'),
        ('revoked', 'NDA Revoked'),
        ('renewal_reminder_sent', 'Renewal Reminder Sent'),
        ('access_granted', 'Access Granted (NDA Valid)'),
        ('access_denied', 'Access Denied (No Valid NDA)'),
        ('document_hash_verified', 'Document Hash Verified'),
        ('document_hash_mismatch', 'Document Hash Mismatch'),
    ]

    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False
    )

    nda_record = models.ForeignKey(
        NDARecord,
        on_delete=models.CASCADE,
        related_name='audit_logs',
        null=True,
        blank=True,
        help_text=_("Associated NDA record (null for access_denied events)")
    )

    event_type = models.CharField(
        max_length=50,
        choices=EVENT_TYPES,
        db_index=True,
        help_text=_("Type of audit event")
    )

    timestamp = models.DateTimeField(
        auto_now_add=True,
        db_index=True,
        help_text=_("UTC timestamp of the event")
    )

    actor = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        help_text=_("User who triggered the event (if applicable)")
    )

    ip_address = models.GenericIPAddressField(
        null=True,
        blank=True,
        help_text=_("IP address from which event originated")
    )

    user_agent = models.TextField(
        blank=True,
        help_text=_("Browser user agent (if applicable)")
    )

    details = models.JSONField(
        default=dict,
        help_text=_("Additional event-specific details")
    )

    class Meta:
        db_table = 'nda_audit_logs'
        verbose_name = _('NDA Audit Log')
        verbose_name_plural = _('NDA Audit Logs')
        ordering = ['-timestamp']
        indexes = [
            models.Index(fields=['nda_record', 'timestamp']),
            models.Index(fields=['event_type', 'timestamp']),
        ]

    def __str__(self) -> str:
        return f"{self.event_type} - {self.timestamp.isoformat()}"

    @classmethod
    def log_event(
        cls,
        event_type: str,
        nda_record: Optional[NDARecord] = None,
        actor: Optional[User] = None,
        ip_address: Optional[str] = None,
        user_agent: Optional[str] = None,
        details: Optional[dict] = None,
    ) -> 'NDAAuditLog':
        """Factory method to create audit log entry."""
        return cls.objects.create(
            event_type=event_type,
            nda_record=nda_record,
            actor=actor,
            ip_address=ip_address,
            user_agent=user_agent or '',
            details=details or {},
        )

---

2. Model Methods

2.1 Business Logic Methods

All core methods are implemented in the model definition above. Summary:

Method	Return Type	Purpose
is_active()	bool	Check if NDA is currently valid (not revoked, within 365 days)
revoke(reason, revoked_by)	None	Soft-delete NDA with audit trail
renewal_needed(warning_days=30)	bool	Check if NDA is within warning period before expiry
days_until_expiry()	int	Calculate remaining days (negative if expired)
get_audit_entries()	QuerySet	Retrieve all audit log entries for this NDA
verify_document_hash(pdf_bytes)	bool	Verify PDF matches signed document hash
create_from_signature(...)	NDARecord	Factory method for signature flow
to_dict()	dict	Serialize for API responses

2.2 Usage Examples

Example 1: Check Active NDA for User

from ndas.models import NDARecord

def check_user_nda_status(email: str) -> dict:
    """Check NDA status for a user."""
    nda = NDARecord.objects.active_for_email(email)

    if not nda:
        return {'has_active_nda': False, 'message': 'No active NDA found'}

    return {
        'has_active_nda': True,
        'nda_version': nda.document_version,
        'signed_at': nda.signed_at,
        'days_until_expiry': nda.days_until_expiry(),
        'renewal_needed': nda.renewal_needed(),
    }

Example 2: Revoke NDA

from django.contrib.auth import get_user_model
from ndas.models import NDARecord

User = get_user_model()

def revoke_user_nda(email: str, reason: str, admin_username: str) -> None:
    """Revoke a user's active NDA."""
    nda = NDARecord.objects.active_for_email(email)
    if not nda:
        raise ValueError(f"No active NDA found for {email}")

    admin = User.objects.get(username=admin_username)
    nda.revoke(reason=reason, revoked_by=admin)

    # Audit log is automatically created via signal

Example 3: Create NDA from Signature

from ndas.models import NDARecord

def process_nda_signature(form_data: dict, request) -> NDARecord:
    """Process NDA signature form submission."""
    # Load current NDA document
    from ndas.models import NDADocument
    current_nda = NDADocument.objects.filter(is_active=True).first()

    if not current_nda:
        raise ValueError("No active NDA document available")

    # Read PDF bytes
    with current_nda.pdf_file.open('rb') as f:
        pdf_bytes = f.read()

    # Create signature record
    nda_record = NDARecord.create_from_signature(
        signer_email=form_data['email'],
        signer_name=form_data['name'],
        company=form_data.get('company'),
        document_version=current_nda.version,
        nda_pdf_bytes=pdf_bytes,
        ip_address=request.META['REMOTE_ADDR'],
        user_agent=request.META['HTTP_USER_AGENT'],
        signature_data={
            'method': 'click-wrap',
            'timestamp': timezone.now().isoformat(),
            'agreed_to_terms': form_data['agreed'],
        },
    )

    return nda_record

---

3. Django Admin Configuration

3.1 NDARecordAdmin

File: auth_service/ndas/admin.py

"""
Django Admin configuration for NDA models.
"""
from typing import Optional

from django.contrib import admin, messages
from django.db.models import Count, QuerySet
from django.http import HttpRequest, HttpResponse
from django.urls import path
from django.utils.html import format_html
from django.utils.translation import gettext_lazy as _

import csv
from datetime import datetime

from .models import NDARecord, NDADocument, NDAAuditLog


@admin.register(NDARecord)
class NDARecordAdmin(admin.ModelAdmin):
    """Admin interface for NDA records with analytics and bulk actions."""

    list_display = [
        'signer_email',
        'signer_name',
        'company',
        'document_version',
        'signed_at',
        'status_badge',
        'days_remaining',
        'audit_log_link',
    ]

    list_filter = [
        'document_version',
        'signed_at',
        'revoked_at',
        'company',
    ]

    search_fields = [
        'signer_email',
        'signer_name',
        'company',
        'revocation_reason',
    ]

    readonly_fields = [
        'id',
        'signer_email',
        'signer_name',
        'company',
        'signed_at',
        'document_version',
        'nda_document_hash',
        'ip_address',
        'user_agent',
        'signature_data',
        'created_at',
        'updated_at',
        'audit_entries_display',
    ]

    fieldsets = (
        (_('Signer Information'), {
            'fields': ('id', 'signer_email', 'signer_name', 'company')
        }),
        (_('Document Information'), {
            'fields': ('document_version', 'nda_document_hash', 'signed_at')
        }),
        (_('Signing Context'), {
            'fields': ('ip_address', 'user_agent', 'signature_data'),
            'classes': ('collapse',),
        }),
        (_('Revocation'), {
            'fields': ('revoked_at', 'revoked_by', 'revocation_reason'),
        }),
        (_('Metadata'), {
            'fields': ('created_at', 'updated_at'),
            'classes': ('collapse',),
        }),
        (_('Audit Trail'), {
            'fields': ('audit_entries_display',),
        }),
    )

    actions = [
        'bulk_revoke',
        'export_csv',
        'send_renewal_reminder',
    ]

    def get_urls(self):
        """Add custom admin views."""
        urls = super().get_urls()
        custom_urls = [
            path('analytics/', self.admin_site.admin_view(self.analytics_view), name='ndas_analytics'),
        ]
        return custom_urls + urls

    # -------------------------------------------------------------------------
    # List Display Methods
    # -------------------------------------------------------------------------

    @admin.display(description=_('Status'))
    def status_badge(self, obj: NDARecord) -> str:
        """Display color-coded status badge."""
        if obj.is_active():
            color = 'green'
            status = 'ACTIVE'
        elif obj.revoked_at:
            color = 'red'
            status = 'REVOKED'
        else:
            color = 'orange'
            status = 'EXPIRED'

        return format_html(
            '<span style="background-color: {}; color: white; padding: 3px 10px; '
            'border-radius: 3px; font-weight: bold;">{}</span>',
            color, status
        )

    @admin.display(description=_('Days Remaining'))
    def days_remaining(self, obj: NDARecord) -> str:
        """Display days until expiry."""
        days = obj.days_until_expiry()
        if days < 0:
            return format_html('<span style="color: red;">Expired {} days ago</span>', abs(days))
        elif days < 30:
            return format_html('<span style="color: orange;">{} days</span>', days)
        else:
            return format_html('<span style="color: green;">{} days</span>', days)

    @admin.display(description=_('Audit Log'))
    def audit_log_link(self, obj: NDARecord) -> str:
        """Link to audit log entries."""
        count = obj.get_audit_entries().count()
        url = f"/admin/ndas/ndaauditlog/?nda_record__id__exact={obj.id}"
        return format_html('<a href="{}">{} entries</a>', url, count)

    @admin.display(description=_('Audit Entries'))
    def audit_entries_display(self, obj: NDARecord) -> str:
        """Display recent audit entries in detail view."""
        entries = obj.get_audit_entries()[:10]
        if not entries:
            return "No audit entries"

        html = '<table style="width: 100%; border-collapse: collapse;">'
        html += '<tr style="background-color: #f0f0f0; font-weight: bold;">'
        html += '<th style="padding: 5px; border: 1px solid #ddd;">Timestamp</th>'
        html += '<th style="padding: 5px; border: 1px solid #ddd;">Event</th>'
        html += '<th style="padding: 5px; border: 1px solid #ddd;">Actor</th>'
        html += '<th style="padding: 5px; border: 1px solid #ddd;">Details</th>'
        html += '</tr>'

        for entry in entries:
            html += '<tr>'
            html += f'<td style="padding: 5px; border: 1px solid #ddd;">{entry.timestamp.strftime("%Y-%m-%d %H:%M:%S")}</td>'
            html += f'<td style="padding: 5px; border: 1px solid #ddd;">{entry.event_type}</td>'
            html += f'<td style="padding: 5px; border: 1px solid #ddd;">{entry.actor or "N/A"}</td>'
            html += f'<td style="padding: 5px; border: 1px solid #ddd;">{entry.details or {}}</td>'
            html += '</tr>'

        html += '</table>'
        return format_html(html)

    # -------------------------------------------------------------------------
    # Bulk Actions
    # -------------------------------------------------------------------------

    @admin.action(description=_('Revoke selected NDAs'))
    def bulk_revoke(self, request: HttpRequest, queryset: QuerySet) -> None:
        """Bulk revoke selected NDA records."""
        # Only revoke active NDAs
        active_ndas = queryset.filter(revoked_at__isnull=True)
        count = active_ndas.count()

        if count == 0:
            self.message_user(request, "No active NDAs selected", messages.WARNING)
            return

        # Revoke all
        reason = f"Bulk revocation by {request.user.username} at {datetime.now().isoformat()}"
        for nda in active_ndas:
            nda.revoke(reason=reason, revoked_by=request.user)

        self.message_user(
            request,
            f"Successfully revoked {count} NDA record(s)",
            messages.SUCCESS
        )

    @admin.action(description=_('Export selected NDAs to CSV'))
    def export_csv(self, request: HttpRequest, queryset: QuerySet) -> HttpResponse:
        """Export selected NDA records to CSV."""
        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = f'attachment; filename="nda_records_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv"'

        writer = csv.writer(response)
        writer.writerow([
            'ID', 'Email', 'Name', 'Company', 'Signed At', 'Revoked At',
            'Document Version', 'Status', 'Days Until Expiry', 'IP Address'
        ])

        for nda in queryset:
            status = "ACTIVE" if nda.is_active() else "REVOKED" if nda.revoked_at else "EXPIRED"
            writer.writerow([
                str(nda.id),
                nda.signer_email,
                nda.signer_name,
                nda.company or '',
                nda.signed_at.isoformat(),
                nda.revoked_at.isoformat() if nda.revoked_at else '',
                nda.document_version,
                status,
                nda.days_until_expiry(),
                nda.ip_address,
            ])

        return response

    @admin.action(description=_('Send renewal reminder to selected signers'))
    def send_renewal_reminder(self, request: HttpRequest, queryset: QuerySet) -> None:
        """Send renewal reminder emails to selected NDA signers."""
        from .tasks import send_nda_renewal_reminder  # Celery task

        # Only send to NDAs needing renewal
        needing_renewal = [nda for nda in queryset if nda.renewal_needed()]
        count = len(needing_renewal)

        if count == 0:
            self.message_user(request, "No NDAs needing renewal selected", messages.WARNING)
            return

        for nda in needing_renewal:
            send_nda_renewal_reminder.delay(str(nda.id))

        self.message_user(
            request,
            f"Queued renewal reminder emails for {count} NDA signer(s)",
            messages.SUCCESS
        )

    # -------------------------------------------------------------------------
    # Custom Admin View: Analytics
    # -------------------------------------------------------------------------

    def analytics_view(self, request: HttpRequest) -> HttpResponse:
        """Display NDA analytics dashboard."""
        from django.shortcuts import render

        # Compute statistics
        total_ndas = NDARecord.objects.count()
        active_ndas = NDARecord.objects.active().count()
        revoked_ndas = NDARecord.objects.revoked().count()
        expired_ndas = NDARecord.objects.expired().count()

        renewal_stats = NDARecord.objects.bulk_check_renewal()

        # Signings per month (last 12 months)
        from django.db.models.functions import TruncMonth
        monthly_signings = NDARecord.objects.annotate(
            month=TruncMonth('signed_at')
        ).values('month').annotate(
            count=Count('id')
        ).order_by('-month')[:12]

        # Top companies
        top_companies = NDARecord.objects.filter(
            company__isnull=False
        ).values('company').annotate(
            count=Count('id')
        ).order_by('-count')[:10]

        context = {
            'title': 'NDA Analytics',
            'total_ndas': total_ndas,
            'active_ndas': active_ndas,
            'revoked_ndas': revoked_ndas,
            'expired_ndas': expired_ndas,
            'renewal_stats': renewal_stats,
            'monthly_signings': monthly_signings,
            'top_companies': top_companies,
        }

        return render(request, 'admin/ndas/analytics.html', context)


@admin.register(NDADocument)
class NDADocumentAdmin(admin.ModelAdmin):
    """Admin interface for NDA document versions."""

    list_display = [
        'version',
        'title',
        'effective_date',
        'is_active',
        'signature_count',
        'active_signature_count',
    ]

    list_filter = ['is_active', 'effective_date', 'superseded_date']
    search_fields = ['version', 'title']
    readonly_fields = ['id', 'pdf_hash', 'created_at', 'updated_at']

    fieldsets = (
        (_('Document Information'), {
            'fields': ('id', 'version', 'title', 'pdf_file', 'pdf_hash')
        }),
        (_('Lifecycle'), {
            'fields': ('effective_date', 'superseded_date', 'is_active')
        }),
        (_('Metadata'), {
            'fields': ('created_at', 'updated_at'),
            'classes': ('collapse',),
        }),
    )

    @admin.display(description=_('Total Signatures'))
    def signature_count(self, obj: NDADocument) -> int:
        """Total signatures for this version."""
        return obj.get_signature_count()

    @admin.display(description=_('Active Signatures'))
    def active_signature_count(self, obj: NDADocument) -> int:
        """Active signatures for this version."""
        return obj.get_active_signature_count()


@admin.register(NDAAuditLog)
class NDAAuditLogAdmin(admin.ModelAdmin):
    """Admin interface for NDA audit logs."""

    list_display = [
        'timestamp',
        'event_type',
        'nda_record',
        'actor',
        'ip_address',
    ]

    list_filter = ['event_type', 'timestamp']
    search_fields = ['nda_record__signer_email', 'actor__username', 'ip_address']
    readonly_fields = '__all__'

    def has_add_permission(self, request: HttpRequest) -> bool:
        """Prevent manual creation of audit logs."""
        return False

    def has_delete_permission(self, request: HttpRequest, obj: Optional[NDAAuditLog] = None) -> bool:
        """Prevent deletion of audit logs."""
        return False

3.2 Admin Template: Analytics

File: auth_service/ndas/templates/admin/ndas/analytics.html

{% extends "admin/base_site.html" %}
{% load i18n static %}

{% block content %}
<div style="padding: 20px;">
    <h1>{% trans "NDA Analytics Dashboard" %}</h1>

    <div style="display: grid; grid-template-columns: repeat(4, 1fr); gap: 20px; margin: 20px 0;">
        <div style="background: #f0f0f0; padding: 20px; border-radius: 5px;">
            <h3>{% trans "Total NDAs" %}</h3>
            <p style="font-size: 36px; font-weight: bold; margin: 10px 0;">{{ total_ndas }}</p>
        </div>
        <div style="background: #d4edda; padding: 20px; border-radius: 5px;">
            <h3>{% trans "Active NDAs" %}</h3>
            <p style="font-size: 36px; font-weight: bold; margin: 10px 0; color: green;">{{ active_ndas }}</p>
        </div>
        <div style="background: #f8d7da; padding: 20px; border-radius: 5px;">
            <h3>{% trans "Revoked NDAs" %}</h3>
            <p style="font-size: 36px; font-weight: bold; margin: 10px 0; color: red;">{{ revoked_ndas }}</p>
        </div>
        <div style="background: #fff3cd; padding: 20px; border-radius: 5px;">
            <h3>{% trans "Expired NDAs" %}</h3>
            <p style="font-size: 36px; font-weight: bold; margin: 10px 0; color: orange;">{{ expired_ndas }}</p>
        </div>
    </div>

    <h2>{% trans "Renewal Status" %}</h2>
    <table style="width: 100%; border-collapse: collapse; margin: 20px 0;">
        <tr style="background: #f0f0f0;">
            <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">{% trans "Status" %}</th>
            <th style="padding: 10px; border: 1px solid #ddd; text-align: right;">{% trans "Count" %}</th>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #ddd;">{% trans "Needing Renewal (30 days)" %}</td>
            <td style="padding: 10px; border: 1px solid #ddd; text-align: right;">{{ renewal_stats.needing_renewal_30d }}</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #ddd;">{% trans "Needing Renewal (60 days)" %}</td>
            <td style="padding: 10px; border: 1px solid #ddd; text-align: right;">{{ renewal_stats.needing_renewal_60d }}</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #ddd;">{% trans "Needing Renewal (90 days)" %}</td>
            <td style="padding: 10px; border: 1px solid #ddd; text-align: right;">{{ renewal_stats.needing_renewal_90d }}</td>
        </tr>
    </table>

    <h2>{% trans "Monthly Signings (Last 12 Months)" %}</h2>
    <table style="width: 100%; border-collapse: collapse; margin: 20px 0;">
        <tr style="background: #f0f0f0;">
            <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">{% trans "Month" %}</th>
            <th style="padding: 10px; border: 1px solid #ddd; text-align: right;">{% trans "Signings" %}</th>
        </tr>
        {% for entry in monthly_signings %}
        <tr>
            <td style="padding: 10px; border: 1px solid #ddd;">{{ entry.month|date:"F Y" }}</td>
            <td style="padding: 10px; border: 1px solid #ddd; text-align: right;">{{ entry.count }}</td>
        </tr>
        {% endfor %}
    </table>

    <h2>{% trans "Top Companies (by NDA Count)" %}</h2>
    <table style="width: 100%; border-collapse: collapse; margin: 20px 0;">
        <tr style="background: #f0f0f0;">
            <th style="padding: 10px; border: 1px solid #ddd; text-align: left;">{% trans "Company" %}</th>
            <th style="padding: 10px; border: 1px solid #ddd; text-align: right;">{% trans "NDA Count" %}</th>
        </tr>
        {% for company in top_companies %}
        <tr>
            <td style="padding: 10px; border: 1px solid #ddd;">{{ company.company }}</td>
            <td style="padding: 10px; border: 1px solid #ddd; text-align: right;">{{ company.count }}</td>
        </tr>
        {% endfor %}
    </table>
</div>
{% endblock %}

---

4. Database Schema

4.1 Table Definitions (SQL)

PostgreSQL Schema:

-- ============================================================================
-- NDA Records Table
-- ============================================================================
CREATE TABLE nda_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Signer identification
    signer_email VARCHAR(255) NOT NULL,
    signer_name VARCHAR(255) NOT NULL,
    company VARCHAR(255),

    -- Lifecycle timestamps
    signed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMPTZ,
    revoked_by_id INTEGER REFERENCES auth_user(id) ON DELETE SET NULL,
    revocation_reason TEXT DEFAULT '',

    -- Document version tracking
    document_version VARCHAR(50) NOT NULL,
    nda_document_hash VARCHAR(64) NOT NULL,

    -- Signing context metadata
    ip_address INET NOT NULL,
    user_agent TEXT NOT NULL,
    signature_data JSONB NOT NULL,

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

-- Indexes
CREATE INDEX idx_nda_records_signer_email ON nda_records(signer_email);
CREATE INDEX idx_nda_records_company ON nda_records(company);
CREATE INDEX idx_nda_records_signed_at ON nda_records(signed_at);
CREATE INDEX idx_nda_records_revoked_at ON nda_records(revoked_at);
CREATE INDEX idx_nda_records_document_version ON nda_records(document_version);
CREATE INDEX idx_nda_records_email_signed ON nda_records(signer_email, signed_at);
CREATE INDEX idx_nda_records_company_signed ON nda_records(company, signed_at);
CREATE INDEX idx_nda_records_version_signed ON nda_records(document_version, signed_at);

-- Constraint: one active NDA per email + version
CREATE UNIQUE INDEX unique_active_nda_per_email_version
ON nda_records(signer_email, document_version)
WHERE revoked_at IS NULL;

-- Comments
COMMENT ON TABLE nda_records IS 'Immutable audit trail of NDA signatures for BIO-QMS access control (ADR-196)';
COMMENT ON COLUMN nda_records.signer_email IS 'Email address used for authentication (indexed)';
COMMENT ON COLUMN nda_records.nda_document_hash IS 'SHA-256 hash of PDF at signing time for verification';
COMMENT ON COLUMN nda_records.signature_data IS 'E-signature capture data (method, coordinates, timestamp)';

-- ============================================================================
-- NDA Documents Table
-- ============================================================================
CREATE TABLE nda_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    pdf_file VARCHAR(255) NOT NULL, -- Django FileField path
    pdf_hash VARCHAR(64) NOT NULL,
    effective_date DATE NOT NULL,
    superseded_date DATE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_nda_documents_version ON nda_documents(version);
CREATE INDEX idx_nda_documents_is_active ON nda_documents(is_active);

COMMENT ON TABLE nda_documents IS 'Master catalog of NDA document versions available for signing';

-- ============================================================================
-- NDA Audit Logs Table
-- ============================================================================
CREATE TABLE nda_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nda_record_id UUID REFERENCES nda_records(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    actor_id INTEGER REFERENCES auth_user(id) ON DELETE SET NULL,
    ip_address INET,
    user_agent TEXT DEFAULT '',
    details JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX idx_nda_audit_logs_nda_record ON nda_audit_logs(nda_record_id, timestamp);
CREATE INDEX idx_nda_audit_logs_event_type ON nda_audit_logs(event_type, timestamp);

COMMENT ON TABLE nda_audit_logs IS 'Immutable audit trail for NDA-related events';

-- ============================================================================
-- Trigger: Prevent NDA Record Modification
-- ============================================================================
CREATE OR REPLACE FUNCTION prevent_nda_record_modification()
RETURNS TRIGGER AS $$
BEGIN
    -- Allow updates only to revocation fields
    IF OLD.signer_email != NEW.signer_email OR
       OLD.signer_name != NEW.signer_name OR
       OLD.company IS DISTINCT FROM NEW.company OR
       OLD.signed_at != NEW.signed_at OR
       OLD.document_version != NEW.document_version OR
       OLD.nda_document_hash != NEW.nda_document_hash OR
       OLD.ip_address != NEW.ip_address OR
       OLD.user_agent != NEW.user_agent OR
       OLD.signature_data::text != NEW.signature_data::text THEN
        RAISE EXCEPTION 'NDA records are immutable. Cannot modify core fields after creation.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_nda_immutability
BEFORE UPDATE ON nda_records
FOR EACH ROW
EXECUTE FUNCTION prevent_nda_record_modification();

COMMENT ON FUNCTION prevent_nda_record_modification() IS 'Enforce immutability of NDA records (FDA 21 CFR Part 11 compliance)';

4.2 Django Migration

File: auth_service/ndas/migrations/0001_initial.py

# Generated by Django 5.1 on 2026-02-16

from django.conf import settings
from django.db import migrations, models
import django.db.models.deletion
import uuid


class Migration(migrations.Migration):

    initial = True

    dependencies = [
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        migrations.CreateModel(
            name='NDADocument',
            fields=[
                ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)),
                ('version', models.CharField(db_index=True, help_text="Version identifier (e.g., 'v1.2.0', '2024-01-15')", max_length=50, unique=True)),
                ('title', models.CharField(help_text="Human-readable title (e.g., 'Standard NDA for BIO-QMS')", max_length=255)),
                ('pdf_file', models.FileField(help_text='NDA PDF file', upload_to='nda_documents/')),
                ('pdf_hash', models.CharField(help_text='SHA-256 hash of the PDF file', max_length=64)),
                ('effective_date', models.DateField(help_text='Date from which this version is effective')),
                ('superseded_date', models.DateField(blank=True, help_text='Date when this version was superseded by a newer version', null=True)),
                ('is_active', models.BooleanField(db_index=True, default=True, help_text='Whether this version is currently available for signing')),
                ('created_at', models.DateTimeField(auto_now_add=True)),
                ('updated_at', models.DateTimeField(auto_now=True)),
            ],
            options={
                'verbose_name': 'NDA Document',
                'verbose_name_plural': 'NDA Documents',
                'db_table': 'nda_documents',
                'ordering': ['-effective_date'],
            },
        ),
        migrations.CreateModel(
            name='NDARecord',
            fields=[
                ('id', models.UUIDField(default=uuid.uuid4, editable=False, help_text='Unique identifier for this NDA record', primary_key=True, serialize=False)),
                ('signer_email', models.EmailField(db_index=True, help_text='Email address of the NDA signer (used for authentication)', max_length=255)),
                ('signer_name', models.CharField(help_text='Full name of the NDA signer', max_length=255)),
                ('company', models.CharField(blank=True, db_index=True, help_text='Company/organization name (nullable for individual signers)', max_length=255, null=True)),
                ('signed_at', models.DateTimeField(auto_now_add=True, db_index=True, help_text='UTC timestamp when NDA was signed')),
                ('revoked_at', models.DateTimeField(blank=True, db_index=True, help_text='UTC timestamp when NDA was revoked (null if active)', null=True)),
                ('revocation_reason', models.TextField(blank=True, help_text='Reason for NDA revocation (required if revoked)')),
                ('document_version', models.CharField(db_index=True, help_text="NDA document version (e.g., 'v1.2.0', '2024-01-15')", max_length=50)),
                ('nda_document_hash', models.CharField(help_text='SHA-256 hash of the NDA PDF at signing time (for verification)', max_length=64)),
                ('ip_address', models.GenericIPAddressField(help_text='IP address from which NDA was signed')),
                ('user_agent', models.TextField(help_text='Browser user agent string at signing time')),
                ('signature_data', models.JSONField(help_text="E-signature capture data (coordinates, timestamp, method). Example: {'method': 'click-wrap', 'coordinates': [x, y], 'timestamp': '2024-01-15T10:30:00Z', 'signature_image_url': '...'}")),
                ('created_at', models.DateTimeField(auto_now_add=True, help_text='Internal: record creation timestamp')),
                ('updated_at', models.DateTimeField(auto_now=True, help_text='Internal: last modification timestamp (should never change after creation)')),
                ('revoked_by', models.ForeignKey(blank=True, help_text='Admin user who revoked this NDA', null=True, on_delete=django.db.models.deletion.SET_NULL, related_name='ndas_revoked', to=settings.AUTH_USER_MODEL)),
            ],
            options={
                'verbose_name': 'NDA Record',
                'verbose_name_plural': 'NDA Records',
                'db_table': 'nda_records',
                'ordering': ['-signed_at'],
            },
        ),
        migrations.CreateModel(
            name='NDAAuditLog',
            fields=[
                ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)),
                ('event_type', models.CharField(choices=[('signed', 'NDA Signed'), ('revoked', 'NDA Revoked'), ('renewal_reminder_sent', 'Renewal Reminder Sent'), ('access_granted', 'Access Granted (NDA Valid)'), ('access_denied', 'Access Denied (No Valid NDA)'), ('document_hash_verified', 'Document Hash Verified'), ('document_hash_mismatch', 'Document Hash Mismatch')], db_index=True, help_text='Type of audit event', max_length=50)),
                ('timestamp', models.DateTimeField(auto_now_add=True, db_index=True, help_text='UTC timestamp of the event')),
                ('ip_address', models.GenericIPAddressField(blank=True, help_text='IP address from which event originated', null=True)),
                ('user_agent', models.TextField(blank=True, help_text='Browser user agent (if applicable)')),
                ('details', models.JSONField(default=dict, help_text='Additional event-specific details')),
                ('actor', models.ForeignKey(blank=True, help_text='User who triggered the event (if applicable)', null=True, on_delete=django.db.models.deletion.SET_NULL, to=settings.AUTH_USER_MODEL)),
                ('nda_record', models.ForeignKey(blank=True, help_text='Associated NDA record (null for access_denied events)', null=True, on_delete=django.db.models.deletion.CASCADE, related_name='audit_logs', to='ndas.ndarecord')),
            ],
            options={
                'verbose_name': 'NDA Audit Log',
                'verbose_name_plural': 'NDA Audit Logs',
                'db_table': 'nda_audit_logs',
                'ordering': ['-timestamp'],
            },
        ),
        migrations.AddIndex(
            model_name='ndarecord',
            index=models.Index(fields=['signer_email', 'signed_at'], name='nda_records_signer__idx'),
        ),
        migrations.AddIndex(
            model_name='ndarecord',
            index=models.Index(fields=['company', 'signed_at'], name='nda_records_company_idx'),
        ),
        migrations.AddIndex(
            model_name='ndarecord',
            index=models.Index(fields=['document_version', 'signed_at'], name='nda_records_documen_idx'),
        ),
        migrations.AddIndex(
            model_name='ndarecord',
            index=models.Index(fields=['revoked_at'], name='nda_records_revoked_idx'),
        ),
        migrations.AddConstraint(
            model_name='ndarecord',
            constraint=models.UniqueConstraint(condition=models.Q(('revoked_at__isnull', True)), fields=('signer_email', 'document_version'), name='unique_active_nda_per_email_version'),
        ),
        migrations.AddIndex(
            model_name='ndaauditlog',
            index=models.Index(fields=['nda_record', 'timestamp'], name='nda_audit_l_nda_rec_idx'),
        ),
        migrations.AddIndex(
            model_name='ndaauditlog',
            index=models.Index(fields=['event_type', 'timestamp'], name='nda_audit_l_event_t_idx'),
        ),
    ]

4.3 Data Dictionary

Table	Column	Type	Nullable	Indexed	Description
nda_records	id	UUID	No	PK	Unique identifier
	signer_email	VARCHAR(255)	No	Yes	Email for authentication
	signer_name	VARCHAR(255)	No	No	Full name
	company	VARCHAR(255)	Yes	Yes	Organization name
	signed_at	TIMESTAMPTZ	No	Yes	Signing timestamp
	revoked_at	TIMESTAMPTZ	Yes	Yes	Revocation timestamp
	revoked_by_id	INTEGER (FK)	Yes	No	Admin who revoked
	revocation_reason	TEXT	Yes	No	Revocation explanation
	document_version	VARCHAR(50)	No	Yes	NDA version ID
	nda_document_hash	VARCHAR(64)	No	No	SHA-256 of PDF
	ip_address	INET	No	No	Signing IP
	user_agent	TEXT	No	No	Browser UA
	signature_data	JSONB	No	No	E-sig metadata
	created_at	TIMESTAMPTZ	No	No	Record creation
	updated_at	TIMESTAMPTZ	No	No	Last modification

---

5. Manager Class

The NDARecordManager class is implemented in section 1.1 above. Key methods:

5.1 Custom QuerySets

Method	Returns	Description
active()	QuerySet	All non-revoked NDAs within 365 days
expired()	QuerySet	NDAs beyond 365-day validity
revoked()	QuerySet	All revoked NDAs
needing_renewal(warning_days=30)	QuerySet	NDAs within warning period
for_email(email)	QuerySet	All NDAs for an email (active + historical)
active_for_email(email)	NDARecord or None	Most recent active NDA for email

5.2 Bulk Operations

Method	Returns	Description
bulk_revoke_by_company(company, reason, revoked_by)	int	Revoke all active NDAs for a company, return count
bulk_check_renewal()	dict	Statistics for renewal status across all NDAs

5.3 Usage Examples

Example 1: Query Active NDAs Needing Renewal

from ndas.models import NDARecord

# Get all NDAs expiring within 30 days
expiring_soon = NDARecord.objects.needing_renewal(warning_days=30)

for nda in expiring_soon:
    print(f"{nda.signer_email} - {nda.days_until_expiry()} days remaining")

Example 2: Bulk Company Revocation

from django.contrib.auth import get_user_model
from ndas.models import NDARecord

User = get_user_model()
admin = User.objects.get(username='admin')

# Revoke all NDAs for "Acme Corp"
count = NDARecord.objects.bulk_revoke_by_company(
    company="Acme Corp",
    reason="Company partnership terminated",
    revoked_by=admin
)

print(f"Revoked {count} NDAs for Acme Corp")

Example 3: Renewal Statistics

from ndas.models import NDARecord

stats = NDARecord.objects.bulk_check_renewal()
print(stats)
# Output:
# {
#     'active_count': 150,
#     'expired_count': 42,
#     'needing_renewal_30d': 8,
#     'needing_renewal_60d': 23,
#     'needing_renewal_90d': 45,
# }

---

6. Signal Handlers

6.1 Signal Definitions

File: auth_service/ndas/signals.py

"""
Django signals for NDA lifecycle events.
"""
import django.dispatch
from django.db.models.signals import post_save, pre_delete
from django.dispatch import receiver
from django.core.exceptions import PermissionDenied

from .models import NDARecord, NDAAuditLog

# Custom signal: fired when an NDA is revoked
nda_revoked = django.dispatch.Signal()


@receiver(post_save, sender=NDARecord)
def nda_record_post_save(sender, instance, created, **kwargs):
    """
    Handle NDA record creation and updates.

    Actions:
    - On CREATE: Send confirmation email + create audit log
    - On UPDATE (revocation): Create audit log
    """
    if created:
        # Log signing event
        NDAAuditLog.log_event(
            event_type='signed',
            nda_record=instance,
            ip_address=instance.ip_address,
            user_agent=instance.user_agent,
            details={
                'document_version': instance.document_version,
                'company': instance.company,
            }
        )

        # Send confirmation email (async via Celery)
        from .tasks import send_nda_confirmation_email
        send_nda_confirmation_email.delay(str(instance.id))

    elif instance.revoked_at:
        # Log revocation event (if not already logged)
        existing_log = NDAAuditLog.objects.filter(
            nda_record=instance,
            event_type='revoked'
        ).exists()

        if not existing_log:
            NDAAuditLog.log_event(
                event_type='revoked',
                nda_record=instance,
                actor=instance.revoked_by,
                details={
                    'reason': instance.revocation_reason,
                    'revoked_at': instance.revoked_at.isoformat(),
                }
            )


@receiver(pre_delete, sender=NDARecord)
def prevent_nda_deletion(sender, instance, **kwargs):
    """
    Prevent deletion of NDA records.

    NDA records are immutable audit trail entries and must never be deleted.
    Use revocation for lifecycle management.
    """
    raise PermissionDenied(
        "NDA records cannot be deleted. They are immutable audit trail entries. "
        "Use the revoke() method to mark an NDA as revoked."
    )


@receiver(nda_revoked)
def handle_nda_revocation(sender, instance, reason, **kwargs):
    """
    Handle downstream effects of NDA revocation.

    Actions:
    - Invalidate related DocumentViewTokens
    - Send notification email to signer
    - Log access control event
    """
    # Invalidate any active document view tokens for this signer
    from document_access.models import DocumentViewToken
    tokens = DocumentViewToken.objects.filter(
        email=instance.signer_email,
        is_active=True
    )
    tokens.update(is_active=False, revoked_reason=f"NDA revoked: {reason}")

    # Send revocation notification email (async)
    from .tasks import send_nda_revocation_email
    send_nda_revocation_email.delay(str(instance.id))

    # Log access control event
    NDAAuditLog.log_event(
        event_type='access_denied',
        nda_record=instance,
        details={
            'reason': 'NDA revoked',
            'revocation_reason': reason,
        }
    )

6.2 Signal Registration

File: auth_service/ndas/apps.py

from django.apps import AppConfig


class NdasConfig(AppConfig):
    default_auto_field = 'django.db.models.BigAutoField'
    name = 'ndas'
    verbose_name = 'NDA Management'

    def ready(self):
        """Import signals when app is ready."""
        import ndas.signals  # noqa

File: auth_service/ndas/__init__.py

default_app_config = 'ndas.apps.NdasConfig'

---

7. Serializers

7.1 Django REST Framework Serializers

File: auth_service/ndas/serializers.py

"""
DRF Serializers for NDA models.
"""
from rest_framework import serializers
from django.contrib.auth import get_user_model

from .models import NDARecord, NDADocument, NDAAuditLog

User = get_user_model()


class NDARecordSerializer(serializers.ModelSerializer):
    """
    Read-only serializer for NDA records (API responses).
    """
    is_active = serializers.SerializerMethodField()
    days_until_expiry = serializers.SerializerMethodField()
    renewal_needed = serializers.SerializerMethodField()
    status = serializers.SerializerMethodField()

    class Meta:
        model = NDARecord
        fields = [
            'id',
            'signer_email',
            'signer_name',
            'company',
            'signed_at',
            'revoked_at',
            'document_version',
            'is_active',
            'status',
            'days_until_expiry',
            'renewal_needed',
        ]
        read_only_fields = '__all__'

    def get_is_active(self, obj: NDARecord) -> bool:
        return obj.is_active()

    def get_days_until_expiry(self, obj: NDARecord) -> int:
        return obj.days_until_expiry()

    def get_renewal_needed(self, obj: NDARecord) -> bool:
        return obj.renewal_needed()

    def get_status(self, obj: NDARecord) -> str:
        if obj.is_active():
            return 'ACTIVE'
        elif obj.revoked_at:
            return 'REVOKED'
        else:
            return 'EXPIRED'


class NDARecordDetailSerializer(serializers.ModelSerializer):
    """
    Detailed serializer with full metadata (for admin/audit views).
    """
    is_active = serializers.SerializerMethodField()
    days_until_expiry = serializers.SerializerMethodField()
    renewal_needed = serializers.SerializerMethodField()
    status = serializers.SerializerMethodField()
    revoked_by_username = serializers.SerializerMethodField()
    audit_log_count = serializers.SerializerMethodField()

    class Meta:
        model = NDARecord
        fields = [
            'id',
            'signer_email',
            'signer_name',
            'company',
            'signed_at',
            'revoked_at',
            'revoked_by_username',
            'revocation_reason',
            'document_version',
            'nda_document_hash',
            'ip_address',
            'user_agent',
            'signature_data',
            'is_active',
            'status',
            'days_until_expiry',
            'renewal_needed',
            'audit_log_count',
            'created_at',
            'updated_at',
        ]
        read_only_fields = '__all__'

    def get_is_active(self, obj: NDARecord) -> bool:
        return obj.is_active()

    def get_days_until_expiry(self, obj: NDARecord) -> int:
        return obj.days_until_expiry()

    def get_renewal_needed(self, obj: NDARecord) -> bool:
        return obj.renewal_needed()

    def get_status(self, obj: NDARecord) -> str:
        if obj.is_active():
            return 'ACTIVE'
        elif obj.revoked_at:
            return 'REVOKED'
        else:
            return 'EXPIRED'

    def get_revoked_by_username(self, obj: NDARecord) -> str:
        return obj.revoked_by.username if obj.revoked_by else None

    def get_audit_log_count(self, obj: NDARecord) -> int:
        return obj.get_audit_entries().count()


class NDARecordCreateSerializer(serializers.Serializer):
    """
    Write-only serializer for NDA signature flow.
    """
    signer_email = serializers.EmailField(required=True)
    signer_name = serializers.CharField(max_length=255, required=True)
    company = serializers.CharField(max_length=255, required=False, allow_blank=True)
    agreed = serializers.BooleanField(required=True)
    signature_method = serializers.ChoiceField(
        choices=['click-wrap', 'typed-signature', 'drawn-signature', 'uploaded-signature'],
        required=True
    )
    signature_image = serializers.ImageField(required=False, allow_null=True)

    def validate_agreed(self, value):
        """Ensure user has agreed to terms."""
        if not value:
            raise serializers.ValidationError("You must agree to the NDA terms")
        return value

    def validate_signer_email(self, value):
        """Check for existing active NDA."""
        from .models import NDARecord
        existing = NDARecord.objects.active_for_email(value)
        if existing:
            raise serializers.ValidationError(
                f"An active NDA already exists for this email (signed {existing.signed_at.date()})"
            )
        return value.lower().strip()

    def create(self, validated_data):
        """Create NDA record from validated signature data."""
        from .models import NDARecord, NDADocument

        # Get current active NDA document
        current_nda = NDADocument.objects.filter(is_active=True).first()
        if not current_nda:
            raise serializers.ValidationError("No active NDA document available")

        # Read PDF bytes for hash computation
        with current_nda.pdf_file.open('rb') as f:
            pdf_bytes = f.read()

        # Build signature data
        signature_data = {
            'method': validated_data['signature_method'],
            'timestamp': timezone.now().isoformat(),
            'agreed': validated_data['agreed'],
        }

        if validated_data.get('signature_image'):
            # Upload signature image and store URL
            from django.core.files.storage import default_storage
            image_path = default_storage.save(
                f"nda_signatures/{validated_data['signer_email']}/{timezone.now().strftime('%Y%m%d_%H%M%S')}.png",
                validated_data['signature_image']
            )
            signature_data['signature_image_url'] = default_storage.url(image_path)

        # Extract request metadata from context
        request = self.context['request']
        ip_address = request.META.get('REMOTE_ADDR', '0.0.0.0')
        user_agent = request.META.get('HTTP_USER_AGENT', '')

        # Create NDA record
        nda_record = NDARecord.create_from_signature(
            signer_email=validated_data['signer_email'],
            signer_name=validated_data['signer_name'],
            company=validated_data.get('company'),
            document_version=current_nda.version,
            nda_pdf_bytes=pdf_bytes,
            ip_address=ip_address,
            user_agent=user_agent,
            signature_data=signature_data,
        )

        return nda_record


class NDADocumentSerializer(serializers.ModelSerializer):
    """Serializer for NDA document versions."""
    signature_count = serializers.SerializerMethodField()
    active_signature_count = serializers.SerializerMethodField()

    class Meta:
        model = NDADocument
        fields = [
            'id',
            'version',
            'title',
            'pdf_file',
            'pdf_hash',
            'effective_date',
            'superseded_date',
            'is_active',
            'signature_count',
            'active_signature_count',
        ]
        read_only_fields = ['id', 'pdf_hash', 'signature_count', 'active_signature_count']

    def get_signature_count(self, obj: NDADocument) -> int:
        return obj.get_signature_count()

    def get_active_signature_count(self, obj: NDADocument) -> int:
        return obj.get_active_signature_count()


class NDAAuditLogSerializer(serializers.ModelSerializer):
    """Serializer for audit log entries."""
    nda_signer_email = serializers.SerializerMethodField()
    actor_username = serializers.SerializerMethodField()

    class Meta:
        model = NDAAuditLog
        fields = [
            'id',
            'nda_record',
            'nda_signer_email',
            'event_type',
            'timestamp',
            'actor',
            'actor_username',
            'ip_address',
            'user_agent',
            'details',
        ]
        read_only_fields = '__all__'

    def get_nda_signer_email(self, obj: NDAAuditLog) -> str:
        return obj.nda_record.signer_email if obj.nda_record else None

    def get_actor_username(self, obj: NDAAuditLog) -> str:
        return obj.actor.username if obj.actor else None

7.2 API ViewSets

File: auth_service/ndas/views.py

"""
DRF ViewSets for NDA API.
"""
from rest_framework import viewsets, status, permissions
from rest_framework.decorators import action
from rest_framework.response import Response
from django.utils import timezone

from .models import NDARecord, NDADocument, NDAAuditLog
from .serializers import (
    NDARecordSerializer,
    NDARecordDetailSerializer,
    NDARecordCreateSerializer,
    NDADocumentSerializer,
    NDAAuditLogSerializer,
)


class NDARecordViewSet(viewsets.ReadOnlyModelViewSet):
    """
    ViewSet for NDA records.

    Endpoints:
    - GET /api/ndas/ - List all NDAs (admin only)
    - GET /api/ndas/{id}/ - Retrieve NDA detail
    - POST /api/ndas/sign/ - Sign a new NDA (public)
    - GET /api/ndas/check/{email}/ - Check NDA status for email (public)
    - POST /api/ndas/{id}/revoke/ - Revoke an NDA (admin only)
    """
    queryset = NDARecord.objects.all()
    permission_classes = [permissions.IsAdminUser]

    def get_serializer_class(self):
        if self.action == 'retrieve':
            return NDARecordDetailSerializer
        elif self.action == 'sign':
            return NDARecordCreateSerializer
        return NDARecordSerializer

    def get_permissions(self):
        """Allow public access to sign and check endpoints."""
        if self.action in ['sign', 'check']:
            return [permissions.AllowAny()]
        return super().get_permissions()

    @action(detail=False, methods=['post'], url_path='sign')
    def sign(self, request):
        """
        Sign a new NDA.

        Request body:
        {
            "signer_email": "user@example.com",
            "signer_name": "John Doe",
            "company": "Acme Corp",
            "agreed": true,
            "signature_method": "click-wrap"
        }
        """
        serializer = self.get_serializer(data=request.data, context={'request': request})
        serializer.is_valid(raise_exception=True)
        nda_record = serializer.save()

        return Response(
            NDARecordSerializer(nda_record).data,
            status=status.HTTP_201_CREATED
        )

    @action(detail=False, methods=['get'], url_path='check/(?P<email>[^/.]+)')
    def check(self, request, email=None):
        """
        Check NDA status for an email.

        Returns:
        {
            "has_active_nda": true/false,
            "nda": {...} or null
        }
        """
        nda = NDARecord.objects.active_for_email(email)

        return Response({
            'has_active_nda': nda is not None,
            'nda': NDARecordSerializer(nda).data if nda else None,
        })

    @action(detail=True, methods=['post'], url_path='revoke')
    def revoke(self, request, pk=None):
        """
        Revoke an NDA.

        Request body:
        {
            "reason": "User request"
        }
        """
        nda = self.get_object()
        reason = request.data.get('reason', 'Revoked by admin')

        try:
            nda.revoke(reason=reason, revoked_by=request.user)
        except ValidationError as e:
            return Response({'error': str(e)}, status=status.HTTP_400_BAD_REQUEST)

        return Response(
            NDARecordDetailSerializer(nda).data,
            status=status.HTTP_200_OK
        )


class NDADocumentViewSet(viewsets.ReadOnlyModelViewSet):
    """ViewSet for NDA documents."""
    queryset = NDADocument.objects.all()
    serializer_class = NDADocumentSerializer
    permission_classes = [permissions.AllowAny]

    @action(detail=False, methods=['get'], url_path='current')
    def current(self, request):
        """Get the current active NDA document."""
        current_nda = NDADocument.objects.filter(is_active=True).first()
        if not current_nda:
            return Response({'error': 'No active NDA document'}, status=status.HTTP_404_NOT_FOUND)

        return Response(self.get_serializer(current_nda).data)


class NDAAuditLogViewSet(viewsets.ReadOnlyModelViewSet):
    """ViewSet for audit logs (admin only)."""
    queryset = NDAAuditLog.objects.all()
    serializer_class = NDAAuditLogSerializer
    permission_classes = [permissions.IsAdminUser]
    filterset_fields = ['event_type', 'nda_record']

---

8. Testing Strategy

8.1 Model Unit Tests

File: auth_service/ndas/tests/test_models.py

"""
Unit tests for NDA models.
"""
from datetime import timedelta
from django.test import TestCase
from django.contrib.auth import get_user_model
from django.core.exceptions import ValidationError
from django.utils import timezone

from ndas.models import NDARecord, NDADocument, NDAAuditLog
from ndas.tests.factories import NDARecordFactory, NDADocumentFactory

User = get_user_model()


class NDARecordModelTestCase(TestCase):
    """Test NDARecord model methods and lifecycle."""

    def setUp(self):
        self.admin = User.objects.create_user(username='admin', is_staff=True)
        self.nda_document = NDADocumentFactory(version='v1.0.0')

    def test_create_nda_from_signature(self):
        """Test factory method for creating NDA."""
        pdf_bytes = b'%PDF-1.4 fake pdf content'

        nda = NDARecord.create_from_signature(
            signer_email='test@example.com',
            signer_name='Test User',
            company='Test Corp',
            document_version='v1.0.0',
            nda_pdf_bytes=pdf_bytes,
            ip_address='192.168.1.1',
            user_agent='Mozilla/5.0',
            signature_data={'method': 'click-wrap', 'timestamp': timezone.now().isoformat()},
        )

        self.assertEqual(nda.signer_email, 'test@example.com')
        self.assertEqual(nda.document_version, 'v1.0.0')
        self.assertTrue(nda.is_active())

    def test_is_active_fresh_nda(self):
        """Test is_active() for newly signed NDA."""
        nda = NDARecordFactory(signed_at=timezone.now())
        self.assertTrue(nda.is_active())

    def test_is_active_expired_nda(self):
        """Test is_active() for NDA beyond 365 days."""
        old_date = timezone.now() - timedelta(days=366)
        nda = NDARecordFactory(signed_at=old_date)
        self.assertFalse(nda.is_active())

    def test_is_active_revoked_nda(self):
        """Test is_active() for revoked NDA."""
        nda = NDARecordFactory(revoked_at=timezone.now())
        self.assertFalse(nda.is_active())

    def test_revoke_nda(self):
        """Test revoke() method."""
        nda = NDARecordFactory()
        self.assertTrue(nda.is_active())

        nda.revoke(reason='Test revocation', revoked_by=self.admin)

        self.assertFalse(nda.is_active())
        self.assertIsNotNone(nda.revoked_at)
        self.assertEqual(nda.revoked_by, self.admin)
        self.assertEqual(nda.revocation_reason, 'Test revocation')

    def test_revoke_already_revoked_nda(self):
        """Test that revoking an already-revoked NDA raises error."""
        nda = NDARecordFactory(revoked_at=timezone.now())

        with self.assertRaises(ValidationError):
            nda.revoke(reason='Second revocation', revoked_by=self.admin)

    def test_days_until_expiry(self):
        """Test days_until_expiry() calculation."""
        # Fresh NDA (just signed)
        nda = NDARecordFactory(signed_at=timezone.now())
        self.assertAlmostEqual(nda.days_until_expiry(), 365, delta=1)

        # NDA signed 300 days ago
        old_date = timezone.now() - timedelta(days=300)
        nda = NDARecordFactory(signed_at=old_date)
        self.assertAlmostEqual(nda.days_until_expiry(), 65, delta=1)

        # Expired NDA
        very_old_date = timezone.now() - timedelta(days=400)
        nda = NDARecordFactory(signed_at=very_old_date)
        self.assertLess(nda.days_until_expiry(), 0)

    def test_renewal_needed(self):
        """Test renewal_needed() for various ages."""
        # Fresh NDA - no renewal needed
        nda = NDARecordFactory(signed_at=timezone.now())
        self.assertFalse(nda.renewal_needed(warning_days=30))

        # NDA signed 340 days ago - renewal needed (within 30 days of expiry)
        old_date = timezone.now() - timedelta(days=340)
        nda = NDARecordFactory(signed_at=old_date)
        self.assertTrue(nda.renewal_needed(warning_days=30))

        # Expired NDA - no renewal (already expired)
        very_old_date = timezone.now() - timedelta(days=400)
        nda = NDARecordFactory(signed_at=very_old_date)
        self.assertFalse(nda.renewal_needed(warning_days=30))

    def test_verify_document_hash(self):
        """Test document hash verification."""
        pdf_bytes = b'%PDF-1.4 fake pdf content'
        nda = NDARecord.create_from_signature(
            signer_email='test@example.com',
            signer_name='Test User',
            company=None,
            document_version='v1.0.0',
            nda_pdf_bytes=pdf_bytes,
            ip_address='192.168.1.1',
            user_agent='Mozilla/5.0',
            signature_data={'method': 'click-wrap', 'timestamp': timezone.now().isoformat()},
        )

        # Correct PDF - should verify
        self.assertTrue(nda.verify_document_hash(pdf_bytes))

        # Wrong PDF - should fail
        wrong_pdf = b'%PDF-1.4 different content'
        self.assertFalse(nda.verify_document_hash(wrong_pdf))

    def test_immutability_after_creation(self):
        """Test that core fields cannot be modified after creation."""
        nda = NDARecordFactory()

        # Try to modify immutable field
        nda.signer_email = 'newemail@example.com'

        with self.assertRaises(ValidationError) as cm:
            nda.save()

        self.assertIn('Cannot modify', str(cm.exception))

    def test_unique_active_nda_per_email_version(self):
        """Test constraint: only one active NDA per email+version."""
        NDARecordFactory(signer_email='test@example.com', document_version='v1.0.0')

        # Try to create second active NDA with same email+version
        with self.assertRaises(ValidationError):
            NDARecord.create_from_signature(
                signer_email='test@example.com',
                signer_name='Test User',
                company=None,
                document_version='v1.0.0',
                nda_pdf_bytes=b'%PDF',
                ip_address='192.168.1.1',
                user_agent='Mozilla/5.0',
                signature_data={'method': 'click-wrap', 'timestamp': timezone.now().isoformat()},
            )


class NDARecordManagerTestCase(TestCase):
    """Test NDARecordManager custom querysets."""

    def setUp(self):
        # Create test data
        self.active_nda = NDARecordFactory(signed_at=timezone.now())
        self.expired_nda = NDARecordFactory(signed_at=timezone.now() - timedelta(days=400))
        self.revoked_nda = NDARecordFactory(revoked_at=timezone.now())
        self.expiring_soon = NDARecordFactory(signed_at=timezone.now() - timedelta(days=340))

    def test_active_queryset(self):
        """Test active() queryset."""
        active_ndas = NDARecord.objects.active()

        self.assertIn(self.active_nda, active_ndas)
        self.assertIn(self.expiring_soon, active_ndas)
        self.assertNotIn(self.expired_nda, active_ndas)
        self.assertNotIn(self.revoked_nda, active_ndas)

    def test_expired_queryset(self):
        """Test expired() queryset."""
        expired_ndas = NDARecord.objects.expired()

        self.assertIn(self.expired_nda, expired_ndas)
        self.assertNotIn(self.active_nda, expired_ndas)

    def test_revoked_queryset(self):
        """Test revoked() queryset."""
        revoked_ndas = NDARecord.objects.revoked()

        self.assertIn(self.revoked_nda, revoked_ndas)
        self.assertNotIn(self.active_nda, revoked_ndas)

    def test_needing_renewal_queryset(self):
        """Test needing_renewal() queryset."""
        needing_renewal = NDARecord.objects.needing_renewal(warning_days=30)

        self.assertIn(self.expiring_soon, needing_renewal)
        self.assertNotIn(self.active_nda, needing_renewal)

    def test_active_for_email(self):
        """Test active_for_email() method."""
        email = 'test@example.com'
        NDARecordFactory(signer_email=email, signed_at=timezone.now())

        nda = NDARecord.objects.active_for_email(email)
        self.assertIsNotNone(nda)
        self.assertEqual(nda.signer_email, email)

        # No active NDA for non-existent email
        no_nda = NDARecord.objects.active_for_email('nonexistent@example.com')
        self.assertIsNone(no_nda)

8.2 Factory Classes (factory_boy)

File: auth_service/ndas/tests/factories.py

"""
Factory classes for NDA models (factory_boy).
"""
import factory
from factory.django import DjangoModelFactory
from django.utils import timezone
import hashlib

from ndas.models import NDARecord, NDADocument, NDAAuditLog


class NDADocumentFactory(DjangoModelFactory):
    """Factory for NDADocument."""

    class Meta:
        model = NDADocument

    id = factory.Faker('uuid4')
    version = factory.Sequence(lambda n: f'v1.{n}.0')
    title = factory.Faker('sentence', nb_words=5)
    pdf_file = factory.django.FileField(filename='nda.pdf', data=b'%PDF-1.4 fake content')
    pdf_hash = factory.LazyAttribute(lambda o: hashlib.sha256(b'%PDF-1.4 fake content').hexdigest())
    effective_date = factory.Faker('date_this_year')
    is_active = True


class NDARecordFactory(DjangoModelFactory):
    """Factory for NDARecord."""

    class Meta:
        model = NDARecord

    id = factory.Faker('uuid4')
    signer_email = factory.Faker('email')
    signer_name = factory.Faker('name')
    company = factory.Faker('company')
    signed_at = factory.LazyFunction(timezone.now)
    document_version = 'v1.0.0'
    nda_document_hash = factory.LazyAttribute(lambda o: hashlib.sha256(b'fake pdf').hexdigest())
    ip_address = factory.Faker('ipv4')
    user_agent = 'Mozilla/5.0 (Test Browser)'
    signature_data = factory.LazyFunction(
        lambda: {
            'method': 'click-wrap',
            'timestamp': timezone.now().isoformat(),
        }
    )


class NDAAuditLogFactory(DjangoModelFactory):
    """Factory for NDAAuditLog."""

    class Meta:
        model = NDAAuditLog

    id = factory.Faker('uuid4')
    nda_record = factory.SubFactory(NDARecordFactory)
    event_type = 'signed'
    timestamp = factory.LazyFunction(timezone.now)
    ip_address = factory.Faker('ipv4')
    user_agent = 'Mozilla/5.0 (Test Browser)'
    details = {}

8.3 Integration Tests

File: auth_service/ndas/tests/test_api.py

"""
Integration tests for NDA API endpoints.
"""
from django.test import TestCase
from rest_framework.test import APIClient
from rest_framework import status
from django.contrib.auth import get_user_model

from ndas.models import NDARecord, NDADocument
from ndas.tests.factories import NDARecordFactory, NDADocumentFactory

User = get_user_model()


class NDAAPITestCase(TestCase):
    """Test NDA API endpoints."""

    def setUp(self):
        self.client = APIClient()
        self.admin = User.objects.create_superuser(username='admin', password='admin123')
        self.nda_document = NDADocumentFactory(version='v1.0.0', is_active=True)

    def test_sign_nda(self):
        """Test POST /api/ndas/sign/ endpoint."""
        data = {
            'signer_email': 'newuser@example.com',
            'signer_name': 'New User',
            'company': 'Test Corp',
            'agreed': True,
            'signature_method': 'click-wrap',
        }

        response = self.client.post('/api/ndas/sign/', data, format='json')

        self.assertEqual(response.status_code, status.HTTP_201_CREATED)
        self.assertEqual(response.data['signer_email'], 'newuser@example.com')
        self.assertTrue(response.data['is_active'])

    def test_sign_nda_without_agreement(self):
        """Test that signing without agreed=true fails."""
        data = {
            'signer_email': 'newuser@example.com',
            'signer_name': 'New User',
            'agreed': False,
            'signature_method': 'click-wrap',
        }

        response = self.client.post('/api/ndas/sign/', data, format='json')

        self.assertEqual(response.status_code, status.HTTP_400_BAD_REQUEST)

    def test_check_nda_status(self):
        """Test GET /api/ndas/check/{email}/ endpoint."""
        nda = NDARecordFactory(signer_email='test@example.com')

        response = self.client.get('/api/ndas/check/test@example.com/')

        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertTrue(response.data['has_active_nda'])
        self.assertIsNotNone(response.data['nda'])

    def test_check_nda_status_no_nda(self):
        """Test check endpoint for email with no NDA."""
        response = self.client.get('/api/ndas/check/nonexistent@example.com/')

        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertFalse(response.data['has_active_nda'])
        self.assertIsNone(response.data['nda'])

    def test_revoke_nda(self):
        """Test POST /api/ndas/{id}/revoke/ endpoint."""
        nda = NDARecordFactory()

        self.client.force_authenticate(user=self.admin)
        response = self.client.post(f'/api/ndas/{nda.id}/revoke/', {'reason': 'Test revocation'})

        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertIsNotNone(response.data['revoked_at'])

        # Verify in database
        nda.refresh_from_db()
        self.assertIsNotNone(nda.revoked_at)
        self.assertEqual(nda.revocation_reason, 'Test revocation')

    def test_revoke_nda_requires_auth(self):
        """Test that revoke endpoint requires admin authentication."""
        nda = NDARecordFactory()

        response = self.client.post(f'/api/ndas/{nda.id}/revoke/', {'reason': 'Test'})

        self.assertEqual(response.status_code, status.HTTP_403_FORBIDDEN)

---

9. Compliance Considerations

9.1 FDA 21 CFR Part 11 Alignment

The NDARecord model implements key requirements from FDA 21 CFR Part 11 for electronic records and signatures:

Requirement	Implementation
§11.10(a) - Validation	Django model validation + clean() method
§11.10(b) - Audit trail	NDAAuditLog model with immutable event log
§11.10(c) - System access	Django authentication + admin permissions
§11.10(d) - Sequence integrity	PostgreSQL trigger prevents modification
§11.10(e) - Education/training	Out of scope (organizational policy)
§11.50 - Signature integrity	SHA-256 hash of signed document
§11.70 - Signature uniqueness	UUID primary key + email + timestamp
§11.100 - Record retention	Immutable records (no DELETE)

9.2 Immutability Guarantees

Application Layer (Django):

• save() override prevents modification of core fields after creation
• pre_delete signal raises PermissionDenied to prevent deletion
• Admin interface uses readonly_fields for immutable data

Database Layer (PostgreSQL):

• Trigger prevent_nda_record_modification() enforces immutability at SQL level
• Only revoked_at, revoked_by, revocation_reason can be updated
• Attempts to modify other fields raise exception

Audit Trail:

• All events logged to NDAAuditLog (immutable)
• Audit logs cannot be deleted (admin has_delete_permission returns False)
• Timestamps use auto_now_add (cannot be overridden)

9.3 Cryptographic Verification

Document Hash (SHA-256):

• Computed at signing time from PDF bytes
• Stored in nda_document_hash field (64-character hex string)
• Verification method: verify_document_hash(pdf_bytes) recomputes and compares

Use Cases:

1. Integrity verification: Confirm user signed the exact document version
2. Dispute resolution: Prove document content hasn't changed since signing
3. Compliance audits: Demonstrate document authenticity

Implementation:

import hashlib

# At signing time
pdf_bytes = open('nda.pdf', 'rb').read()
document_hash = hashlib.sha256(pdf_bytes).hexdigest()

# Later verification
def verify_signature(nda_record, pdf_file):
    pdf_bytes = pdf_file.read()
    computed_hash = hashlib.sha256(pdf_bytes).hexdigest()
    return computed_hash == nda_record.nda_document_hash

9.4 Data Retention & GDPR

Retention Policy:

• NDA records are retained indefinitely (legal requirement)
• Revoked NDAs remain in database with revoked_at timestamp
• Audit logs are retained for minimum 7 years (configurable)

GDPR Considerations:

• Lawful basis: Contractual obligation (GDPR Art. 6(1)(b))
• Right to erasure: Not applicable (legal obligation override, Art. 17(3)(b))
• Right to access: Export endpoint provides user data (Art. 15)
• Data minimization: Only collect necessary fields (Art. 5(1)(c))

Privacy-Preserving Measures:

• User agent string (potentially PII) stored only for security audit
• IP address (PII) retained for fraud prevention
• No sensitive personal data collected (race, health, religion, etc.)

---

10. Implementation Checklist

10.1 Backend Implementation

• Models

  • Create ndas/models.py with NDARecord, NDADocument, NDAAuditLog
  • Implement NDARecordManager with custom querysets
  • Add model methods (is_active, revoke, renewal_needed, etc.)
  • Write docstrings for all models and methods

• Migrations

  • Generate initial migration: python manage.py makemigrations ndas
  • Review and test migration SQL
  • Apply migration: python manage.py migrate ndas
  • Create PostgreSQL trigger for immutability enforcement

• Admin Interface

  • Create ndas/admin.py with NDARecordAdmin, NDADocumentAdmin, NDAAuditLogAdmin
  • Implement custom list_display, filters, actions
  • Create analytics dashboard template
  • Add custom admin URLs for analytics view

• Signals

  • Create ndas/signals.py with post_save, pre_delete handlers
  • Define custom nda_revoked signal
  • Register signals in ndas/apps.py

• Serializers & API

  • Create ndas/serializers.py with DRF serializers
  • Create ndas/views.py with ViewSets
  • Add URL routing in ndas/urls.py
  • Configure CORS for cross-origin NDA signing

• Background Tasks (Celery)

  • Create ndas/tasks.py with Celery tasks:
    • send_nda_confirmation_email(nda_id)
    • send_nda_revocation_email(nda_id)
    • send_nda_renewal_reminder(nda_id)
    • check_nda_expiry_daily() (periodic task)

• Testing

  • Create ndas/tests/factories.py with factory_boy factories
  • Create ndas/tests/test_models.py with unit tests
  • Create ndas/tests/test_api.py with integration tests
  • Create ndas/tests/test_signals.py with signal tests
  • Run tests: python manage.py test ndas
  • Achieve >80% code coverage

10.2 Frontend Integration

• NDA Signing Page

  • Create React component for NDA signing flow
  • Integrate PDF viewer for NDA document
  • Implement e-signature capture (click-wrap, typed, drawn)
  • Add form validation and error handling
  • Submit signature to /api/ndas/sign/ endpoint

• NDA Status Check

  • Create utility function to check NDA status before document access
  • Display renewal warnings for NDAs approaching expiry
  • Redirect to NDA signing page if no active NDA

• Admin Dashboard

  • Create React admin dashboard for NDA management
  • Display analytics (signings per month, top companies)
  • Implement bulk actions (revoke, export, renewal reminders)

10.3 Infrastructure & DevOps

• Database Setup

  • Create PostgreSQL database tables
  • Apply indexes for performance
  • Create database backup policy for nda_records table

• Cloud Storage

  • Configure S3/GCS bucket for NDA PDF storage
  • Set up signed URLs for secure PDF access
  • Implement PDF retention policy

• Email Service

  • Configure SendGrid/SES for transactional emails
  • Create email templates for confirmation, revocation, renewal
  • Test email delivery in staging

• Monitoring

  • Add Prometheus metrics for NDA operations
  • Create Grafana dashboard for NDA analytics
  • Set up alerts for failed signings, expiring NDAs

10.4 Documentation & Compliance

• API Documentation

  • Generate OpenAPI/Swagger spec for NDA endpoints
  • Write API usage guide for frontend developers
  • Publish to internal docs portal

• Compliance Documentation

  • Document FDA 21 CFR Part 11 alignment
  • Create data retention policy document
  • Write GDPR privacy notice for NDA signing

• Runbooks

  • Create runbook for NDA revocation process
  • Document renewal reminder workflow
  • Write troubleshooting guide for common issues

---

Appendix A: Complete File Structure

auth_service/
└── ndas/
    ├── __init__.py
    ├── apps.py                    # App configuration
    ├── models.py                  # NDARecord, NDADocument, NDAAuditLog
    ├── admin.py                   # Django admin configuration
    ├── serializers.py             # DRF serializers
    ├── views.py                   # DRF ViewSets
    ├── signals.py                 # Django signals
    ├── tasks.py                   # Celery background tasks
    ├── urls.py                    # URL routing
    ├── migrations/
    │   └── 0001_initial.py        # Initial migration
    ├── templates/
    │   └── admin/
    │       └── ndas/
    │           └── analytics.html # Admin analytics dashboard
    └── tests/
        ├── __init__.py
        ├── factories.py           # factory_boy factories
        ├── test_models.py         # Model unit tests
        ├── test_api.py            # API integration tests
        └── test_signals.py        # Signal tests

---

Appendix B: Environment Variables

Required Django Settings:

# settings.py

INSTALLED_APPS = [
    # ...
    'ndas',
    'rest_framework',
]

# NDA Configuration
NDA_VALIDITY_DAYS = 365
NDA_RENEWAL_WARNING_DAYS = 30

# File Storage (for NDA PDFs)
DEFAULT_FILE_STORAGE = 'storages.backends.s3boto3.S3Boto3Storage'
AWS_STORAGE_BUCKET_NAME = 'bio-qms-nda-documents'
AWS_S3_REGION_NAME = 'us-east-1'

# Email (for NDA notifications)
EMAIL_BACKEND = 'django.core.mail.backends.smtp.EmailBackend'
EMAIL_HOST = 'smtp.sendgrid.net'
EMAIL_PORT = 587
EMAIL_USE_TLS = True
EMAIL_HOST_USER = 'apikey'
EMAIL_HOST_PASSWORD = os.environ.get('SENDGRID_API_KEY')
DEFAULT_FROM_EMAIL = 'noreply@coditect.ai'

# Celery (for background tasks)
CELERY_BROKER_URL = 'redis://localhost:6379/0'
CELERY_RESULT_BACKEND = 'redis://localhost:6379/0'

---

Appendix C: API Examples

C.1 Sign NDA (Public Endpoint)

Request:

curl -X POST https://auth.coditect.ai/api/ndas/sign/ \
  -H "Content-Type: application/json" \
  -d '{
    "signer_email": "researcher@biotech.com",
    "signer_name": "Dr. Jane Smith",
    "company": "BioTech Research Inc",
    "agreed": true,
    "signature_method": "click-wrap"
  }'

Response (201 Created):

{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "signer_email": "researcher@biotech.com",
  "signer_name": "Dr. Jane Smith",
  "company": "BioTech Research Inc",
  "signed_at": "2026-02-16T10:30:00Z",
  "revoked_at": null,
  "document_version": "v1.2.0",
  "is_active": true,
  "status": "ACTIVE",
  "days_until_expiry": 365,
  "renewal_needed": false
}

C.2 Check NDA Status (Public Endpoint)

Request:

curl https://auth.coditect.ai/api/ndas/check/researcher@biotech.com/

Response (200 OK):

{
  "has_active_nda": true,
  "nda": {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "signer_email": "researcher@biotech.com",
    "signed_at": "2026-02-16T10:30:00Z",
    "document_version": "v1.2.0",
    "is_active": true,
    "days_until_expiry": 365,
    "renewal_needed": false
  }
}

C.3 Revoke NDA (Admin Endpoint)

Request:

curl -X POST https://auth.coditect.ai/api/ndas/550e8400-e29b-41d4-a716-446655440000/revoke/ \
  -H "Authorization: Bearer <admin_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "reason": "User left organization"
  }'

Response (200 OK):

{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "signer_email": "researcher@biotech.com",
  "revoked_at": "2026-02-16T14:22:00Z",
  "revoked_by_username": "admin",
  "revocation_reason": "User left organization",
  "is_active": false,
  "status": "REVOKED"
}

---

Appendix D: SQL Query Examples

D.1 Find Users with Expiring NDAs (Next 30 Days)

SELECT
    signer_email,
    signer_name,
    company,
    signed_at,
    (365 - EXTRACT(DAY FROM NOW() - signed_at)) AS days_until_expiry
FROM nda_records
WHERE revoked_at IS NULL
  AND signed_at >= NOW() - INTERVAL '365 days'
  AND signed_at <= NOW() - INTERVAL '335 days'
ORDER BY signed_at ASC;

D.2 Audit Report: All NDAs Signed in Last Month

SELECT
    signer_email,
    signer_name,
    company,
    signed_at,
    document_version,
    ip_address
FROM nda_records
WHERE signed_at >= NOW() - INTERVAL '30 days'
ORDER BY signed_at DESC;

D.3 Company NDA Summary

SELECT
    company,
    COUNT(*) AS total_ndas,
    COUNT(*) FILTER (WHERE revoked_at IS NULL AND signed_at >= NOW() - INTERVAL '365 days') AS active_ndas,
    COUNT(*) FILTER (WHERE revoked_at IS NOT NULL) AS revoked_ndas
FROM nda_records
WHERE company IS NOT NULL
GROUP BY company
ORDER BY total_ndas DESC
LIMIT 20;

---

Document Control

Version	Date	Author	Changes
1.0.0	2026-02-16	Claude (Sonnet 4.5)	Initial comprehensive design document

---

End of Document